XML

DECLARE
      F UTL_FILE.FILE_TYPE;
      MYCLOB CLOB;
  BEGIN
      SELECT
DBMS_XMLGEN.GETXML('SELECT FIRST_NAME, SALARY FROM EMP WHERE ROWNUM <= 5')
      INTO MYCLOB
      FROM DUAL;

      F := UTL_FILE.FOPEN('NEW','EMP_DEPT.XML','w',32767);
      UTL_FILE.PUT(F,MYCLOB);
      UTL_FILE.FCLOSE(F);
  END;
  /

INSERT INTO TEST_XML (A)
VALUES ('<ROWSET>
 <ROW>
  <FIRST_NAME>Steven</FIRST_NAME>
  <SALARY>25000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Neena</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Lex</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Michael</FIRST_NAME>
  <SALARY>13000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Steven</FIRST_NAME>
  <SALARY>6000</SALARY>
 </ROW>
</ROWSET>');






SELECT extract(value(d), '//FIRST_NAME/text()').getStringVal() AS FIRST_NAME,
       extract(value(d), '//SALARY/text()').getNumberVal() AS SALARY
FROM   xml_tab x,
       table(xmlsequence(extract(x.xmlval, '/ROWSET/EMP'))) d;


CREATE TABLE T_XML_ORDERS(ORDERS XMLTYPE);

CREATE OR REPLACE PROCEDURE SP_BULK_ORDERS
(
IN_ORDERS IN CLOB,
OUT_ORDER_DETAILS OUT SYS_REFCURSOR
)
AS
BEGIN

INSERT INTO T_XML_ORDERS VALUES (XMLTYPE(IN_ORDERS));

COMMIT;

OPEN OUT_ORDER_DETAILS FOR 
SELECT extract(value(d), '//EXCHANGE/text()').getStringVal() AS EXCHANGE,
       extract(value(d), '//ORDER_ID/text()').getNumberVal() AS ORDER_ID
FROM   T_XML_ORDERS x,
       table(xmlsequence(extract(x.ORDERS, '/ROWSET/ROW'))) d;


END;
/

VARIABLE X REFCURSOR;

DECLARE

I CLOB := '<ROWSET>
 <ROW>
  <FIRST_NAME>Steven</FIRST_NAME>
  <SALARY>25000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Neena</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Dinesh</FIRST_NAME>
  <SALARY>17000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Alexander</FIRST_NAME>
  <SALARY>9000</SALARY>
 </ROW>
 <ROW>
  <FIRST_NAME>Bruce</FIRST_NAME>
  <SALARY>6000</SALARY>
 </ROW>
</ROWSET>';

BEGIN

SP_PLACE_ORDERS(
 IN_ORDERS=>I,
 OUT_ORDER_DETAILS=>:X
);

END;
/


PRINT X;


CREATE TABLE T1(ORDERS XMLTYPE);

create or replace procedure sp_place_orders(in_orders in clob, o out sys_refcursor)
as
x xmltype;
begin
x := xmltype(in_orders);
execute immediate 'truncate table t1';
insert into t1 values (x);
open o for
SELECT extract(value(d), '//FIRST_NAME/text()').getStringVal() AS FIRST_NAME,
       extract(value(d), '//SALARY/text()').getNumberVal() AS SALARY
FROM   t1 x,
       table(xmlsequence(extract(x.orders, '/ROWSET/ROW'))) d;
end;
/

variable j refcursor;
declare
i clob := '<?xml version="1.0"?> <ROWSET> <ROW> <FIRST_NAME>Steven</FIRST_NAME> <SALARY>24000</SALARY> </ROW> <ROW> <FIRST_NAME>Dinesh</FIRST_NAME> <SALARY>17000</SALARY> </ROW> <ROW> <FIRST_NAME>Hari</FIRST_NAME> <SALARY>17000</SALARY> </ROW> <ROW> <FIRST_NAME>Alexander</FIRST_NAME> <SALARY>9000</SALARY> </ROW> <ROW> <FIRST_NAME>Bruce</FIRST_NAME> <SALARY>6000</SALARY> </ROW> </ROWSET>';
begin
sp_place_orders(in_orders=>i,o=>:j);
end;
/

print j;



select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;


ALTER SYSTEM KILL SESSION '30,666' IMMEDIATE; 

LOCK TABLE EMPLOYEES IN SHARE MODE NOWAIT;