SQL & PL/SQL :: Spooling From XML
Mar 29, 2010
I am using Oracle 10g and I have XML stored in column XMLTYPE of my table. I want the XML in a flat file and process it to C exe.
My XML Structure is like this
<Data>
<Tag1>
<id>val1</id>
</Tag1>
<Account>
<Number>1111119&</Number>
</Data>
When I try to spool the XML i get the o/p without new line format as
<Data><Tag1><id>val1</id></Tag1><Account><Number>1111119&</Number</Data>
I want the o/p with new line after every tag and replace character like & to & . i can use utl_file too but spool is easy
View 2 Replies
ADVERTISEMENT
Oct 11, 2012
I need to create a program wherein it will create an excel files through spool command for every 25 records only. So if my select statement will return 100 records, it will create 4 excel files, 25 records for each excel file. i have these sample codes,it should be the logic, but then i'm having syntax error.
set serveroutput on size 1000000
SET PAGESIZE 0
SET LINESIZE 299
[Code].....
View 8 Replies
View Related
Jun 17, 2013
I have been trying to spool a table into a .lst. The table is spooled correctly, but there is a column which has xmlType data into. The problem is it does not write the xml into a single row. Every time it find a xml node, the spooling gets indented.
My code is as follows:
-- Establece opciones de formato.
set echo off;
set feedback off;
set heading off;
set recsep off;
set verify off;
set embedded off;
set long 1000000;
set pagesize 0;
SET LINESIZE unlimited;
set trimout off;
set trimspool on;
set serveroutput on size unlimited;
set term on;
-- Definicion de variables del script
prompt Introducir Creador de la tabla
prompt Creador de la tabla: &&1
prompt Introducir Nombre de la tabla
prompt Nombre de la tabla: &&2
prompt Introducir Directorio del fichero
prompt Directorio del fichero: &&3
prompt Introducir Nombre del fichero
prompt Nombre del fichero: &&4
prompt Introducir Caracter separador de campos
prompt Caracter separador de campos: &&5
prompt Campo fecha: &&6
-- Redirige la salida al fichero
spool /&&3/&&4
SELECT H5279_CD_EMP || '&&5' ||
H5279_LOCAL_ID || '&&5' ||
H5279_LOG_TYPE || '&&5' ||
H5279_GLOBL_ID || '&&5' ||
TO_CHAR(H5279_CREATED, 'YYYY-MM-DD HH24:MI:SS.FF3') || '&&5' ||
[Code] .......
where the field LOGDATA contains the xml. And the output im getting is as follows(I have attached the file)
0049|16052B0E44E3A30037E3CB59|4|16052B0E44E3BF0037D9CB4E|2013-06-12 11:51:16.387|16052B0E44E3A90037E2CB58|n31001|172.31.184.2|trxOpService|BDPESP_CIC_SAN_ENS_V1|||INTERNET|ALN_BDPGPO_Localizperscomun|| OI_LocalizarPersPorDocumentoComun|||ISWSBKSD149|frNixc6Vlic01jhixt5TkfT|16052A9553AE930037FAC740| <?xml version="1.0" encoding="ISO-8859-1"?>
[Code]....
View 5 Replies
View Related
Sep 13, 2012
Im trying spooling a file in DB with below mentioned details.
SET SERVEROUTPUT OFF
set heading on
SET PAUSE OFF
SET FEED OFF
SET PAGES 0
[code]........
file .sql has 20 odd columns.
When file is spooled, it shows no columns headers at the top, why is it happening, I don't want to spool column headers separately.
View 12 Replies
View Related
Dec 25, 2010
i have a requirement..i have a script which retrieves data from different table and my requirement is using spool, i need the data from each table to be spooled into different sheets in the same excel sheet.
CODEspool d:\spool\spool1.xls
select * from tab1;
select * from tab2;
select * from tab3;
spool off
in the above code, data from tab1 should be spooled into sheet1 and data from should be spooled into sheet2 and so on.. in the same excel.
View 7 Replies
View Related
May 12, 2010
I am an IT auditor who has been using Oracle 10g Express to test some audit scripts we have created. I write the output of each query in the script to a separate file because it makes it easier for us to go through. However, I cannot get SQLPlus to spool the output files at all. I do not get any errors from running the script, and I can see the results in the SQLPlus window, but I cannot find where the files are actually spooling to, if they are spooling at all.
I didn't specify a network path for any of the spool files because the scripts need to be as generic as possible so they can run on any Windows or UNIX box. An example of my code is below:
spool Audit_Ver
SELECT * FROM V$VERSION;
spool off
But if I search my harddrive for "Audit_Ver" no such file can be found anywhere.
View 7 Replies
View Related
Feb 15, 2010
I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records. Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) . I have following sql setting in my spool file:
set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv
I tried increase linesize to 5000 but its not working.
Ex.
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
Data should be like into the file:
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
I think it should be something with linesize or pagesize but not sure
View 18 Replies
View Related
Jan 27, 2010
i have a issue that when i spool the sql command i get a doted lines below the heading. how to eliminate the doted line but need the heading.
Previous:
OCCUPACODE OCCUPADESC
-------------------- -----------------------------------
1 BUSINESS
2 SERVICE
Needed :
OCCUPACODE OCCUPADESC
1 BUSINESS
2 SERVICE
View 1 Replies
View Related
Feb 6, 2013
I need to spool data from a remote server using putty(sqlplus) to a local machine. There are credentials i need to give before accessing the remote databases and i am able to do it..i tired with the below query but the spool file(csv or txt) is not able to create on local machine.
set colsep ,
set pagesize 120
set trimspool on
set headsep off
set linesize 1000
set numw
spool D: estmyfile.csv
select table_name, tablespace_name from all_tables;
spool off
View 2 Replies
View Related
Sep 20, 2013
I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues. I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.
public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)
throws Exception { logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());
FileInputStream is = new FileInputStream(pDoc);
// Create the print job DocPrintJob job = pService.createPrintJob();
//Set print request attributes with file name as job
[code].....
View 0 Replies
View Related
Oct 24, 2011
Is it possible to ignore the sql command prompt and the command entered while spooling. I tried the to set echo off,term off and some more options but it did not work.
Example:
SQL> spool c: est.log
SQL> Prompt "This is a test"
"This is a test"
SQL> spool off;
The spool file has the following
SQL> Prompt "This is a test"
"This is a test"
SQL> spool off;
Is it possible to set some options so that the spool file only contains output.
"This is a test"
View 3 Replies
View Related
