Home » SQL & PL/SQL » SQL & PL/SQL » Passing parameters
Passing parameters [message #184754] Thu, 27 July 2006 15:12 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I am spool data into files. I have to sys id will be passing as the substitution parameter from UNIX shell script into SQL and will be part of name of files.
How can I do this?

My SQL:

spool C:\Load\daily\&1_service.dat

select service_id, service_date, service_descr
from service
where service_id >= &2
and service_id >= &3
/

In shell script:

export sys_id=e1
export SERVICE_ID_FROM=100604
export SERVICE_ID_TO=-100996

sqlplus ${user} @${progname}.sql ${sys_id} ${SERVICE_ID_FROM} ${SERVICE_ID_TO}

The name of file should be e1_service.dat

Thanks.
Re: Passing parameters [message #184755 is a reply to message #184754] Thu, 27 July 2006 15:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
A quick lookover looks ok to me. what happens when you run it? what is the output filename called? Is SQL*Plus called at all?
Re: Passing parameters [message #184758 is a reply to message #184755] Thu, 27 July 2006 15:27 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
No, the SQL plus can read only &2 and &3. How I can pass &1 parameter. In the one SQL script will be multiple queries and output files name for each should started with e1( sys id = 1), e2 (sys id = 2).
Re: Passing parameters [message #184792 is a reply to message #184758] Fri, 28 July 2006 01:26 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi,
just modify your sript as given below.

in the shell script......
 export sys_id=$1 # if you want just hadcode the value
export SERVICE_ID_FROM=100604
export SERVICE_ID_TO=-100996

echo "
spool C:\Load\daily\${sys_id}_service.dat
select service_id, service_date, service_descr
from service
where service_id >= ${SERVICE_ID_FROM}
and service_id >= ${SERVICE_ID_TO};
exit
" > temp.sql

sqlplus user/passwor @temp.sql
rm temp.sql


i believe i will fullfil your needs

Thanks,
Thangam




Re: Passing parameters [message #184893 is a reply to message #184792] Fri, 28 July 2006 09:13 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
This will work. Also I can rename files after spool data, but I cannot put sql query to shell, because queries are huge and I have 4-5 sql's in one sql script that will generate separate files for each query.

Just ekzample:

spool C:\Load\daily\${sys_id}_service.dat
select service_id, service_date, service_descr
from service
where service_id >= &1
and service_id >= &2;

spool C:\Load\daily\${sys_id}_patient.dat
select patient_id, service_id, patient_name, patient_dob
from patient
where service_id >= $1
and service_id >= $2};
.....

I just need to pass sys id and path into name for each query because I have two systems running in same batch server, but data will retrieve from different databases (dbs1 and dbs2). I don't want to create two same set of sql scripts.

Thanks.

Re: Passing parameters [message #184897 is a reply to message #184893] Fri, 28 July 2006 09:25 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
it is not big deal. you can add as many sql as you want just like given below

export sys_id=$1 # if you want just hadcode the value
export SERVICE_ID_FROM=100604
export SERVICE_ID_TO=-100996

echo "
spool C:\Load\daily\${sys_id}_service.dat
select service_id, service_date, service_descr
from service
where service_id >= ${SERVICE_ID_FROM}
and service_id >= ${SERVICE_ID_TO};
spool off
exit
" > temp.sql

echo "
spool C:\Load\daily\${sys_id}_patient.dat
select patient_id, service_id, patient_name, patient_dob
from patient
where service_id >= ${sys_id}
and service_id >= ${SERVICE_ID_FROM};
spool off
exit
" > temp1.sql


sqlplus user/passwor @temp.sql
rm temp.sql

sqlplus user/passwor @temp1.sql
rm temp1.sql

.....

like that you can any number of sqls statement.
otherwise can you attach your actuall sqls, i will modify accordingly....
Re: Passing parameters [message #184910 is a reply to message #184897] Fri, 28 July 2006 10:45 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
Based on multiple and huge queries I have, I don't wont to put sql inside shell. I would like to separate sql from shell and just execute it from shell.

Can I dynamically pass those parameters (into C:\Load\daily\${sys_id}_service.dat) from shell to sql or concatenate in

echo "
spool C:\Load\daily\${sys_id}_service.dat
select service_id, service_date, service_descr
from service
where service_id >= ${SERVICE_ID_FROM}
and service_id >= ${SERVICE_ID_TO};
exit
" > temp.sql

sqlplus user/passwor @temp.sql

Thanks.
Re: Passing parameters [message #185120 is a reply to message #184910] Mon, 31 July 2006 01:41 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Yes, you can separate both sql and shellscript separately.

for instance,
my shell script is
tl:temp_user:/tlr01_users/temp_user> cat > temp_ksh.ksh
sqlplus user/pass @temp_sql.sql 33

my sql is
tl:temp_user:/tlr01_users/temp_user> cat > temp_sql.sql
set verify off
spool gold_&1..txt
select * from tab1 where a=&1;
spool off
exit

my spool file is
tl:temp_user:/tlr01_users/temp_user> cat gold_33.txt

A B
---------- --------------------
33 agbd


please keep in mind that if you want to pass the parameter from shell script to sql by positional notation, the sql script should only have numbers like &1,&2 and etc. however if you specify like &num or anything, it will prompt you to enter value for num though you had passed value for it.

Thanks,
Thangam
Previous Topic: can i use union in pl/sql??
Next Topic: Need help in comparison of date...
Goto Forum:
  


Current Time: Fri Dec 02 20:46:09 CST 2016

Total time taken to generate the page: 0.33578 seconds