Home » SQL & PL/SQL » SQL & PL/SQL » wirting automatically multi files by utl_file.fopen (oracle database, 11g, window)
wirting automatically multi files by utl_file.fopen [message #665544] Sat, 09 September 2017 17:36 Go to next message
a76014
Messages: 1
Registered: September 2017
Junior Member
Hello.

I want to make files about result that I query sales table.

But I have a problem.

my_output.log file size is very big.

I want to automatically separate output file by size or line count.

I mean If my_output.log file size is over 3GB, Next reading data is created files as my_output1.log, next my_output2.log...

Or If sql read data over 3000000 line, Next reading data is created files as my_output1.log, next my_output2.log...


Bottom is my PLSQL.

How to make the PLSQL?



SQL> CREATE DIREECTORY TMP_DIR AS '/export_bk/tmp';

SQL> DECLARE

v_name VARCHAR2(320);

v_count CLOB;

v_type VARCHAR2(320);

l_file utl_file.file_type;

max_linesize number := 32000;

CURSOR c_sql IS

select a.type type , B.VALUE_STRING VALUE_STRING,A.name name ,A.count count

from

(select id,name,count,type from salse) a,

(select id,'exec '|| name ||' :='''||VALUE_STRING||''';' as VALUE_STRING from salse) b

WHERE A.id=B.id;

BEGIN

l_file := utl_file.fopen('TMP_DIR', 'my_output.log', 'W',max_linesize);

FOR r_sql IN c_sql LOOP

BEGIN

dbms_output.enable(10000000000);

v_name := 'My customer is = '||r_sql.name||';';

v_type := r_sql.type;

v_count := r_sql.count||';';

utl_file.put_line(l_file, v_name);

utl_file.put_line(l_file, v_type);

END;

END LOOP;

utl_file.fflush(l_file);

utl_file.fclose(l_file);

END;
Re: wirting automatically multi files by utl_file.fopen [message #665545 is a reply to message #665544] Sat, 09 September 2017 21:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://community.oracle.com/thread/4079575
Re: wirting automatically multi files by utl_file.fopen [message #665667 is a reply to message #665544] Thu, 14 September 2017 09:39 Go to previous message
JPBoileau
Messages: 88
Registered: September 2017
Member
It would be fairly simple to keep track of the bytes written (use "LENGTH()") and the lines written (use REGEXP_COUNT(x, CHR(13)).

Once either is nearing the limit, open a new file.

JP
Previous Topic: UTL_FILE.PUT. Need help to handle the line with more than 32767 characters
Next Topic: Adding ax extra column to the existing constraint
Goto Forum:
  


Current Time: Thu Mar 28 17:47:49 CDT 2024