Question: I’m testing an import for one customer and while importing the database dump file with Data Pump I’m getting the following error:

IMP-00017: following statement failed with ORACLE error 959:
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace ‘_$deleted$24$0′ does not exist

How do I remove the impdp message that the tablespace does not exist?

Answer: Data Pump impdp will return a ORA-00959 when a table definition specifies multiple tablespaces (i.e. a CLOB column stored in a separate tablespace. In these cases, the solution is to pre-create the table (punching the DDL with dbms_metadata) and use impdp with ignore=y.

By default, an export only exports the objects in a tablespace, not the tablespace definition itself.

In this case, the import is complaining because a tablespace with the same name as the export tablespace does not exist. You can fix the ORA-00959 error in several ways:

* Create the tablespace and use IGNORE=Y in the impdp syntax
* Import into another user ID with a different default tablespace name (fromuser touser)
* Use include=tablespace if you want to have the import pre-build the same tablespaces

Generally, in an Oracle migration, the schema and DDL are pre-created in the target database, and then the rows are imported into the new database using the Oracle imp utility with the IGNORE=Y parameter. The IGNORE=Y parameters tells Oracle to use the new DDL definitions instead of the DDL inside the import data file.

But in Oracle9i and beyond, you can use the new utility package called dbms_metadata that will easily display DDL and stored procedures directly from the data dictionary.

I recently needed to provide mapping functionality in an APEX app I was developing.

In V3+ of Oracle apex, you can create interactive reports like this

Now when you click the cog, you geta map icon at the top:

I added a <li> element to the interactive report on-the-fly using jQuery (depending if it finds a div with an attribute of ‘gis’ on the page within the report).

I’ve written some jQuery for the onclick event of the map href to loop through the displayed rows, looking for a DIV with an invisible attribute called ‘gis’.  I then assemble these into a GEORSS file and store it in a public locations. The href from the map then calls a page showing the map and settings its source to the newly created file.

In reality, the file is actually a CLOB stored in the oracle database, and I use the ability of oracle to call a package from a URL to return the clob with the GEORSS file from my database table.

When clicked, the icon opens up an arcview  GIS map with the current interactive report data displayed.

I’ve used colorbox for jQuery to show the map in a lightbox style pop-up window.

You might occasionally get this error in Oracle Apex when submitting a page.
Alternatively, the page might not submit at all when you click a page button.

I must admit this had me stumped and i had to resort to disabling everything, then reenabling region items to find out what was wrong.

Turns out that APEX does not like the ‘name’ attribute of an element. Remove the name element and the problem goes away. Just use the ‘id’ attribute element instead.

1
2
3
4
5

before:
<input type="text" id="username" name="username" class="grey"/>
 
after:
<input type="text" id="username" class="grey"/>

Hope this little tip helps someone.

1. Vertical centering with line-height

1

line-height:24px;

When you have a container with fixed height you can use line-height property to vertically center the content.

2. Prevent oversized content to break fixed width floated layouts

1
2
3

#main{
	overflow:hidden;
}

When oversized content (i.e. wide image) is placed in fixed width floated container, it may break the layout. To prevent that use this trick. It will hide a part of the content but at least your layout structure will remain intact.

3. Prevent line breaks in links

1
2
3

a{
	white-space:nowrap;
}

This little trick will prevent line breaks on your links. I recommend using this with long text to avoid having links break into 2 lines.

4. Always show Firefox scrollbar

1
2
3

html{
	overflow:-moz-scrollbars-vertical;
}

Firefox hides vertical scrollbar by default. So, when you browse a site that have different page heights you notice a horizontal shift. This code will always display a scrollbar and prevent shifting.

5. Centering block elements horizontally

1

margin:0 auto;

For all modern browser this line of css is enough to horizontally center a block level element.

6. Remove vertical textarea scrollbar in IE

1
2
3

textarea{
	overflow:auto;
}

Textareas in IE have vertical scrollbar visible by default. If you want those removed (I know I do) use this line.

7. Force page breaks when printing your document

1
2
3

h2{
	page-break-before:always;
}

With this line of code you can control places where you want your pages to break when printing a document.

8. Remove active link borders

1
2
3

a:active, a:focus{
	outline:none;
}

