Create a spool file for each user 1

[acct098]

Does anyone know how to create a PL/SQL script that will create a file for each USERID_2? The file will contain all records for a specific user. I would also like the file to be the name of the userid_2 (XXXXMW1). A copy of the table is as follows:

EDATE USERID_2 B_ B_ B_NUM T_ AO T_NUM
--------- -------- -- -- ------ -- ---- ------------
15-NOV-06 XXXXAS2 TO 10 Z0525Z TO 10 654T62318
15-NOV-06 XXXXDCM CT 04 CT 04 1017R900206
15-NOV-06 XXXXMET ET 04 ET 04 101JV7018
15-NOV-06 XXXXMSS AY 04 AY 04 0151A1FIX01
15-NOV-06 XXXXMW1 AL 02 AL 02 01516GC0401
15-NOV-06 XXXXMW1 AL 20 AL 20 0137MIKE01
15-NOV-06 XXXXMW1 TA 04 TA 04 01656MBW001
15-NOV-06 XXXXTMH AL 04 AL 04 0151200002
15-NOV-06 XXXXTMH SA 40 SA 40 0167BCTST01
15-NOV-06 XXXXTMH SY 04 SY 04 01516000119

 

[Beilstwh]

You want to use a cursor and utl_file to do this. If you need help with the procedure, please give more information on the table that the data to be dumped is coming from and what directory ON THE DATABASE SERVER that you want to create the files in.

Bill
Oracle DBA/Developer
New York State, USA

 

[LKBrwnDBA]

Or you could try something likr this:

Set Pages 0 Feed Off Ver Off Term Off Trims On Lin 120
Spo /tmp/_MyScript.sql
Prompt Set Pages 55 Feed On Ver On Term On Trims On Lin 80
Select 'Spo '||Userid_2||Chr(10)
     ||'Select * From MyTable Where Userid_2='''||Userid_2||''';'||Chr(10)
     ||'Spo Off'
  From (Select Distinct Userid_2 From MyTable);
Spo Off  
@/tmp/_MyScript.sql

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Beilstwh]

Nice one, I didn't think of building the spooling on the fly. However, I would use the following. Use a group by instead of a sub-select.

Select 'Spo '||Userid_2||Chr(10)
     ||'Select * From MyTable Where Userid_2='''||Userid_2||''';'||Chr(10)
     ||'Spo Off'
from MyTable
group by userid_2
order by userid_2;

Bill
Oracle DBA/Developer
New York State, USA

 

[acct098]

Thank LK
*************************************************
Set Pages 0 Feed Off Ver Off Term Off Trims On Lin 200
Spo C:/ORACLE10G/BIN/SUSF_FILE.sql
Prompt Set Pages 55 Feed On Ver On Term On Trims On Lin 200
Select 'Spo '||Userid_2||Chr(10)
||'Select * From SUSF Where Userid_2='''||Userid_2||''';'||Chr(10)
||'Spo Off'
From SUSF
where substr(userid_2,1,4) = 'R047'
and edate = '15-NOV-06'
group by userid_2, edate, b_c, b_ao, b_num, t_c, ao, t_num
order by userid_2, edate, b_c, b_ao, b_num, t_c, ao, t_num
;
Spo Off
@C:/ORACLE10G/BIN/SUSF_FILE.sql
**************************************************

Two additional questions- These two should be simple.


1. How can I get the output spooled file to be .txt vs .lst?

2. How do I get rid of the following error?
18 rows selected.

SP2-0734: unknown command beginning "SQL> Spo O..." - rest of line ignored.

 

[LKBrwnDBA]

Try this:

Set Pages 0 Feed Off Ver Off Term Off Trims On Lin 200
Spo C:/ORACLE10G/BIN/SUSF_FILE.sql
Prompt Set Pages 55 Feed On Ver On Term On Trims On Lin 200
Select 'Spo '||Userid_2||'.txt'||Chr(10)
     ||'Select * From SUSF Where userid_2='''
     ||Userid_2||''';'||Chr(10)
  From SUSF
  where substr(userid_2,1,4) = 'R047'
  and edate = '15-NOV-06'
  group by userid_2, edate, b_c, b_ao, b_num, t_c, ao, t_num
  order by  userid_2, edate, b_c, b_ao, b_num, t_c, ao, t_num
/
Spo Off  
@C:/ORACLE10G/BIN/SUSF_FILE.sql

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[acct098]

LK

Thanks again!

Is there a book that you know of that can explain how to create scripts like the one you created?

 

[acct098]

LK

Same concept different file. I can the the sql to just select a specific date (TDATE). Any ideas why?

Set Pages 0 Feed Off Ver Off Term Off Trims On Lin 200
Spo C:/_AL_FILES/SQL/GLFCAT.SQL
Prompt Set Pages 55 Feed On Ver On Term On Trims On Lin 200
Select 'Spo '||FUNDCAT||'.txt'||Chr(10)
||'Select BOC From GLFCAT Where FUNDCAT='''
||FUNDCAT||''';'||Chr(10)
From GLFCAT
WHERE FUNDCAT IN ('F','G','S')
AND TDATE = '23-JUN-06'
GROUP BY FUNDCAT
;
/
Spo Off
@C:/_AL_FILES/SQL/GLFCAT.SQL

 

[LKBrwnDBA]

You have the date condition in the wrong place:

Set Pages 0 Feed Off Ver Off Term Off Trims On Lin 200
Spo C:/_AL_FILES/SQL/GLFCAT.SQL
Prompt Set Pages 55 Feed On Ver On Term On Trims On Lin 200
Select 'Spo '||FUNDCAT||'.txt'||Chr(10)
     ||'Select BOC From GLFCAT '||chr(10)
     ||' Where FUNDCAT='''||FUNDCAT||''''||Chr(10)
     ||'   And TDATE = ''23-JUN-06'''||chr(10)||'/'||chr(10)  
  From GLFCAT
  WHERE FUNDCAT IN ('F','G','S')
  GROUP BY FUNDCAT
/
Spo Off  
@C:/_AL_FILES/SQL/GLFCAT.SQL

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb