Exporting data from Oracle to a XML

Greeting,

I need to export data(SQL select) from Oracle into a XML in daily basis.
Please suggest.

Thanks.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Please provide more information about your exact requirements.

If could be as simple as:
select dbms_xmlgen.getxml('select * from some_table') from dual;

or very detailed using the XMLELEMENT/XMLAGG/XMLFOREST/etc... SQL functions.
0
mrongAuthor Commented:
Simple as select dbms_xmlgen.getxml('select * from some_table') from dual;

Thanks.
0
mrongAuthor Commented:
I also need to know how to dumo the xml file to a remote server.
Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>how to dumo the xml file to a remote server.

Again, it depends on how you do things.

I would use sqlplus to spool out the output and ftp/rcp the file.
0
mrongAuthor Commented:
How to setup the spooling automatically?
0
sdstuberCommented:
You could generate the XML into a clob and FTP the clob directly to the remote site


An example of sending a clob is in the following article:

http://www.experts-exchange.com/Database/Oracle/A_3043-How-to-FTP-with-Oracle-PL-SQL.html
0
slightwv (䄆 Netminder) Commented:
There is no 'automatic' spooling.

In addition to spooling, you need to turn off some features of sqlplus and set up the environment to meet your needs.

If you have questions about what any of the below things do, the online docs has all the information:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037682

In sqlplus:
set lines 1000
set trimspool on
set pages 0
set feedback off

spool myfile.xml
select dbms_xmlgen.getxml('select sysdate from from dual') from dual;
spool off
0
sdstuberCommented:
A full example of using the sdsftp package with the get_xml might look something like this...


DECLARE
    v_conn sdsftp.connection;
    v_clob CLOB;
BEGIN
    v_clob := DBMS_XMLGEN.getxml('select * from user_tables');

    v_conn := sdsftp.open( :HOST, :username, :pwd);
    sdsftp.put_clob(v_conn, v_clob, 'tables.txt');
    sdsftp.close(v_conn);

    IF DBMS_LOB.ISOPEN(v_clob) = 1
    THEN
        DBMS_LOB.close(v_clob);
    END IF;

    DBMS_LOB.freetemporary(v_clob);
EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);

        IF DBMS_LOB.ISOPEN(v_clob) = 1
        THEN
            DBMS_LOB.close(v_clob);
        END IF;

        DBMS_LOB.freetemporary(v_clob);

        RAISE;
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrongAuthor Commented:
sdstuber,

The sample code you provided is a trigger stored in oracle?
thanks.
0
sdstuberCommented:
no it's a package, but it is stored in oracle
0
mrongAuthor Commented:
I use the following sql but gave me error on my single quote. how to fix it?

select dbms_xmlgen.getxml('select * from TBL1 where active='Y'') from dual;
0
slightwv (䄆 Netminder) Commented:
try:
select dbms_xmlgen.getxml('select * from TBL1 where active=''Y''') from dual;

note: those are ALL single quotes.  No double quotes.

You need two single quotes to get one in a string.
0
mrongAuthor Commented:
I tried the followings in SQL Plus worksheet

set linesize 1000;
set trimspool on;
set pages 0;
set feedback off;

spool C:\DATA.xml;
select dbms_xmlgen.getxml('select * from TBL1 where active="Y"') from dual;
spool off;


Got the following errors.

ERROR:
ORA-19202: Error occurred in XML processing
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y'
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
0
slightwv (䄆 Netminder) Commented:
You added double quotes not two single quotes.

Change the one " to TWO '
0
mrongAuthor Commented:
Should I see the actual data in the XML output file?
Below are what I got in XML.

select dbms_xmlgen.getxml('select * from TBL1 where status=''ACTIVE''') from dual;
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <REGION_CODE>RU</REGION_CODE>
  <FAC_ID>

spool off;
0
slightwv (䄆 Netminder) Commented:
In the sqlplus script add:
set long 1000000000
set longchunk 1000000000
0
mrongAuthor Commented:
sdstuber,

I assume the sdsftp code you suggested will generate XML file and put it on a remote FTP site.
Can we set up a schedule/time which your code will run?

Thanks.

Mark
0
sdstuberCommented:
sdsftp itself doesn't generate the XML, but the example I posted above ( http:#a40244478 )  shows one way to generate the xml and then use sdsftp to move the xml to a remote site.  

Note -there is no file anywhere except on the remote site using that method.

You can use a similar anonymous block inside a dbms_job or dbms_scheduler job.

Best would be to wrap it in a procedure and then use dbms_scheduler to run the procedure at whatever schedule you needed.
0
mrongAuthor Commented:
Can you provide a sample code for using dbms_scheduler to run such stored procedures?
Thanks.
0
mrongAuthor Commented:
I opened another question at the link below. Please help if you can. thanks.

http://www.experts-exchange.com/Database/Oracle/Q_28506085.html
0
slightwv (䄆 Netminder) Commented:
There are TONS of examples out there.

I would start with the online docs for dbms_scheduler.create_job:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235

An example is in another doc:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12384
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.