Script fails when passing values from pl/sql to unix variable

From: Nischal <nischal.mahakal_at_farmers.co.nz>
Date: 23 Mar 2004 13:00:30 -0800
Message-ID: <7b6255b3.0403231300.49602d19_at_posting.google.com>

Dear All,

I am Automating STATSPACK reporting by modifying the sprepins.sql script.
Using DBMS_JOB I take the snap of the database and at the end of the day the cron job creates the statspack report and emails it to me. I am storing the snapshot ids in the database and when running the report picking up the recent ids(begin snap and end snap).

From the sprepins.sql script

variable bid number;
variable eid number;

begin

select begin_snap into :bid from db_snap; select end_snap into :eid from db_snap;

end;
/

This fails with the following error:

DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- -------

---

RDMDEVL 3576140228 RDMDEVL 1 9.2.0.4.0 NO ibm-rdm :ela := ;
*
ERROR at line 4:

ORA-06550: line 4, column 17: 
PLS-00103: Encountered the symbol ";" when expecting one of the
following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ";" to continue.

ORA-06550: line 6, column 16: 
PLS-00103: Encountered the symbol ";" when expecting one of the
following:

( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev su

But when I change the select statements below the report runs successfully.

variable bid number;
variable eid number;

begin

select '46' into :bid from db_snap;
select '47' into :eid from db_snap;

end;
/

Even changing the select statements to:

select TO_CHAR(begin_snap) into :bid from db_snap; select TO_CHAR(end_snap) into :eid from db_snap;

Does not help.

Please Help.

TIA,
Nischal Received on Tue Mar 23 2004 - 22:00:30 CET