Assigning Bind Variables to Substitution Variables in PL/SQL

From: <suvinay_at_gmail.com>
Date: 4 Nov 2005 15:14:41 -0800
Message-ID: <1131146081.508023.292290@g47g2000cwa.googlegroups.com>

Hello all,
I seem to be hitting an issue trying to assign a bind variable to substitution variable in PL/SQL block - no problem in plain old SQL*Plus. Here it is:

using PL/SQL (does not work)
i also tried without execute immediate - just by selecting into -did not help! Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> column mydbid new_value dbid
SQL> declare
  2 x number;
  3 begin
  4 execute immediate 'select dbid mydbid from v$database' into x;   5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> define dbid
SP2-0135: symbol dbid is UNDEFINED

now using SQL - works like a charm! SQL> select dbid mydbid from v$database;

MYDBID

1396901698

SQL> define dbid
DEFINE DBID = 1396901698 (NUMBER) Any help in how to achieve this in PL/SQL is much appreicated!

thanks
-- suvinay Received on Fri Nov 04 2005 - 17:14:41 CST