From: Ram Lakshminarayanan <raml@india.hp.com>
Newsgroups: comp.databases.oracle.tools
Subject: Re: Sql*Plus in Shell Script Question
Date: Wed, 08 Aug 2001 10:39:11 +0530
Organization: Hewlett-Packard India Software Operations - E-Intelligence Section
Lines: 60
Message-ID: <3B70C977.17089313@india.hp.com>
References: <3B703079.B4E6646E@pacbell.net>
NNTP-Posting-Host: nt45158.india.hp.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: pavan.india.hp.com 997247700 11032 15.10.45.158 (8 Aug 2001 05:15:00 GMT)
X-Complaints-To: dhruva@india.hp.com,news@india.hp.com
NNTP-Posting-Date: 8 Aug 2001 05:15:00 GMT
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: en


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.