How to pass a shellscript variable to a sql file?

Shell Programming and Scripting
1

Hi,

i wan't to pass a shellscript variable to a sql file.

a.sql

select $field from dual;

the way i am calling this is through sqlplus

field_name="sysdate"
sqlplus -s username/password@hostname:port/servicename <<EOF
@a.sql $field_name
EOF
2

You cannot pass an argument to sql script. Try this

field_name="sysdate"
sql=$( sed "s/\$field/$field_name/" a.sql)
sqlplus -s username/password@hostname:port/servicename <<EOF
$sql
EOF
3

how will i be able to pass parameter to this sql file. what i want is to removed the prompt command and replace it with a unix variable say client_id

main.sql

set verify off
set serverout on
set timing on
set linesize 120
undefine client_id
accept client_id prompt 'Enter Client ID for EDW On-boarding:'
@create_1.sql &client_id
@create_2.sql &client_id
@create_3.sql &client_id
@create_4.sql &client_id
@create_5.sql &client_id
4

If I understand correctly:

$ cat a.sql
select &1 from dual;
$ field_name=sysdate
$ sqlplus -s '/ as sysdba' <<EOF
> @a.sql "$field_name"
> EOF
old   1: select &1 from dual
new   1: select sysdate from dual

SYSDATE
---------
08-APR-14

$

For clearer output:

$ sqlplus -s '/ as sysdba' <<EOF
> set ver off head off pages 0
> @a.sql "$field_name"
> EOF
08-APR-14

$
1 Like
5

Remove the prompt and use &1 instead of &client_id .

1 Like