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.

Answer 2

I'm not 100% sure what you're trying to do, but if you want a variable filename in your spool file you can do this:

col dat1 NEW_VALUE v_start_time

select to_char(sysdate,'YYYY-MM-DD_HH24MISS') dat1 from dual;

spool test_script_&v_start_time..txt

/*
other stuff...
*/
spool off

This was an example taken from a script I have that adds a timestamp to the file name to make multiple output files easier to manage. Note the .. in the spool [filename] command.

Since you say you want an actual field name in the filename, you will have to change my query to something that queries against the Oracle table that stores all the field names (I can't remember its name right now), but that should be pretty easy.