Pipelined functions are an extremely useful feature of Oracle 9i and above. They allow you to present disperate nontable based data as a standard table whilst streaming the output so that processing can begin immediately.

I.e., they let you SELECT from a function much like you would SELECT from a table. Cool huh?
Why would you need something like this?

Let me go straight in with an example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE OR REPLACE function fn_ea_bio_file (p_path varchar2)
    return ea_bio_table
    pipelined
    is
      l_clob clob;
      l_n    pls_integer;
      l_line varchar2(1000);
      l_site varchar2(20);
      l_sortcode varchar2(10);
      l_abundance varchar2(5);
    begin
        SELECT XDBURIType(p_path).getClob()
        into l_clob FROM DUAL;
     if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
     l_n := instr(l_clob,chr(10));
     l_line := substr(l_clob,1,l_n)||',';
     while l_n > 0 loop
        for x in 0 .. 5
        Loop
            --dbms_output.put_line('line:'||l_line);
            l_line := replace(l_line,chr(10),'');
            l_site := trim(substr(l_line, 1, 20));
            l_sortcode := substr(l_line, 21+(10*x),9);
            l_abundance := trim(substr(l_sortcode,9,1));
            l_sortcode := trim(substr(replace(l_sortcode,',',''),1,8));
            if l_sortcode is not null then
                pipe row(ea_bio(l_site,l_sortcode, l_abundance));
            end if;
            End Loop;
 
       l_clob := substr(l_clob,l_n+1);
       l_n := instr(l_clob,chr(10));
       l_line := substr(l_clob,1,l_n)||',';
     end loop;
   end;

Here. im reading a CSV file from the local XMLDB repository, but I could just as easily read a CSV from a local directory or a URL somewhere on the net.

I need to present the CSV data as a table so that I can query, group, sum count etc, basically manipulate and select as I like. Normally, I might read the entire CSV in, and store it in a temporary table. This approach can be very messy, i’d need to clean up afterwards, it would take time to read and process large files, i’d need to include some concurrent user functionality, so i’d have to store a user_id and a concept of a run (a sequence) number. I just want to select from a CSV file, is this so hard? Nope it isn’t. Read on.

In this code, i’m looping through a CSV file one line at a time.
Next, i’m setting some column values that I want to output.
Finally, i’m piping the columns out of the function.

I select from this function using:

1
2

Select * from table(fn_ea_bio_file(
        p_source_folder||'/'||p_source_filename))

What could be easier. The result look like they come from a table or view, and I can point the query to any source file I like (no setting up directories in advance or any nonsense like that).

I can join this CSV file to other table and basically treat it as I would any other internal data source.

The function returns rows as it processes them. Thats why it’s called a pipeline function. It will spit out data whilst it is still consuming and processing the remaining data. It streams it as it gets it.

River Invertebrate Classification System

RICT is a software tool that the environment agencies in the UK use to help monitor and classify the ecology of invertebrates throughout the UK river network

Technical architect and solution developer for this project. PL/SQL, XML, Oracle Apex, Web Services, JQUERY, CSS, RSS, Linux, Amazon AWS cloud computing, Amazon EBS, Security, load balancing and availability design. I wrote all the code and designed the interface and workflow as well as the major functional features of the software.

Working with statisticians to build complex analytical models into the software

Google Maps integration, KML, GML integration with ArcGIS and others.

Training staff in England, NI and Scotland.

Running numerous workshops to raise awareness of software

Board meetings, funding presentations, demo reels, documentation

FISH monitoring tool prototype

Wrote the protype Fish classification tool. This tool helps classify rivers according to their fish populations which in turn assist ecologists and environmental bodies in monitoring changes to these water bodies over time.

Technical architect and solution developer for this project. PL/SQL, XML, Oracle Apex, Web Services, JQUERY, CSS, RSS, Amazon AWS cloud computing

Graphing in APEX using custom javascript libraries (FLOT, jQuery)

Understanding of fish measuring techniques and statistical methods and translating these to technical specifications which I developed in oracle with APEX as a front end.

Creation of custom APEX templates, custom authorization and authentication routines.

GIS outputs, data visualization interactive reports

EU Water Framework Directive Classification Reporting

Working with statisticians and data owners to develop a classification system for EU reporting of water bodies in Scotland. Allowing SEPA to meet its duties to classify these water bodies using EU standard quality measures.

