Oracle SQLPlus – SQL Spool FILE in Batch File

oraclesqlplus

*/

SET colsep ';'
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 800
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET NUMW 20

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT '.csv' spool_extension
FROM dual;
/

SPOOL \\SERVERNAME\FOLDER\FOLDER\FOLDER...ETC\InternationalReport&&suffix
@@"C:\FOLDER\FOLDER\International_report\International";
/
Exit;

Currently, it will spool the .csv file but I need it to have an output of InternationalReportyyyymmdd.csv

Any ideas on how exactly to set this up? Thanks!

Best Answer

column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension
FROM dual;

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension -- Solved the problem after I added TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')

Hope this can help someone else who is looking for a solution

Related Solutions

Sqlplus parameterized query via file

If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:

variable p_owner varchar2(30);
variable p_column varchar2(30);

set verify off
set feedback off

begin
    :p_owner := '&1';
    :P_column := '&2';
end;
/

set feedback on

select table_name
from all_tab_columns
where owner = :p_owner
and column_name = :p_column;

exit 0;

And put the parameter values in a text file, say parms.txt:

SYS
OSUSER

And put it together with:

sqlplus -s user/password @query `cat parms.txt`

(This is for Unix, obviously; not sure off-hand what the command-line equivalent is for Windows). Change parms.txt, or use different files, and you'll still get the same hard-parsed plan each time.

Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description. The execution plan is determined by the first query run, which might choose suitable indexes etc. for those parameters; subsequent runs then use the same plan even if different indexes might be more suitable for the new parameters. The stand-alone straight SQL will have a separate parse and thus possibly different (more suitable) plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.

Message file not found when starting SQLPlus

This error usually occurs if you are executing sqlplus as a different user (other than the one which installed Oracle software), due to permission issues

Check Oracle Note: 356850.1

Best Answer

Related Solutions

Sqlplus parameterized query via file

Message file not found when starting SQLPlus

Related Question