Wednesday 20 February 2013

Oracle: Querying XML from a CLOB (Part 2)

Using Oracle 11g again...Moving on from yesterdays XML examples, I added some more complexity and things started to go wrong!
  • Why am I getting null values back?
  • How do I get attribute values?
  • Does this method scale to multiple rows?
After some investigation...
I'm getting null values back because my new XML had a default namespace. A default namespace needs to be passed into the extract function, otherwise it returns null. I could just hard code this in, or have a variable, but I think that would be a pain, so I've added a new inline query to get the default namespace value dynamically

Getting attribute values is straight forward XPath stuff, just use the @ symbol. Easy.

But all of my data is being concatinated together in one row! Well of course is it, the table it's selecting from only has one row. So this needs the XML to be converted into a table.

OK Here we go.

1. Create the table (if not done so on the previous post)

CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB );
2. Insert some XML with 2 records, a default namespace and an attribute.

INSERT INTO xml_test VALUES ( 2, '<?xml version="1.0" encoding="iso-8859-1"?> <mydata MyAttribute="Fuzzy" xmlns:="http://www.abc.com"> <dude> <id>100</id> <name>John</name> <address> <line1>10 High Road</line1> <line2>London</line2> </address> <phone>123456789</phone> </dude> <dude> <id>101</id> <name>Frank</name> <address> <line1>11 High Road</line1> <line2>London</line2> </address> <phone>987654321</phone> </dude> </mydata>');
3. Query the data with the new query

WITH xmlData AS ( SELECT xt.id, XMLTYPE(xt.xml) xml FROM xml_test xt WHERE xt.id = 2 ), namespace AS ( SELECT xd.id, 'xmlns="'||XMLCAST(XMLQUERY('namespace-uri(.)' PASSING xd.xml RETURNING CONTENT) AS VARCHAR2(4000))||'"' defaultNs FROM xmlData xd ) SELECT xd.id, xd.xml.EXTRACT('//mydata/@MyAttribute',ns.defaultNs).GETSTRINGVAL() myAttribute, x.COLUMN_VALUE.EXTRACT('//dude/name/text()',ns.defaultNs).GETSTRINGVAL() dudeName, x.COLUMN_VALUE.EXTRACT('//dude/address/line1/text()',ns.defaultNs).GETSTRINGVAL() dudeAddress FROM xmlData xd, namespace ns, TABLE(XMLSEQUENCE(EXTRACT(xd.xml,'/mydata/dude',ns.defaultNs))) x WHERE xd.id = ns.id ORDER BY 1 ;
And you should get...

ID MYATTRIBUTE DUDENAME DUDEADDRESS -- ----------- -------- ------------ 2 Fuzzy John 10 High Road 2 Fuzzy Frank 11 High Road
Phew

Tuesday 19 February 2013

Oracle: Querying XML from a CLOB

Using Oracle 11g, a quick example of one way to query XML data from a CLOB.

1. Create the table.

CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB );
2. Insert some xml data

INSERT INTO xml_test VALUES ( 1, '<?xml version="1.0" encoding="iso-8859-1"?> <mydata> <id>100</id> <name>John</name> <address> <line1>10 High Road</line1> <line2>London</line2> </address> <phone>123456789</phone> </mydata>');
3. Query it

SELECT XMLTYPE(t.xml).EXTRACT('//name/text()').getStringVal(), XMLTYPE(t.xml).EXTRACT('//address/line1/text()').getStringVal() FROM xml_test t;
4. Alternative query

WITH xmlData AS ( SELECT XMLTYPE(t.xml) xml FROM xml_test t WHERE t.id = 1 ) SELECT x.xml.EXTRACT('//name/text()').getStringVal(), x.xml.EXTRACT('//address/line1/text()').getStringVal() FROM xmlData x ;
Next step: Load the XML data into the table from a file.

Oracle: Reading a Text File into a CLOB

Using Oracle11g, this procedure reads a text file, in this case an xml file, into a CLOB.

DECLARE l_clob CLOB; l_bfile BFILE := BFILENAME('SCRATCH','test.xml'); BEGIN INSERT INTO xml_test ( id, xml ) VALUES ( 2, EMPTY_CLOB() ) RETURNING xml INTO l_clob; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE ( dest_lob => l_clob, src_lob => l_bfile, amount => DBMS_LOB.GETLENGTH(l_bfile) ); DBMS_LOB.CLOSE(l_bfile); END; /
If you need to worry about different character sets etc, then use DBMS_LOB.LOADCLOBFROMFILE, which has several more parameters.

File Read and Inserted. See also: Querying XML from a CLOB

Friday 15 February 2013

Timing Sections of PLSQL Code

