Search code examples
oracle-databaseplsqlspool

PL/SQL how to spool multiple files?

Hy Guys, I don't understand how to create a correct script for spool, of multiple files. I have read some question about this problem, but I was unable to solve it.

this is my code MAIN_PROCEDURE.sql

SET serveroutput ON;
SET trimspool ON;
SET pagesize;

PROCEDURE DINAMIC_SPOOL(file_name VARCHAR2) IS
BEGIN
 dbms_output.put_line('spool '|| file_name || '.txt');
 dbms_output.put_line('bla bla bla');
 dbms_output.put_line('spool off');

END DINAMIC_SPOOL;


BEGIN
 file_name:='test_1';
 DINAMIC_SPOOL(file_name);

 file_name:='test2';
 DINAMIC_SPOOL(file_name);

END;
/
EXIT;

Otherwise, there is another method? I hope in your kind reply.

Solution

  • I don't think that what you try do can work.

    SPOOL is a SQLPlus command and as such can only be executed directly by SQLPlus.

    DBMS_OUTPUT a PL/SQL package that runs on the server: its output is retrieved by SQLPlus and displayed by SQLPlus but it does run on the database server side: and it cannot run SQLPlus commands.