Oracle How to Use Spool with Dynamic Spool Location

ORACLE How to use spool with dynamic spool location

SPOOL is a SQLPlus directive and you can't mix it into the PL/SQL anonymous block. If you're going to do this purely in SQLPlus, I think the general idea would be to process in two passes, i.e. use a first script that dynamically generates the spool filename references into a second script that actually makes the dbms_metadata call.

[Edit]

This should be close to what you need - maybe a line termination problem, depending on your platform:

    set pagesize 0
    set linesize 300
    spool wrapper.sql
    select
    'spool '||object_name||'.sql'||chr(10)||
    'begin 
    dbms_metadata.get_ddl('||chr(39)||object_type||chr(39)||','||chr(39)||object_name||chr(39)||')'||' end;'||chr(10)||
    '/'||chr(10)||
    'spool off'
    from user_objects
    where object_type = 'PROCEDURE'
;
spool off

SQLPlus dynamic spool filename

You could use substitution variables and the new_value clause of the column command.

conn ....

column spool_path new_value sub_spool_path noprint
column sep new_value sub_sep noprint
set verify off
set termout off

select path || filename ||'.'|| extension as spool_path, separator as sep
from Table;

set termout on

set separator &sub_sep

spool &sub_spool_path
... select queries...
spool off;

Dynamic Spool to File name

Probably nothing. This is a little different that what @ruudvan says is a duplicate. You should be able to use substitution on the spool command the way you are doing it.

This was a bug in an earlier release. In the latest version of SQLcl, I've tried this and seems to work great.

(~/qa) $cat subnew.sql
SET ECHO  OFF
SET FEEDBACK OFF
SET VERIFY OFF
COL VPATH NEW_VALUE NEW_VPATH;
COL PRESIDIO NEW_VALUE NEW_PRESIDIO;
COL SEPARATORE NEW_VALUE NEW_SEPARATORE;
COL DATA NEW_VALUE NEW_DATA;
COL EXT NEW_VALUE NEW_EXT;
COL USER NEW_VALUE NEW_USER;
SET SQLFORMAT CSV;
SELECT './' VPATH, 'AO_GALLARATE' PRESIDIO,'_' SEPARATORE, TO_CHAR(CURRENT_DATE) DATA,
       '.CSV' EXT , USER FROM DUAL;
SET FEEDBACK OFF;
SET TERMOUT OFF;
SPOOL "&NEW_VPATH&NEW_PRESIDIO&NEW_SEPARATORE&NEW_DATA&NEW_EXT";
select /* csv */ * from substitution
where upper(name) = '&NEW_USER';
SPOOL OFF;
exit

(~/qa) $sql barry/oracle @subnew

SQLcl: Release 12.2.0.1.0 RC on Tue Aug 09 13:21:28 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 09 2016 13:21:29 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

"VPATH","PRESIDIO","SEPARATORE","DATA","EXT","USER"
"./","AO_GALLARATE","_","09-AUG-16",".CSV","BARRY"
(~/qa) $ls -altr AO_GALLARATE_09-AUG-16.CSV 
-rw-r--r--  1 bamcgill  staff  22  9 Aug 13:21 AO_GALLARATE_09-AUG-16.CSV
(~/qa) $cat AO_GALLARATE_09-AUG-16.CSV 
"ID","NAME"
1,"Barry"
(~/qa) $

Oracle Spool using Dynamic SQL

I think this is what you're trying to achieve:

set linesize 10000 pagesize 0 embedded on
set heading off feedback off verify off trimspool on trimout on  termout off
set underline off

SPOOL myfilename.sql

SELECT 'SELECT table_name||'',''||column_name FROM USER_TAB_COLS WHERE ROWNUM < 10;' gen_sql_ FROM DUAL;

SPOOL OFF

spool results.csv

@myfilename.sql

SPOOL OFF

I.e. first you spool the results of your query into a file, and then once the spool is complete, you call the script you just created, spooling the results of that into a separate file.

How to make column name dynamic with spool on ORACLE?

If you are using SQLPlus and you are speaking about substitution variables, you can make this:

column title_column1 new_value vc1
column title_column2 new_value vc2
column title_column3 new_value vc3
select title_column1,title_column2,title_column3 from test_columns;
select &vc1,&vc2,&vc3 from test_data;

SQL*PLUS - Create a new folder while spooling

you can use the HOST command for that. It calls out to the operating system. example for windows:

SQL>  HOST "md mydirectory"

Spooling to a file with a name containing a space and script's parameter in sqlplus?

OK, I found the answer in comments to https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3581757800346555562 .

The author of the original script has changed the defaults, so that I have to use

spool ^filename

instead of

spool &filename

In case you have similar problem, the orginal code contained:

set define "^"
set sqlprefix "^"

And that was the reason I could not get the file created.

---

Related Topics