How to select spool's filename from table in sqlplus

Question

how could I select the name of my spool filename from a table in sqlplus?

I have something like:

SPOOL ON
SPOOL XXXXXXXXX.bkp
SELECT * FROM my_table WHERE field1='value';
SPOOL OFF

And want the XXXXXXXXX.bkp to have the value of a field from this same table.

Regards, Fernando

Answer 1

You can use the SQLPLUS "new_value" feature to save the column value into a sqlplus variable and then spool it as below.

SQL> create table spool_file (
  2       id number,
  3       file_name varchar2(200)
  4  );

Table created.

SQL> insert into spool_file values (1, 'test_spool_123.txt');

1 row created.

SQL> commit;

Commit complete.

SQL> column file_name new_value file_name;
SQL> select file_name from spool_file
  2    where id = 1;

FILE_NAME
--------------------------------------------------------------------------------
test_spool_123.txt

SQL> spool '&file_name';
SQL> select object_id from dba_objects
  2  where rownum < 10;

 OBJECT_ID
----------
        28
        49
        11
         6
         3
        52
        40
        19
        59

9 rows selected.

SQL> spool off;

And here are the contents from my spool file.

SQL> select object_id from dba_objects
  2  where rownum < 10;

 OBJECT_ID                                                                      
----------                                                                      
        28                                                                      
        49                                                                      
        11                                                                      
         6                                                                      
         3                                                                      
        52                                                                      
        40                                                                      
        19                                                                      
        59                                                                      

9 rows selected.

SQL> spool off;

You might also want to look at the UTL_FILE Package and its subprograms if you need greater control over your writing to files.