Hello.
I want to make a file about result that I query sales table.
But I have a problem.
my_output.log file size is very big.
Bottom is my PLSQL.
I want to automatically separate output file by size or line count.
I mean If my_output.log file size is over 3GB, Next file is created as my_output1.log, next my_output2.log...
Or If sql read data over 300000 line, Next file is created as my_output1.log, next my_output2.log...
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;