Hello everyone,
I am fairly new to plsql coding. And I am trying to solve a simple problem by writing a plsql snippet.
All I am trying to do is to print the output of a query to a file using utl_file. I am getting UTL WRITE error as the file is above 32767 b.
Here's the code.
sqlplus -s "/ as sysdba"<< EOF
DECLARE
-- file Handlers
fileH_exp_par UTL_FILE.FILE_TYPE;
fileH_purge_tables UTL_FILE.FILE_TYPE;
--file_str varchar2(4000);
v_first_line boolean;
BEGIN
fileH_exp_par := utl_file.fopen('DATA_PUMP_DIR', 'exp_par', 'W');
fileH_purge_tables := utl_file.fopen('DATA_PUMP_DIR', 'purge.sql', 'W');
utl_file.put_line(fileH_exp_par, 'DUMPFILE=my_bkp.dmp');
utl_file.put_line(fileH_exp_par, 'directory=DATA_PUMP_DIR');
utl_file.put_line(fileH_exp_par, 'LOGFILE=my_bkp.log');
FOR c1 IN (select object_name from my_table) LOOP
utl_file.put_line(fileH_purge_tables,' drop table '||c1.object_name||' CASCADE CONSTRAINTS PURGE;');
file_str := file_str || to_char(c1.object_name)||',';
END LOOP;
file_str := substr(file_str,1,length(file_str)-1);
utl_file.put_line(fileH_exp_par, 'tables='||file_str);
utl_file.fclose(fileH_exp_par);
utl_file.fclose(fileH_purge_tables);
END;
/
How can i modify the program to make it work using utl_file. Any suggestions or links to learning material will be very helpful.