OraFAQ Forum: SQL & PL/SQL » Pass substitution variables from shell script to the SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Pass substitution variables from shell script to the SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pass substitution variables from shell script to the SQL [message #184151]

Tue, 25 July 2006 09:44

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.

Report message to a moderator

Re: Pass substitution variables from shell script to the SQL [message #184156 is a reply to message #184151]

Tue, 25 July 2006 10:00

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

Report message to a moderator

Re: Pass substitution variables from shell script to the SQL [message #184193 is a reply to message #184156]

Tue, 25 July 2006 12:30

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?

Report message to a moderator

Re: Pass substitution variables from shell script to the SQL [message #184206 is a reply to message #184193]

Tue, 25 July 2006 13:35

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}

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 09 05:50:16 CDT 2025