Re: Assigning Bind Variables to Substitution Variables in PL/SQL

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Sat, 5 Nov 2005 13:42:05 +0000 (UTC)
Message-ID: <dkicrd$jjf$1@nntp.init7.net>

On 2005-11-04, suvinay_at_gmail.com <suvinay_at_gmail.com> wrote:
> 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

suvinay,

Wouldn't you think it'd be easier for anyone to reply if you stated what "didn't work". It leaves us guessing what you really wanted to achieve.

Anyway, I suspect you're looking for something like

RENE> var x number;
RENE> begin
2 select dbid into :x from v$database; 3 end;
4 /

PL/SQL procedure successfully completed.

RENE> print x

1894527177

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Sat Nov 05 2005 - 07:42:05 CST