Your PLSQL routine is running like a dog with three legs? Don't know where to start looking as it could be any of a number of issues? Then adapt this timing code and drop it in for testing. Wrap the start and end calls around suspected sections of code to find where the hold up is occuring.

DECLARE ----------------------------- -- Timing Declaration Start TYPE tt_timingStart IS TABLE OF TIMESTAMP(9) INDEX BY VARCHAR2(30); l_timingStart tt_timingStart; TYPE tt_timingTotal IS TABLE OF NUMBER INDEX BY VARCHAR2(30); l_timingCount tt_timingTotal; l_timingTotal tt_timingTotal; -- PROCEDURE timingStart( p_timingIndex IN VARCHAR2 ) IS BEGIN BEGIN IF l_timingTotal(p_timingIndex) IS NULL THEN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN l_timingCount(p_timingIndex) := 0; l_timingTotal(p_timingIndex) := 0; END; l_timingStart(p_timingIndex) := SYSTIMESTAMP; END timingStart; -- PROCEDURE timingEnd( p_timingIndex IN VARCHAR2 ) IS l_duration INTERVAL DAY(9) TO SECOND(5) := SYSTIMESTAMP - l_timingStart(p_timingIndex); BEGIN l_timingCount(p_timingIndex) := l_timingCount(p_timingIndex) + 1; l_timingTotal(p_timingIndex) := l_timingTotal(p_timingIndex) + (EXTRACT(SECOND FROM l_duration)*1000) + (EXTRACT(MINUTE FROM l_duration)*60000) + (EXTRACT(HOUR FROM l_duration)*3600000) + (EXTRACT(DAY FROM l_duration)*86400000); END timingEnd; -- PROCEDURE timingOutput IS l_file UTL_FILE.FILE_TYPE; l_index VARCHAR2(30); BEGIN l_file := UTL_FILE.FOPEN('SCRATCH_DIR','TimingOutput.txt','W'); l_index := l_timingTotal.FIRST; WHILE l_index IS NOT NULL LOOP UTL_FILE.PUT_LINE(l_file,LPAD(l_index,20,'_')||': ' ||LPAD(ROUND(l_timingCount(l_index)),10)||'iterations' ||LPAD(ROUND(l_timingTotal(l_index)),10)||'ms' ||LPAD(ROUND(l_timingTotal(l_index)/1000),10)||'s' ||LPAD(ROUND(l_timingTotal(l_index)/60000),10)||'m'); l_index := l_timingTotal.NEXT(l_index); END LOOP; UTL_FILE.FCLOSE(l_file); END timingOutput; -- Timing Declaration End ----------------------------- BEGIN timingStart('Procedure'); FOR i IN 1..20 LOOP timingStart('First Sleep'); DBMS_LOCK.SLEEP(0.3); timingEnd('First Sleep'); timingStart('Second Sleep'); DBMS_LOCK.SLEEP(0.5); timingEnd('Second Sleep'); timingStart('Third Sleep'); DBMS_LOCK.SLEEP(0.1); timingEnd('Third Sleep'); END LOOP; timingEnd('Procedure'); timingOutput; END; /
This does assume there is a SCRATCH directory to output the file to. If you haven't got one it's easily modified to DBMS_OUTPUT.PUT_LINE.

Timed

Friday 8 February 2013

Oracle Processes CPU Usage

Is that long running job doing anything? This SQL query (Oracle 11) will list user processes and the amount of CPU usage the current job has used so far. Re-query to see how the CPU usage progesses.

SELECT vs.username, vs.sid, vs.serial#, vp.spid, vs.machine, vs.module, vs.program, vs.action, vs.status, vs.last_call_et, ROUND((vss_sess.value-vss_call.value)/100) "CPU Usage" FROM gv$session vs, gv$process vp, v$sesstat vss_sess, v$sesstat vss_call WHERE vs.paddr = vp.addr(+) AND vs.inst_id = vp.inst_id(+) AND vs.sid = vss_sess.sid AND vs.sid = vss_call.sid AND vss_call.statistic# = 13 -- CPU used when call started AND vss_sess.statistic# = 14 -- CPU used by this session AND vs.username IS NOT NULL ORDER BY ( CASE vs.status WHEN 'INACTIVE' THEN 0 ELSE vss_sess.value-vss_call.value END ) DESC, vs.status, vs.last_call_et;
Maybe useful here...

Several ways to kill a process.

The kill session syntax. The immediate option will not kill the session faster, however it will return the prompt immediately after the statement has been issued.

ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
The kill session statement asks the session to end, whereas the disconnect session statement cuts the session process.

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The post transaction option is equivilent to kill session.

For those even more stubborn processes, you need to move to the operating system, using the spid value from the query above.

In Windows:
orakill ORACLE_SID spid
In UNIX:
kill -9 spid

Done and Killed (hopefully).