Developing Service Oriented Architecture (SOA) web services for offline communication between the SEPA LIMS database and the statistical analysis routines required for classification. Promoting the use of SOA thereby modularizing business processes in a loosely coupled way for ease of integration, and resulting in future development cost savings by an increase in code-reuse.

Creating XSD document interchange standards for inter-software communications.

Wrote numerous data conversion routines to handle field collected data in Excel, CSV and other custom formats.

Developing front-end Oracle Forms applications for administrator of the WFD classification software.

Extensive use of PL/SQL and JAVA stored procedures. Performance tuning. Object Oriented design. Materialized views.

Creation of intranet reporting software (using Oracle APEX) to monitor and compare classifications using key performance metrics.

Development of automated unit test software and general compliance with Agile software testing methodologies and ideals.

Laboratory Information Management System (LIMS)

Modification of NEMS (in-house LIMS system) Smalltalk routines that interface with RIVPACS software to correct errors in interface logic.

Integrated UK standard taxa conventions from the National Biodiversity Network (NBN) into NEMS. Developed a series of routines to map existing coding standards to universal NBN codes. Updating historical data to use the new NBN code and hierarchy.

Development of software routines for conversion of Geo-spatial information between various datum. For instance, the software converts between NGR references, Latitude/Longitude pairs and Northings / Eastings.

Creation and maintenance of various Crystal reports for use with water network planning and monitoring.

Performance tuning and monitoring

eXtensible Markup Language (XML) framework

Creating a framework for SEPA to take advantage of Oracle’s new XML features which were require for inter-software communications, and also for delivery of new governmental reporting requirements. Developing storage methodologies, and the beginnings of a searchable XML document repository for future meta-data purposes.

Water Information Systems for Europe (WISE) reporting

Producing XML/XSL reports for presentation of quality measures in a Europe Wide XML standard.

Development of Oracle XML routines to extract data from the working database and map to published schema definitions.

Contact model design

Development of standards for the storage of contacts in the SEPA corporate database.

Modification of existing Oracle software to comply with the new standards.

Working closely with legal department and data protection act guidelines to ensure full compliance when storing and retrieving personal contact information.

Scottish Water were developing a corporate Data Warehouse to consolidate the change from three authorities to one authority. They were developing way to integrate business intelligence between the three authorities with a single point of entry to the business’s information. Mark has a determined and focused attitude, and done everything necessary in his role as technical architect and team leader to ensure the project delivered on time and on budget.

Mark was involved as technical architect and testing team lead for the billing Data Mart his duties included –

• Working with the business to ensure technical solutions are fit for purpose. Formulating technical solutions and building a strategy to see them though to completion. Ensuring targets are met. Developing and verifying analysis documentation.
• Forming a test strategy for the various development and UAT stages. Managing test scripts and scenarios. Managing test results using established in-house methods. Managing expectations and setting realistic work stream targets based on test results analysis.
• Creating test scenarios and building test rigs
• Ensuring data standards compliance, working with data cleansing team on data quality issues
• Challenging the business on every aspect of the development requirements to ensure future clarity and product acceptance later in the development cycle.
• Holding development workshops where encouragement was given for ideas to be openly challenged allowing new angles of attack to be created for development bottlenecks
• Forming a clear, focused and measurable development plan for the project. Building an Oracle development team and responsible for creating strong team-working environment
• Producing and managing development work streams.
• Main point of contact to the business for Business Intelligence technical solutions
• Senior Oracle 9i developer using Toad and PL/SQL(strong)
• Documentation of Data Warehouse with the help of PVCS Tracker
• Reporting on development and test progress to the business
• Analysis and design of Data Warehouse, focusing on the Billing and CRM Data Marts
• Creation of Oracle database objects such as packages, procedures, functions, triggers, materialized views, bitmap indexes, custom classes and objects.
• Performing training workshops on the use of the Data Marts and related tools (Informatica ETL, Business Objects, PVCS)
• Ensuring Data Mart is fit for purpose. Meeting with the business to discuss requirements and recording use case studies
• Analysis and design of Star schemas, and conformed dimension modeling and implementation
• Performance tuning and maintenance of Data Warehouse, working closely with DBAs and testing staff with ultimate goal of a simple to use responsive Data Warehouse
• Analysis and preparation of technical specifications documentation for ETL stage, including full and incremental loads scenarios and using the use of Type 1 and Type 2 dimensions.
• Developed a Data Warehouse summary report detailing, in a page, the current status of the various Facts and Dimensions and highlighting changes and potential issues. Monitoring change thresholds and proving first point of notice to the data quality teams

