Home » SQL & PL/SQL » SQL & PL/SQL » Can someone help me with this? (Oracle 10g)
Can someone help me with this? [message #350383] Wed, 24 September 2008 21:12 Go to next message
Anthrax
Messages: 8
Registered: June 2006
Junior Member
hi there,

im running oracle 10g and need to extact some relational data from a table and convert it into XML and have the xml file exported to the local drive.

i found the below code at http://www.vjrao.com/node/16 and i think it does what i want it to but i cant get it to work.
CREATE OR REPLACE PROCEDURE table_to_xml_out
( sdate varchar2,file_dir varchar2,file_name varchar2)

IS
begin
declare
g_xml clob;
l_ctx dbms_xmlquery.ctxtype;
BEGIN

l_ctx := dbms_xmlquery.newcontext('SELECT empno from emp emp_date = :m_date ');
--bind variable
DBMS_XMLQuery.setBindValue(l_ctx,'m_date',to_date(sdate,'mm/dd/yyyy'));
--gets all the data into clob
g_xml := DBMS_XMLQuery.getXML(l_ctx);
dbms_xmlquery.closecontext(l_ctx);
--call another procedure to write the clob into a file in batches
clob_to_file(file_dir,file_name||'.xml',g_xml);
END;

END table_to_xml_out;

create or replace procedure clob_to_file( p_dir in varchar2,
p_file in varchar2,
p_clob in clob )
is
l_output utl_file.file_type;
l_amt number default 32000;
l_offset number default 1;
l_length number default
nvl(dbms_lob.getlength(p_clob),0); --gets the total length of the clob
BEGIN
l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
--this loop passes 32000 bytes from the clob till the end.
while ( l_offset < l_length )
loop
utl_file.put(l_output,
dbms_lob.substr(p_clob,l_amt,l_offset) );
--dbms_lob.read(file,amt, pos, buf);
utl_file.fflush(l_output);
l_offset := l_offset + l_amt;
end loop;
utl_file.new_line(l_output);
utl_file.fclose(l_output);
end;

i added utl_dir=c:\test to my init.ora file but it doesnt work.

i get the following error
SQL> exec table_to_xml_out('09/24/2008','c:\test\','testout')
BEGIN table_to_xml_out('09/24/2008','c:\test\','testout'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "SCOTT.CLOB_TO_FILE", line 11
ORA-06512: at "SCOTT.TABLE_TO_XML_OUT", line 18
ORA-06512: at line 1


im not sure if im sending the correct parameters, so any help would be very useful Smile

im using the scott\tiger schema to test this on

[Updated on: Wed, 24 September 2008 21:15]

Report message to a moderator

Re: Can someone help me with this? [message #350435 is a reply to message #350383] Thu, 25 September 2008 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To answer your question. Directory must be either an Oracle directory (see CREATE DIRECTORY) or a directory named in UTL_FILE_DIR instance parameter.
Note that a procedure can only write to a local directory that is on server and not on client.

Finally, you can also use something like:
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Regards
Michel
Re: Can someone help me with this? [message #350515 is a reply to message #350435] Thu, 25 September 2008 04:41 Go to previous messageGo to next message
Anthrax
Messages: 8
Registered: June 2006
Junior Member
cool, thanks for your help i will give it a go tomorrow.

i dont suppose you know any easy way of getting the data out into a .xml file?

i could get it to work using spool but it would always just put in everything on the screen.
Re: Can someone help me with this? [message #350521 is a reply to message #350515] Thu, 25 September 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i could get it to work using spool but it would always just put in everything on the screen.

set termouf off


Regards
Michel
Re: Can someone help me with this? [message #350543 is a reply to message #350521] Thu, 25 September 2008 06:04 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi Michel ,

SQL>select * From emp ;

     EMPNO ENAME      JOB               MGR HIREDATE
---------- ---------- ---------- ---------- ---------
      7369 SMITH      CLERK            7902 17-DEC-80
      7499 ALLEN      SALESMAN         7698 20-FEB-81
      7521 WARD       SALESMAN         7698 22-FEB-81
      7566 JONES      MANAGER          7839 02-APR-81
      7654 MARTIN     SALESMAN         7698 28-SEP-81
      7698 BLAKE      MANAGER          7839 01-MAY-81
      7782 CLARK      MANAGER          7839 09-JUN-81
      7788 SCOTT      ANALYST          7566 19-APR-87
      7839 KING       PRESIDENT             17-NOV-81
      7844 TURNER     SALESMAN         7698 08-SEP-81
      7876 ADAMS      CLERK            7788 23-MAY-87
      7900 JAMES      CLERK            7698 03-DEC-81
      7902 FORD       ANALYST          7566 03-DEC-81
      7934 MILLER     CLERK            7782 23-JAN-82

14 rows selected.


but the query returning :

SQL>select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;

DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>



SQL>


what is wrong here ??
Re: Can someone help me with this? [message #350550 is a reply to message #350543] Thu, 25 September 2008 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set long 10000

Regards
Michel
Re: Can someone help me with this? [message #350665 is a reply to message #350383] Thu, 25 September 2008 18:43 Go to previous messageGo to next message
Anthrax
Messages: 8
Registered: June 2006
Junior Member
thank you so much for your help, i got it almost working.

i might rewrite the clob part to use the method you described.

Thanks Smile
Re: Can someone help me with this? [message #350704 is a reply to message #350383] Thu, 25 September 2008 23:56 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is some more detail on XML from Oracle if interested:

Kevin Meade's blog

Easy XML - Let the Database do the Work

Easy XML - a Programming Oriented Approach

Yes, there is a bit of reading here. But if you want to be in this business then get used to it. There is no substitute for self teaching.

Good luck, Kevin
Previous Topic: DIFFERENCE BETWEEN APPLICATION TRIGGER AND DATABASE TRIGGER
Next Topic: Error in Trigger
Goto Forum:
  


Current Time: Fri Dec 09 19:07:57 CST 2016

Total time taken to generate the page: 0.27174 seconds