PL/SQL how to spool multiple files?

Question

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.

score 2 · Answer 1 · edited May 26 '20 at 05:06

PL/SQL procedures live on the database server. You write procedures and packages which access the database and can - with some effort, for instance with the package UTL_FILE- write files on the database server.

SQL*Plus lives on the client. It can access the database and spool the output of queries to files on the client. However, it is not a full scripting language, there are no loops, no if/then, no procedures, just plain SQL in in SQL*Plus.

So to speak, SQL*Plus is SQL plus some formatting/spooling extras, and PL/SQL is the (P)rocedural (L)anguage for (SQL).

score 1 · Accepted Answer · answered May 19 '20 at 09:28

1

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.

answered May 19 '20 at 09:28

pifor

7,419
2
8
16

is there another method? – Stefano Bombo Bombonati May 19 '20 at 09:59
Maybe you can try to generate a SQL file with mutiple SPOOL statements: it depends on the context. – pifor May 19 '20 at 10:02
all it's started to this example : https://stackoverflow.com/questions/4148458/spooling-multiple-files. I just don't understand the solution – Stefano Bombo Bombonati May 19 '20 at 10:06
This example is using DBMS_OUTPUT and PL/SQL code generate a SQL file named `temp_script.sql` and they run it with `@temp_script.sql` in SQLPlus: as long as the generated SPOOL statements are OK, this is OK. – pifor May 19 '20 at 11:26

PL/SQL how to spool multiple files?

2 Answers2