[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exporting data from Oracle to a XML

Posted on 2014-08-06
21
Medium Priority
?
516 Views
Last Modified: 2014-08-26
Greeting,

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

Thanks.
0
Comment
Question by:mrong
  • 10
  • 7
  • 4
21 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40244278
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
 

Author Comment

by:mrong
ID: 40244428
Simple as select dbms_xmlgen.getxml('select * from some_table') from dual;

Thanks.
0
 

Author Comment

by:mrong
ID: 40244431
I also need to know how to dumo the xml file to a remote server.
Thanks.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40244434
>>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
 

Author Comment

by:mrong
ID: 40244448
How to setup the spooling automatically?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40244463
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40244467
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 40244478
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
 

Author Comment

by:mrong
ID: 40245797
sdstuber,

The sample code you provided is a trigger stored in oracle?
thanks.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40245930
no it's a package, but it is stored in oracle
0
 

Author Comment

by:mrong
ID: 40273842
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40273974
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
 

Author Comment

by:mrong
ID: 40274029
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40274039
You added double quotes not two single quotes.

Change the one " to TWO '
0
 

Author Comment

by:mrong
ID: 40274059
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40274076
In the sqlplus script add:
set long 1000000000
set longchunk 1000000000
0
 

Author Comment

by:mrong
ID: 40286450
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40286483
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
 

Author Comment

by:mrong
ID: 40286957
Can you provide a sample code for using dbms_scheduler to run such stored procedures?
Thanks.
0
 

Author Comment

by:mrong
ID: 40286965
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40286969
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question