Home » SQL & PL/SQL » SQL & PL/SQL » Pass substitution variables from shell script to the SQL
Pass substitution variables from shell script to the SQL [message #184151] Tue, 25 July 2006 09:44 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I have simple script that spool data into file and I need to pass substitution variables from the shell script to the SQL. How can I do this?
Here is my SQL script:

set pagesize 0;
set echo off;
set head off;
set linesize 1000;
set verify off;
set feedback off;

WHENEVER SQLERROR EXIT 1;

define dim = ||'|'||

select
SERVICE_ID &dim
SERVICE_ITEM &dim
REP_CD &dim
nvl(REJECT_CD, ' ')
from service
where SERVICE_ID >=&SERVICE_ID_FROM
and SERVICE_ID <=&SERVICE_ID_TO
/
spool off;

exit;

In shell script:

export SERVICE_ID_FROM=10072921
export SERVICE_ID_TO=10099206

sqlplus ${user} @HOME/extracts/test2.sql ${SERVICE_ID_FROM} ${SERVICE_ID_TO}

What am I doing wrong?


Thanks.




Re: Pass substitution variables from shell script to the SQL [message #184156 is a reply to message #184151] Tue, 25 July 2006 10:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
AS I remember, parameters passed into SQL scripts are referred to by their position in the command.

So:
SQLPLUS me/my_pwd@my_db my_script  myparam1 myparam2

would connect to My_db as user ME, load up my_script and execute it with parameter &1 = myparam1, and &2 = myparam2.

Also, I doubt your query will work.
It looks like you're trying to dynamically join the columns together, but all you will end up doing is giving every column the same alias, which will cause an error.

SQL> define dim = ||'|'||

  1  select to_char(sysdate,'yyyy') &dim
  2  ,to_char(sysdate,'Month') &dim
  3  ,to_char(sysdate,'Day') &dim
  4* from dual
SQL> /
old   1: select to_char(sysdate,'yyyy') &dim
new   1: select to_char(sysdate,'yyyy') ||'|'||
old   2: ,to_char(sysdate,'Month') &dim
new   2: ,to_char(sysdate,'Month') ||'|'||
old   3: ,to_char(sysdate,'Day') &dim
new   3: ,to_char(sysdate,'Day') ||'|'||
,to_char(sysdate,'Month') ||'|'||
*
ERROR at line 2:
ORA-00936: missing expression

SQL> select to_char(sysdate,'yyyy')
  2  ,to_char(sysdate,'Month')
  3  ,to_char(sysdate,'Day')
  4  from dual;

TO_C TO_CHAR(S TO_CHAR(S
---- --------- ---------
2006 July      Tuesday
Re: Pass substitution variables from shell script to the SQL [message #184193 is a reply to message #184156] Tue, 25 July 2006 12:30 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I am passing parameters into SQL scripts are referred
to by their position in the command (see shell), but
what I am doing wrong. For some reason my parameters
is not visible.

In the SQl script I have:
select ...
from service
where SERVICE_ID >=&SERVICE_ID_FROM
and SERVICE_ID <=&SERVICE_ID_TO

In shell script I'm doing export:

export SERVICE_ID_FROM=10072921
export SERVICE_ID_TO=10099206

And pass parameters into SQL scripts are referred to
by their position:

sqlplus ${user} @HOME/extracts/test2.sql
${SERVICE_ID_FROM} ${SERVICE_ID_TO}

It will connect to database and execute script with
parameters &SERVICE_ID_FROM=10072921, and
&SERVICE_ID_TO=10099206.

Is this correct?
Re: Pass substitution variables from shell script to the SQL [message #184206 is a reply to message #184193] Tue, 25 July 2006 13:35 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use &1...&n for each parameter you are passing:

select ...
from service
where SERVICE_ID >=&1
and SERVICE_ID <=&2

sqlplus ${user} @HOME/extracts/test2.sql ${SERVICE_ID_FROM} ${SERVICE_ID_TO}


Previous Topic: Parsing XML and populating the table
Next Topic: rtrim problem
Goto Forum:
  


Current Time: Fri Dec 02 12:02:36 CST 2016

Total time taken to generate the page: 0.17058 seconds