0
votes

I need to give the spool file name dynamically and I have to pass the parameters when I call sqlplus. Below is what I tried

echo exit | sqlplus "{{ Oracle_username }}/ {{ Oracle_pwd}} @(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host={{ Oracle_HostName }} )(Port=1521))(CONNECT_DATA=(SID= {{Oracle_SID }})))" @Script.sql 'AppName' 'DatabaseName' 'ObjectType'

Over here I tried to pass App Name, Database Name and Object Type dynamically. Prior to running SQLPLUS step, I create folders dynamically (App Name , Database Name , Object Type are all folders and it will vary depending on each application) .Below is how my script.sql looks like :

    SPOOL &&AppName/&&DatabaseName/&&ObjectType/Output.csv
    <<SQL Script>>
    SPOOL OFF

This doenst work . Can someone tell me what needs to be changed.

2
What does "doesn't work" mean - what happens? Are you defining your substitution variables somewhere? - Alex Poole
When I spool like above by passing parameters , Output is not exported . Without parameter , when I execute the script , it works . Ex: SPOOL AppName/Databasename/Output.csv . I am running all these steps from Ansible playbook . I will declare the values of all of this in a separate var file . - Prashanth_Ramanathan

2 Answers

0
votes

You are passing the values you want to form your spool file path and name as arguments to your script, but you need to refer to them as positional parameters:

SPOOL &1/&2/&3/Output.csv

Or if you're going to reuse them for something else you could define your own variable, set from the positional parameters:

DEFINE AppName=&1
DEFINE DatabaseName=&2
DEFINE ObjectType=&3

SPOOL &&AppName/&&DatabaseName/&&ObjectType/Output.csv

The spool file path will be relative to the directory you're in when you run the script. If that isn't what you want then put the root before the first substitution variable in the spool command, whichever form you use.


You could also include the exit in your .sql file so you don't have to echo it in; and you could use a TNS alias instead of passing all of the connection information on the command line - or if you can use a service name instead of a SID, you could use the easy connect syntax which is a bit simpler:

sqlplus username/password@//hostname:1521/service_name @Script.sql 'AppName' 'DatabaseName' 'ObjectType'
0
votes

set your appname,dbname,objecttype's as environmental variables and then Try like below

[oracle@ct-myhost-02 ~]$ export app_name=/stage
[oracle@ct-myhost-02 ~]$ export database_name=PSES
[oracle@ct-myhost-02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 1 12:04:08 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool $app_name/$database_name/out.csv
SQL> select * from dual;

D
-
X

SQL> spool off;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

[oracle@ct-myhost-02 ~]$ ls -l /stage/PSES/out.csv
-rw-r-----. 1 oracle oinstall 286 Feb  1 12:04 /stage/PSES/out.csv