Re: Sql*Plus in Shell Script Question

You have to use anonymous block to execute the procedure. That is the right way to do it.

Your anonymous block is the script that you run succesfully from the SQLPLUS prompt.The script is calling a stored procedure from within. YOu already have one.

Pl. do as follows:

____begin cut____

declare
v_totalrecords number
BEGIN
 v_total_records := null;
execute some_proc('Joe', v_totalrecords);  END; ____end cut______

save this into a sql file and call the file from the shell script.

Venki Ramachandran wrote:

> Hi,
>
> I have a shell script as follows:
>
> #!/bin/sh
>
> MYNAME='Joe'; export MYNAME
>
> sqlplus user/password -S <<File
> Define v_name=$MYNAME
>
> execute some_proc('&v_name', v_totalrecords);
>
> File
>
> The problem is that the variable MYNAME gets passed successfully, but
> the procedure does not execute because of the second variable
> 'v_totalrecords. It is an 'IN/OUT' variable and it needs to be DECLARED.
> It works in sqlplus prompt as in:
>
> sqlplus>declare
> > v_totalrecords number
> >BEGIN
> > v_total_records := null;
> >execute some_proc('Joe', v_totalrecords);
> > END;
> >/
> Procedure completed successfully.
>
> The problem is how do you declare an 'IN/OUT' variable in sqlplus inside
> a shell script. I have tried different variations, but have been
> unsuccessful. The moment I put the above declare, begin and end
> statements around the exeute statement, it does not run or the messages
> do not show up. Any help will be appreciated. Thanks.
Received on Wed Aug 08 2001 - 07:09:11 CEST