Mark was responsible to the test manager for ensuring adequate testing was performed at the various stages of the project life cycle. He was closely involved as technical point of contact with the UAT team. He worked closely with the test team to ensure product compliance.

Mark performed most of the development and analysis for the project and produced full Datamart technical specification documents including, but not limited to Bus Matrix; Star diagrams; mapping specifications; usage instructions;  extensive in-line documentation; System and Unit testing scripts; logical and physical data models;

Mark’s previous role was as technical lead for the data migration projects which was marrying three billing systems into one brand new billing system. His main responsibilities in this role were –

• Team leader with responsibility for 4 PL/SQL developers, a Business Objects developer and an Infomatica (ETL) developer
• Preparation of test scripts for Unit testing / system testing and assistance with preparation of script and scenarios for Unit Testing
• Working closely and integrating responses and actions from the test managers. Helping them understand the data and holding workshops with their staff members to allow them to make the best use of their testing resources. Assisted in creating a test scenario rig chained to a set Business Objects reports which allowed rapid testing of use cases
• Analysis of business departments data usage, and holding workshops to both establish requirements and ensure better user acceptance through good communications throughout the project
• Responsible for relaying progress of project to the business. Point of contact and arbitrator between the testing teams, the development teams and the management and other interested parties
• Creation of development strategy and redundancy planning. Pairing of developers and testers, team building and generally ensuring everyone is aware of their duties and feels part of the team
• Developing the most part of the PL/SQL code, triggers and functions required for the billing and CRM reporting. Assisting in the creation of reporting system used to deliver KPIs’ to the government watchdog
• Working with government commissioner office to establish correct data delivery mechanisms and interfaces

Many people seem to have trouble reading XML data within Oracle.

Oracle has a full library of routines built in to allow you to interrogate XML data. Unfortunately, they’re not very well documented.

Just say you have an XML file you want to be able to SELECT from. it looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

create table mytable 
(id number
,col1 clob);
 
