Case 11: Calling a Stored Function from SQL*Loader
5- SQL*Loader Conventional and Direct Path
SQL*Loader is a utility provided by Oracle specifically for the purpose of loading large volumes of data from flat files into Oracle tables. It is very powerful, flexible, and fast. However, we must launch it from the operating system. (Invoking SQL*Loader within a dbms_job or a PL/SQL stored procedure is tricky but we can do it.).
SQL*Loader still seems to be about the fastest and most efficient way to get flat file data into Oracle. By default, SQL*Loader uses what it calls "conventional path" loading–bulk inserts, basically. The
performance is not phenomenal, and there are faster alternatives. However, with a simple "direct=true" on the command line, we can invoke "direct path" loading. In a direct path load, SQL*Loader writes rows directly into new data blocks above the table's high water mark. This is like inserting with the Append hint as seen in the last section, but it's even better. In a direct path load, SQL*Loader also updates indexes using a very efficient bulk method.
Loading the call center data using SQL*Loader with conventional path load took about 81 seconds. The database server process used about 12 seconds of CPU time. This was with indexes in place and
SQL*Loader bulk inserting rows 100 at a time. With a direct path load, SQL*Loader got the data into the database in just 9 seconds, with only 3 CPU seconds used by the database server process. This was with indexes in place.
Direct path SQL*Loader is fast and efficient. It is even fast when indexes on the target table are not dropped before the load. As for disadvantages, direct path loading has implications for recovery akin to the NOLOGGING keyword. Also, indexes on the target table are unusable for a period during the load. This can impact users trying to access the target table while a load is in progress. Also, indexes can be left in an unusable state if the SQL*Loader session were to crash midway through the load.
Data Loading Method Elapsed Seconds Database Server CPU Seconds
SQL*Loader conventional path (indexes in place and rows=100)
81 12
SQL*Loader direct path (indexes
in place) 9 3
Conclusion
There are many different ways to load data into Oracle. Each technique offers its own balance between speed, simplicity, scalability, recoverability, and data availability.To recap, here are all of the timing figures in one place:
Data Loading Method Elapsed Seconds Database Server CPU Seconds
Single row inserts 172 52
Single row inserts without indexes (time includes index rebuild using NOLOGGING and large sort area)
130 35
Bulk row inserts 100 rows at a time without indexes (time includes index rebuild using NOLOGGING and large sort area)
14 7
CREATE as SELECT from an external table with NOLOGGING (time includes index build using NOLOGGING and large sort area)
15 8
SQL*Loader conventional path
(indexes in place and rows=100) 81 12
SQL*Loader direct path (indexes
in place) 9 3
Please keep in mind that I did not even touch on the subject of parallelism in data loads. (Inserts with the Append hint can use parallelism in the Enterprise Edition of the Oracle software.
Oracle XML
What is XML and what is it used for?
XML (eXtensible Markup Language) is a W3C initiative that allows information and services to be encoded with meaningful structure and semantics that both computers and humans can understand. XML is great for information exchange, and can easily be extended to include user-specified and industry-specified tags. Look at this simple example defining a FAQ:
<?xml version="1.0"?>
<!DOCTYPE question-list SYSTEM "faq.dtd"> <?xml-stylesheet type="text/xml" href="faq.xsl"?> <FAQ-LIST>
<QUESTION>
<QUERY>Question goes here</QUERY>
<RESPONSE>Answer goes here.</RESPONSE> </QUESTION>
<QUESTION>
<QUERY>Another question goes here.</QUERY> <RESPONSE>The answer goes here.</RESPONSE> </QUESTION>
</FAQ-LIST>
What is a DTD and what is it used for?
A Document Type Definition (DTD) defines the elements or record structure of a XML document. A DTD allows your XML files to carry a description of its format with it. The DTD for the above XML example looks like this:
<?xml version="1.0"?>
<!ELEMENT faq-list (question+)>
<!ELEMENT question (query, response*)> <!ELEMENT query (#PCDATA)> <!ELEMENT response (#PCDATA)> Notes:
• #PCDATA (parsed character data) means that the element contains data that can be parsed by a parser like HTML
• The + sign in the example above declares that the "QUESTION" element must occur one or more times inside the "FAQ-LIST" element.
• The * sign in the example above declares that the "QUERY" element can occur zero or more times inside the "QUESTION" element.
The W3C also formulated a new standard, called XML Schemas that superceded DTD's. Schemas allow for more complex data types within your tags and better ways to constrain (validate) data within these tags.
What XML compliant products do Oracle provide?
• XMLDB
Standard option that ships with the Oracle 9i database (from 9.2.0). Previously called Project XDB.
• XML-SQL Utility (XSU)
Programatic interfaces (packages) for PL/SQL and Java • XML Developer's kits (XDK) for Java, C, C++ and PL/SQL.
See demo programs in the $ORACLE_HOME/xdk/ directory. • XSQL Servlet
Processing SQL queries and generating an XML result set
See http://technet.oracle.com/tech/xml/xsql_servlet/htdocs/relnotes.htm • Other products
Oracle integrated XML in various other product offerings like the Oracle iFS, Oracle InterMedia, JDeveloper, etc.
If you're using Oracle 8i, use the DBMS_XMLQUERY and DBMS_XMLSAVE JAVA based packages. For Oracle 9i, use the C-based package DBMS_XMLGEN.
Look at the following Oracle 9i code example: connect scott/tiger
set serveroutput on DECLARE
Ctx DBMS_XMLGEN.ctxHandle; -- Var's to convert SQL output to XML xml clob;
emp_no NUMBER := 7369;
xmlc varchar2(4000); -- Var's required to convert lob to varchar off integer := 1;
len integer := 4000; BEGIN
Ctx := DBMS_XMLGEN.newContext('SELECT * FROM emp WHERE empno = '||emp_no); DBMS_XMLGen.setRowsetTag(Ctx, 'EMP_TABLE');
DBMS_XMLGen.setRowTag(Ctx, 'EMP_ROW'); DBMS_XMLGEN.closeContext(Ctx);
xml := DBMS_XMLGEN.getXML(Ctx);
DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen DBMS_OUTPUT.PUT_LINE(xmlc);
END; /
The same results can be achieved using SQLX (see http://sqlx.org/). Some of the SQLX functions are XMLElement(), XMLForest(), XMLSequence(), etc. Look at this example.
set long 32000
SELECT XMLELEMENT("EMP_TABLE",
(select XMLELEMENT("EMP_ROW",
XMLFOREST(empno, ename, job, mgr, hiredate, sal, deptno) )
from emp
where empno = 7369)) from dual;
An older Oracle 8i example: connect scott/tiger set serveroutput on DECLARE
Ctx DBMS_XMLQuery.ctxType; -- Var's to convert SQL output to XML xml clob;
emp_no NUMBER := 7369;
xmlc varchar2(4000); -- Var's required to convert lob to varchar off integer := 1;
len integer := 4000; BEGIN
Ctx := DBMS_XMLQuery.newContext('SELECT * FROM emp WHERE empno = :empno'); DBMS_XMLQuery.setBindValue(Ctx, 'empno', emp_no);
xml := DBMS_XMLQuery.getXML(Ctx); DBMS_XMLQuery.closeContext(Ctx);
DBMS_LOB.READ(xml, len, off, xmlc); -- Display first part on screen DBMS_OUTPUT.PUT_LINE(xmlc);
END; /
How does one store and extract XML data from Oracle?
XML data can be stored in Oracle (9.2.0 and above) using the XMLType data type. Look at this example: connect scott/tiger
create table XMLTable (doc_id number, xml_data XMLType); insert into XMLTable values (1,
XMLType('<FAQ-LIST> <QUESTION>
<QUERY>Question 1</QUERY>
<RESPONSE>Answer goes here.</RESPONSE> </QUESTION>
</FAQ-LIST>'));
select extractValue(xml_data, '/FAQ-LIST/QUESTION/RESPONSE') -- XPath expression from XMLTable
where existsNode(xml_data, '/FAQ-LIST/QUESTION[QUERY="Question 1"]') = 1;
The XML Developer Kit
Beginning with the first release of Oracle 8i, Oracle has offered the XDK for the Oracle database. For Oracle 8i releases 1 and 2 (8.1.5 and 8.1.6) you must download the XDK from Oracle Technology Network and install it manually. With Oracle 8i release 3 (8.1.7) and Oracle 9i, the XDK is integrated into the database and is installed automatically when the database is installed.
The XDK may be accessed from PL/SQL, Java, C, and C++ applications. PL/SQL applications must run inside the database (i.e. stored procedures, packages, triggers, or anonymous PL/SQL blocks) in order to access the XDK. Java applications may reside inside or outside the database. C and C++ applications, of course, reside outside the database.
In order to access the XDK from PL/SQL or Java applications residing inside the database, you must install Oracle’s JVM (sometimes called the Java option or Oracle JServer). It may seem counter-intuitive that you must have Oracle’s JVM installed in order to use the XDK from PL/SQL, but there is a reason for it: The XDK is itself written in Java. Oracle merely put PL/SQL wrappers on the Java code in order to make it accessible from PL/SQL.
The XDK contains an XML parser, an XSLT processor, an XPath engine, an XSQL page processor and servlet, and an XML SQL utility. The first three are Oracle’s implementations of the XML 1.0 specification, while the last two are features unique to Oracle.
Note that the XSQL page facility is only accessible from Java applications. Also note that in earlier releases of the XDK, the XML SQL utility was a separate tool that you had to install separately from the XDK. You can download the XML SQL utility from Oracle Technology Network.
Hello! Could you elaborate the example of serializing the XML into a table, let's say the XML file has data which corresponds to 2 tables (repetitive tags for a detail table), is it possible?
Followup: Sean here...
This is pretty easy to accomplish. There is no automated XML utility in Oracle to insert a single XML document into two different tables... What you could do, however, is create a join view on the two tables, then write an INSTEAD OF trigger on the join view. Insert the XML document into the join view. The INSTEAD OF trigger's job would be to insert rows into the appropriate tables
based on the values of the parent key found in each ROWSET of the XML document. As an example, I have an XML document that looks like SO:
<?xml version = "1.0"?> <ROWSET> <ROW num="1"> <DEPTNO>10</DEPTNO> <DNAME>SALES</DNAME> <EMPNO>100</EMPNO> <ENAME>MARK JOHNSON</ENAME> </ROW> <ROW num="2"> <DEPTNO>20</DEPTNO> <DNAME>TECHNOLOGY</DNAME> <EMPNO>200</EMPNO> <ENAME>TOM KYTE</ENAME> </ROW> <ROW num="3"> <DEPTNO>20</DEPTNO> <DNAME>TECHNOLOGY</DNAME> <EMPNO>300</EMPNO> <ENAME>SEAN DILLON</ENAME> </ROW> </ROWSET>
So you can see... the department data and the employee data co-mingled. We want to normalize this into two tables... so here's what I'd do:
---
system@SLAP> create table dept ( 2 deptno number
3 primary key,
4 dname varchar2(30)); Table created.
system@SLAP> create table emp ( 2 empno number
3 primary key, 4 deptno number,
5 ename varchar2(30)); Table created.
system@SLAP> create view deptemp as
2 select d.deptno, d.dname, e.empno, e.ename 3 from dept d, emp e
4 where d.deptno = e.empno; View created.
system@SLAP> create or replace trigger deptemp_ioifer 2 instead of insert on deptemp
3 declare 4 begin 5 begin
6 insert into dept (deptno, dname) 7 values (:new.deptno, :new.dname); 8 exception
9 when DUP_VAL_ON_INDEX then 10 update dept
11 set dname = :new.dname 12 where deptno = :new.deptno; 13 end;
14 --
15 insert into emp (empno, deptno, ename)
16 values (:new.empno, :new.deptno, :new.ename); 17 end;
18 /
Trigger created.
system@SLAP> declare
2 l_clob clob := '<?xml version = "1.0"?> 3 <ROWSET> 4 <ROW num="1"> 5 <DEPTNO>10</DEPTNO> 6 <DNAME>SALES</DNAME> 7 <EMPNO>100</EMPNO> 8 <ENAME>MARK JOHNSON</ENAME> 9 </ROW> 10 <ROW num="2"> 11 <DEPTNO>20</DEPTNO> 12 <DNAME>TECHNOLOGY</DNAME> 13 <EMPNO>200</EMPNO> 14 <ENAME>TOM KYTE</ENAME> 15 </ROW> 16 <ROW num="3"> 17 <DEPTNO>20</DEPTNO> 18 <DNAME>TECHNOLOGY</DNAME> 19 <EMPNO>300</EMPNO> 20 <ENAME>SEAN DILLON</ENAME> 21 </ROW> 22 </ROWSET>'; 23 24 l_ctx dbms_xmlsave.ctxType; 25 l_rows number; 26 begin 27 l_ctx := dbms_xmlsave.newContext('DEPTEMP'); 28 l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob); 29 dbms_xmlsave.closeContext(l_ctx);
30 dbms_output.put_line(l_rows || ' rows inserted...'); 31 end insert_xml_emps;
32 /
PL/SQL procedure successfully completed. system@SLAP> select * from dept; DEPTNO DNAME
--- --- 10 SALES
20 TECHNOLOGY
system@SLAP> select * from emp; EMPNO DEPTNO ENAME
--- --- --- 100 10 MARK JOHNSON
200 20 TOM KYTE 300 20 SEAN DILLON ---
...and there you have it. Hope that helps!
How can I handle this? One row for the department table and 3 rows for the employee table in the same XML row.
<?xml version = "1.0"?> <ROWSET> <ROW num="1"> <DEPTNO>10</DEPTNO> <DNAME>SALES</DNAME> <EMPLOYEE> <EMPNO>100</EMPNO> <ENAME>MARK JOHNSON</ENAME> </EMPLOYEE> <EMPLOYEE> <EMPNO>200</EMPNO> <ENAME>VICTOR JAEN</ENAME> </EMPLOYEE> <EMPLOYEE> <EMPNO>300</EMPNO> <ENAME>JHON SMITH</ENAME> </EMPLOYEE> </ROW> </ROWSET> Thanks a lot! Followup:
1* select dbms_xmlgen.getxml( 'select deptno, dname, cursor( select empno,
ename from emp where emp.deptno = dept.deptno ) employee from dept where deptno = 10' ) from dual scott@ORA920> / DBMS_XMLGEN.GETXML('SELECTDEPTNO,DNAME,CURSOR(SELECTEMPNO,ENAMEFROMEMPWHEREEMP.D --- <?xml version="1.0"?> <ROWSET> <ROW> <DEPTNO>10</DEPTNO> <DNAME>ACCOUNTING</DNAME> <EMPLOYEE>
<EMPLOYEE_ROW> <EMPNO>7782</EMPNO> <ENAME>CLARK</ENAME> </EMPLOYEE_ROW> <EMPLOYEE_ROW> <EMPNO>7839</EMPNO> <ENAME>KING</ENAME> </EMPLOYEE_ROW> <EMPLOYEE_ROW> <EMPNO>7934</EMPNO> <ENAME>MILLER</ENAME> </EMPLOYEE_ROW> </EMPLOYEE> </ROW> </ROWSET>
Oracle 9i and 8i Database Limits
Oracle8i ONLY
Advanced Queuing Processes maximum per instance 10 Oracle8i ONLY
Job Queue
Processes maximum per instance 36 I/O Slave
Processes maximum per background process (DBWR, LGWR, etc.)
15
maximum per Backup
session Sessions maximum per
instance 32K, limited by PROCESSES and SESSIONS init parameters LCK Processes maximum per instance 10 MTS Servers maximum per
instance Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance Dispatchers maximum per
instance Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance. Parallel
Execution Slaves
maximum per
instance Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance. Backup
Sessions maximum per instance Unlimited within constraints set by PROCESSES and SESSIONS init
parame ters, for instance.
Oracle 9i Limits
Datatype Limits
VARCHAR2 Maximum size is 4000
NVARCHAR2 Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes
NUMBER( p,s) The precision p can range from 1 to 38. The scale s can range from -84 to 127
LONG up to 2 gigabytes, or 231 -1 bytes
DATE range from January 1, 4712 BC to December 31, 9999
AD
TIMESTAMP( fractional_seconds_precision) fractional_seconds_precisionis the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6
TIMESTAMP( fractional_seconds_precision) WITH
TIME ZONE fractional_seconds_precisionis the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6
TIMESTAMP( fractional_seconds_precision)WITH
LOCAL TIME ZONE same as TIMESTAMP( fractional_seconds_precision) WITH TIME ZONE INTERVAL YEAR( year_precision) TO MONTH year_precision is the number of digits in the YEAR
datetime field. Accepted values are 0 to 9. The default is 2
INTERVAL DAY (day_precision) TO SECOND
( fractional_seconds_precision) day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6
RAW( size) Maximum size is 2000 bytes
LONG RAW up to 2 gigabytes
ROWID Base 64 string representing the unique address of a
row in its table
UROWID [( size)] Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes
CHAR( size)[BYTE |CHAR] Fixed-length character data of length size bytes.Maximum size is 2000 bytes. Default and minimum size is 1 byte
NCHAR( size) Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character
CLOB Maximum size is 4 gigabytes
NCLOB Maximum size is 4 gigabytes
BLOB Maximum size is 4 gigabytes
BFILE Maximum size is 4 gigabytes
Physical Database Limits
Database Block Size Minimum
Maximum
2048 bytes; must be a multiple of operating system physical block size
Operating system dependent; never more than 32 KB Database Blocks Minimum in initial
extent of a segment Maximum per datafile
2 blocks
Platform dependent; typically 222-1 blocks Controlfiles Number of control
files
Size of a control file
1 minimum; 2 or more (on separate devices) strongly recommended
Dependent on operating system and database creation options; maximum of 20,000 x (database block size) Database files Maximum per
tablespace Maximum per database
Operating system dependent; usually 1022
65533. May be less on some operating systems Limited also by size of database blocks and by the
DB_FILES initialization parameter for a particular instance
Database extents Maximum 2 GB, regardless of the maximum file size allowed by the operating system
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M blocks
MAXEXTENTS Default value
Maximum
Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter Unlimited
Redo Log Files Maximum number of logfiles
Maximum number of logfiles per group
Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement. Control file can be resized to allow more entries; ultimately an operating system limit
Unlimited Redo Log File Size Minimum size
Maximum size
50 KB
Operating system limit; typically 2 GB Tablespaces Maximum number
per database 64 KB Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file
Logical Database Limits
GROUP BY clause Maximum length The GROUP BY expression and all of the nondistinct aggregates functions (for example, SUM, AVG) must fit within a single database block. Indexes Maximum per table
total size of indexed column
Unlimited
75% of the database block size minus some overhead
Columns Per table
Per index (or clustered index)
1000 columns maximum 32 columns maximum Per bitmapped index 30 columns maximum Constraints Maximum per column Unlimited
Subqueries Maximum levels of subqueries
in a SQL statement Unlimited in the FROM clause of the top-level query; 255 subqueries in the WHERE clause
Partitions Maximum length of linear partitioning key
Maximum number of columns in partition key
Maximum number of
4 KB - overhead
16 columns
partitions allowed per table or index
Rollback Segments Maximum number per
database No limit; limited within a session by MAX_ROLLBACK_SEGMENTS initialization