need help??

[Trina]

Hi everyone,
When I run the shell script to execute the SQL script so it can pick up the value
from the loopcount. The SQL script does not recognize the $. How can I make it
to work. Below is the sample script.

shell script:
loopcount=5

SQL script:
DEFINE apple = $loopcount

[alenz]

you can pass the var into sqlplus with the &n (n being the number of the argument)

ex
ksh
i=1
while [ $i -lt 10 ]
do
echo $i
sqlplus -s @ $i
((i=$i+1))
done

in you will have a &1. &1 will = $i

[alenz]

html did not show my less then and greater then chars


while [ $i -lt 10 ]
do
echo $i
sqlplus -s @myscript.sql $i
((i=$i+1))
done

in myscript.sql you will have a &1. &1 will = $i

[Trina]

Hi Alenz,
I want to thank you for your help. I tried your method and I'm not sure if I did it
correctly. It didn't work. My sql scripts are below. It didn't recognize the $.

ksh script:
i=1
sqlplus -s @myscript.sql $i

sql script:
SET SERVEROUTPUT on
DEFINE loopcount = $i

DECLARE
v_test NUMBER := &loopcount;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_test);
END;
/

[Knarayan]

I think this should work :
this is the shell script
i=1;export i
sqlplus -s system/complex < set serverout on;
define loopcount=$i
declare
v number :=&loopcount;
begin
dbms_output.put_line(v);
end;

[Knarayan]

oh it missed out some characters let me try again basically there is an exclamation mark after two less than signs at the end of the sqlplus line and an exclamation mark at the end of the file:
i=1;export i
sqlplus -s system/complex < set serverout on;
define loopcount=$i
declare
v number :=&loopcount;
begin
dbms_output.put_line(v);
end;
/
!

[Trina]

Hi Knarayan,
thanks for your assistance but it didn't work. My OS is HP and I used
kornshell script. It didn't work. It kept saying DEFINE and DECLARE not found.
Below is my kornshell script.

userid=apple
export i=1

$ORACLE_SQLPLUS -s $apple/$apple < set serverout on;

DEFINE loopcount = $i

declare
v_test number := &loopcount;
begin
dbms_output.put_line(v_test);
end;
/

[Knarayan]

I think that is bcoz your shell script is incorrect.
What is oracle_sqlplus??

[Trina]

oracle_sqlplus is where i defined to get the sqlplus tool to run.

shell script:
export oracle_sqlplus=/opt/app/oracle/....

[alenz]

Originally posted by Trina
Hi Alenz,
I want to thank you for your help. I tried your method and I'm not sure if I did it
correctly. It didn't work. My sql scripts are below. It didn't recognize the $.

ksh script:
i=1
sqlplus -s @myscript.sql $i

sql script:
SET SERVEROUTPUT on
DEFINE loopcount = $i

DECLARE
v_test NUMBER := &loopcount;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_test);
END;
/

change
DEFINE loopcount = $i
to
DEFINE loopcount = &1

does this work?