Re: Help

There is a very nice script to dump a table to a column delimited flat file and produce a sql*loader control file at     

                  http://www.teleport.com/~jkstill/util

The output is put into a file using the SQL*Plus spool command.

manok (ruthless_at_infonie.fr) wrote:
: Hi,
: I use ORACLE 7.2 on UNIX and I have creates a SQL procedure that stocks data
: in a file text.
: Unfortunately, i'm limited to one million of characters while using the
: command
: 'set serveroutput on size 1000000'.
: I look for a solution to get round the problem knowing that I cannot use
: package UTL+AF8-FILE.
 

: Please help me +ACEAIQAhACEAIQ-

: PROCEDURE SQL :
 
: set verify off
: set termout on
: set serveroutput off
: set serveroutput on size 1000000
 

: accept can+AF8-ven
: accept deb+AF8-per
: accept fin+AF8-per
 

: spool toto.txt
 

: DECLARE
: cursor c1 is
: select distinct annee, axe+AF8-can, axe+AF8-pays
: from fact+AF8-a0+AF8-smp+AF8-ael+AF8-chi+AF8-uo26
: where axe+AF8-can in (+ACY-can+AF8-ven)
: and period between +ACY-deb+AF8-per and +ACY-fin+AF8-per
: and axe+AF8-cli +AD0- -1
: group by annee, axe+AF8-can, axe+AF8-pays+ADs-
: cur1+AF8-annee NUMBER(4)+ADs-
: cur1+AF8-axe+AF8-can CHAR(2)+ADs-
: cur1+AF8-axe+AF8-pays CHAR(2)+ADs-
 

: BEGIN
 
: open c1+ADs-
: loop
: fetch c1 into cur1+AF8-annee, cur1+AF8-axe+AF8-can, cur1+AF8-axe+AF8-pays+ADs-
: exit when c1+ACU-notfound+ADs-
 

: dbms+AF8-output.put+AF8-line(cur1+AF8-annee +AHwAfA- '+ADs-' +AHwAfA- cur1+AF8-axe+AF8-can +AHwAfA- '+ADs-' +AHwAfA-
: cur1+AF8-axe+AF8-pays)+ADs-
 

: end loop+ADs-
: close c1+ADs-
 

: for i in 1..50 loop
: dbms+AF8-output.put+AF8-line(' ')+ADs-
: end loop+ADs-
: END+ADs-
 

: /
: spool off
: clear screen

--