CODEWITHSUNDEEP
sqloracle-databaseplsql
kashi
kashi

Reputation: 83

how to generate multiple text files dynamically in Oracle

I have a table t1, which contains two columns i.e company and employee name. I want to generate the txt files for each company, and the files should contain the employees working in those companies.

Any suggestions on how to do this will be appreciated.

CREATE TABLE T1(COMPANY VARCHAR2(10) , ENAME VARCHAR2(100));

INSERT INTO T1 
SELECT 'A' , 'SSS' FROM DUAL
UNION
SELECT 'B' , 'AAA' FROM DUAL
UNION 
SELECT 'C' , 'FFF' FROM DUAL
UNION 
SELECT 'A' , 'KKK' FROM DUAL
UNION 
SELECT 'B' , 'LLL' FROM DUAL
UNION 
SELECT 'C' , 'EEE' FROM DUAL
UNION 
SELECT 'A' , 'UUU' FROM DUAL
UNION 
SELECT 'B' , 'WWW' FROM DUAL
UNION 
SELECT 'C' , 'TTT' FROM DUAL
UNION 
SELECT 'A' , 'MMM' FROM DUAL;

commit;

Expected results:

A.txt
KKK
MMM
SSS
UUU

B.txt
AAA
LLL
WWW

C.txt
EEE
FFF
TTT

Thanks

Thanks, i tried what you have suggested and i am getting the data on v_out.sql but files are not getting generated . i am getting the error at the end of v_out.sql

error

 Elapsed: 00:00:00.02
not spooling currently
SP2-0734: unknown command beginning "Elapsed: 0..." - rest of line ignored.

query

 set termout off
    set echo off
    set feedback off
    spool v_out.sql
    select * from
    (
    with files as ( select distinct company from t1 )
      select
      'set termout off '||chr(10)|| 
      'set serveroutput on '||chr(10)||
      'set echo off '||chr(10)||
      'set feedback off '||chr(10)||
      'spool '||company||'.dat'||chr(10)||
      'select * from t1 where company = '''||company||''';'||chr(10)||
      'spool off'
      from files
      );
     /
     spool off
    @v_out.sql;

anything wrong in this query?

Upvotes: 0

Views: 738

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Setting up your test data:

SQL> CREATE TABLE T1(COMPANY VARCHAR2(10) , ENAME VARCHAR2(100));

Table created.

SQL>
SQL> INSERT INTO T1
  2  SELECT 'A' , 'SSS' FROM DUAL
  3  UNION
  4  SELECT 'B' , 'AAA' FROM DUAL
  5  UNION
  6  SELECT 'C' , 'FFF' FROM DUAL
  7  UNION
  8  SELECT 'A' , 'KKK' FROM DUAL
  9  UNION
 10  SELECT 'B' , 'LLL' FROM DUAL
 11  UNION
 12  SELECT 'C' , 'EEE' FROM DUAL
 13  UNION
 14  SELECT 'A' , 'UUU' FROM DUAL
 15  UNION
 16  SELECT 'B' , 'WWW' FROM DUAL
 17  UNION
 18  SELECT 'C' , 'TTT' FROM DUAL
 19  UNION
 20  SELECT 'A' , 'MMM' FROM DUAL;

10 rows created.

SQL>
SQL> commit;

Commit complete.


SQL>

Here is the query:

SQL> with files as ( select distinct company from t1 )
  2  select
  3   'spool '||company||'.dat'||chr(10)||
  4   'select * from t1 where company = '''||company||''';'||chr(10)||
  5   'spool off'
  6  from files;

'SPOOL'||COMPANY||'.DAT'||CHR(10)||'SELECT*FROMT1WHERECOMPANY='''||COMPANY||'
-----------------------------------------------------------------------------
spool A.dat
select * from t1 where company = 'A';
spool off

spool B.dat
select * from t1 where company = 'B';
spool off

spool C.dat
select * from t1 where company = 'C';
spool off

Now you just spool THAT output to a file, and then run that file.

Upvotes: 3

Related Questions