SQL & PL/SQL :: Spooling Data In Pipe Delimited File
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
ADVERTISEMENT
Oct 20, 2011
I have a task to code a procedure and function in sql developer that will extract data within a date range (Jan 1 to April 3) from a source (source_name: expenses)and produce a text-file in pipe-delimited format.
View 2 Replies
View Related
Nov 12, 2010
I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
View 9 Replies
View Related
Aug 8, 2012
using SQLLDR: Looking for a control file solution to move past or bypass extra data fields which are not on destination table. Basically if you have 8 tab delimited fields(terminated by ' ') on a data record; but only need to load 5 of the values from the delimited record; is there a way to ignore/bypass the not needed data. Obviously, the answer would be to massage the data at the OS and removed the 3 unnecessary fields.
However my hands are tied by volume,time, and compliancy. I am familiar with using 'FILLER' for the reverse scenario; but not where you have more data available on the record then exists on the table.
View 1 Replies
View Related
Mar 17, 2013
A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.
View 9 Replies
View Related
May 8, 2013
Need to transform a fixed delimited file to an XML format.
A WSDL file is given which is composed from a header and body. We need to map the fixed file to the body node.
Let me know the steps and also a sample xml for the same if possible.
View 6 Replies
View Related
Jan 24, 2013
I need to load csv file using an external table.
Structure of External Table:
---------------------------
create table A (col1 varchar2(30), col3 varchar2(30), col5 varchar2(30));
CSV FILE:
-----------
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
The table data should look like
COL1 COL3 COL5
A C E
1 3 5
need to skip the columns in CSV file.
View 5 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
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
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
View Related
Sep 24, 2012
SET serveroutput on size 1000000
SET wrap on
SET linesize 80
DECLARE
v_text VARCHAR2 (4000);
v_res NUMBER;
v_num NUMBER;
[code]...
View 6 Replies
View Related
Feb 27, 2011
Pipe Function and Materialized View.
There is a materialized view:
CODECREATE MATERIALIZED VIEW MY_MAT_VIEW NOLOGGING
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 5/24/60
AS
SELECT F1,
F2,
F3
from SOMETAB;
And there is a pipelined function:
CODEDROP TYPE MY_MAT_VIEW_table_type;
DROP TYPE MY_MAT_VIEW_row_type;
CREATE TYPE MY_MAT_VIEW_row_type AS OBJECT
(
F1 number,
F2 varchar2(100),
F3 date
[code].......
I've noticed that when materialized view is near to be refreshed (every 5 minutes) there is some "delay" in getting result sets using this query:
SELECT *
FROM TABLE(FN_GET_MY_MAT_VIEW(100)) a;
And when the refresh is finished the result set returns immediately.
Question.
Is there a way to avoid such "delay" while materialized view is refreshing?
View 1 Replies
View Related
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
I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.
Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.
Below is the test case:
CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),
[code]....
View 13 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
Feb 1, 2011
'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :
SELECT *
FROM TABLE(
DATA_UNLOAD(
CURSOR(
SELECT /*+ PARALLEL(A, 2, 1) */
TABLE_NAME || '|' ||
COLUMN_NAME || '|' ||
DATA_TYPE
FROM MYTABLE A
[code]....
It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work
View 2 Replies
View Related
Jun 9, 2010
Is there any possible to export & import the data using unix pipe parallel with two server( at the same time)?
Eg. In Server A, i will export the data & import the data in server B at the same time. (300 GB data)
View 2 Replies
View Related
Jan 31, 2013
In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query
SELECT * from TABLE(SPLIT('bbb003,bb004'));
out put is
bbb003
bb004
now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.
SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));
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
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
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
Mar 15, 2011
I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
View 2 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
Jul 18, 2013
I need to update a column with tab delimited ie that column value should be tab delimited. Eg:- Url_name is the column and column values =[URL]. THe space between [URL] should be tab delimited. I wanted to use a update query to update this values with tab delimited.
View 2 Replies
View Related
Sep 4, 2013
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
View 10 Replies
View Related
Jan 10, 2012
I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
View 4 Replies
View Related
Mar 3, 2010
I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
View 10 Replies
View Related
May 31, 2011
how can I convert
select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
to
select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
?
I would prefer sql then plsql. Script for creating a test table:
create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');
View 3 Replies
View Related
Aug 6, 2013
I have a table that has about 20,000 rows.
There is a column called Keyword which has values like below:
File_IDKeyword1SMITH;ALLEN;WARD;JONES; BRADY2S&P500;TOPIX3SMALL;LARGE;MEDIUM
I want to output the data like this: FILE_IDKEYWORD1SMITH1ALLEN1WARD1BRADY2S&P5002TOPIXetc
I'm using this query and it works: SELECT STG.FILE_ID, REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) AS KEYWORD FROM STG_TABLE STGCONNECT BY REGEXP_SUBSTR(STG.KEYWORD,'[^;]+', 1, LEVEL) IS NOT NULL
But its sooooo slow, its unusable. Is there a quicker way to return this output? Other info:KEYWORD is varchar2(4000) but rarely more than 100 bytes are usedOracle 11g2 !
View 5 Replies
View Related