insert into mytable values(1,'<Datasets>
  <Dataset Index_Name="NTAXA" Season_ID="1">
    <Value>1.62</Value>
  </Dataset>
  <Dataset Index_Name="NTAXA" Season_ID="7">
    <Value>1.7982</Value>
  </Dataset>
  <Dataset Index_Name="BMWP" Season_ID="1">
    <Value>1.25</Value>
  </Dataset>
</Datasets>
');
insert into mytable values(2,'<Datasets>
  <Dataset Index_Name="NTAXA" Season_ID="8">
    <Value>3</Value>
  </Dataset>
  <Dataset Index_Name="NTAXA" Season_ID="2">
    <Value>2</Value>
  </Dataset>
</Datasets>
');
commit;

Get your data out using this query

1
2
3
4
5
6

SELECT id,
       EXTRACTVALUE (VALUE (ds), '/Dataset/@Index_Name') Index_Name,
       EXTRACTVALUE (VALUE (ds), '/Dataset/@Season_ID') Season_ID,
       EXTRACTVALUE (VALUE (ds), '/Dataset/Value') VALUE
  FROM mytable rs,
       TABLE (XMLSEQUENCE (EXTRACT (xmltype(col1), '/Datasets/Dataset'))) ds

We’re using the @ symbol in the EXTRACTVALUE statement for Index_Name and Season_ID to indicate that we are looking for an ATTRIBUTE and not an element.

1
2
3
4
5
6

ID,INDEX_NAME,SEASON_ID,VALUE
1,NTAXA,1,1.62
1,NTAXA,7,1.7982
1,BMWP,1,1.25
2,NTAXA,8,3
2,NTAXA,2,2

Pretty simple huh? So why is it so hard to work with XML in Oracle?

Well, i’ve been misleading you a bit. For any real-world software the requirements are likely to be much more complicated than this simple example demonstrates.

You might have structured storage (xsd based xml instances), optional elements, external references to other schemas within the xsd, deeply nested xml structures etc..

Oracle can handle them all nicely, but it gets a bit more tricky – until that gotcha! moment.

I’ll be writing many more articles on the peculiarities of Oracle and XML, so hopefully all will become clear. Click the code tag or the xml tag below this post for more related articles.

HTTP, HTTPS, FTP, WebDAV including WebDAV Version Control

Oracle XML DB is not a separate server; it is an integral part of the Oracle database, providing all of the high-availability, scalability, reliability and unbreakable security features needed to run mission-critical applications.

You can use Enterprise Manager to manage and administer XML DB applications. The GUI in Enterprise Manager allows us to perform the following tasks.

Configuration

• Configure XML DB including protocol server configuration
• View and edit XML DB configuration parameters
• Register XML schemas

Create Resources

• Manage resource security such as ACL’s
• Granting and revoking privileges
• Creating and editing resource indexes
• Viewing and navigating XML DB repository

Create XML schema-based tables and views

• Create the storage structure based on XML schemas
• Editing an XML schema
• Creating XMLType columns and tables based on XMLType
• Creating a view-based XML-schema
• Creating function based indexes

Because the XML repository can be exposed as a WebDAV folder, getting access to the schema files is very straight forward. Creating XSD document and XSL transformations using Altova XML Spy for instance is especially easy.

The contents of the XML DB repository – usually XSD, XSL, XSLT and XML documents, but can also be JPG, DOCS or anything else you like – can be easily accessed directly over WebDAV or FTP from within the XMLSpy IDE.

Automatic bi-directional translation between client and database character sets

XML DB handles conversion of the files from/to oracle db on the fly. For instance when a WebDAV user opens a file in the repository, XML DB dynamically generates the file from data held in the oracle database.

When a schema-based XML document is loaded in the repository, XML DB will automatically recognise the document, shred it, and store it in the default table defined by the XML schema.

Storage options

Unstructured Storage

XML data is stored in a single field in a table as a Character Large Object (CLOB)

Advantages:

• Throughput
• Preserves fidelity of original document byte for byte, this may be important for some applications

Disadvantages:

• When a row is updated, the entire XML document must be re-written to disk

Structured Storage

XML DB creates underlying oracle tables and objects to hold the XML data. These objects are created by referencing the required associated XSL document.

Advantages:

• XQUERY / XPATH can be used to query and reference the data
• Queries use the built-in standard oracle optimiser
• Data can be streamed from/to the tables, reducing memory overhead when dealing with large document
• Can use B-Tree indexes, Function-based indexes, Text based indexes
• Can index on existsNode values etc…

Disadvantages:

• All data must conform to a XSD schema (valid XML document) (also an advantage)
• Extra processing required whilst ingesting and retrieving xml content

XSD Schemas

XSD schemas specify how to formally describe the elements in an Extensible Markup Language (XML) document. This description can be used to verify that each item of content in a document adheres to the description of the element in which the content is to be placed.

In general, a schema is an abstract representation of an object’s characteristics and relationship to other objects. An XML schema represents the interrelationship between the attributes and elements of an XML object (for example, a document or a portion of a document). To create a schema for a document, you analyze its structure, defining each structural element as you encounter it. For example, within a schema for a document describing a Web site, you would define a Web site element, a Web page element, and other elements that describe possible content divisions within any page on that site. Just as in XML and HTML, elements are defined within a set of tags.

An investment in an XML registry will, over time, allow projects to be more manageable and business driven allowing the business with the help of IS achieve its goals in a consistent and timely manner; which is, after all, the whole point of IS.

XMLTYPE

XMLType data type is a native Oracle type that contains XML data. It is stored internally as a CLOB column.

XMLType data type can be user like any other SQL data type in Oracle, including in Create Tables, Views, parameter passing or return types.

XMLType columns should contain well-formed XML documents.

Service oriented Architecture

Keywords: Abstraction, Granularity, Versioning, Internal dependencies

Web services provide easy access to remote content and application functionality using industry-standard mechanisms, without any dependency on the provider’s platform, the location, the service implementation, or the data format. For that reason, Web services are a principal means of achieving SEPA resource virtualisation, a key requirement for building enterprise scalable solutions.

The ability to turn the database into a Web services provider extends the database’s functionality to Web services client applications by allowing the execution of database operations and data retrieval through standard Web services mechanisms.