Assigning Bind Variables to Substitution Variables in PL/SQL

[suv...@gmail.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

[Rene Nyffenegger]

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

X
----------
1894527177

hth
Rene

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

[suvinay]

Sorry about that. Thanks for input Rene.
I guessed I missed on specifying what did not work!
Here it goes again - what did not work is I was not able to assign
a substitution variable (as in one set by define) inside a pl/sql
code based on a substituion variable (as in set by a var/declare).

--- so here i am setting a define for dbid

SQL> column mydbid new_value dbid

--- and here i try to set the define above from inside a pl/sql code
--- based on a declare variable x - in other words, the objective
--- was to set dbid (defined above) based on value select in
--- in x variable inside a pl/sql code

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.

-- when i try to check if dbid got initialized, it did not

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

Thanks again.
Regards,
Suvinay

[Malcolm Dew-Jones]

suvinay (suv...@gmail.com) wrote:
: Sorry about that. Thanks for input Rene.

: I guessed I missed on specifying what did not work!
: Here it goes again - what did not work is I was not able to assign
: a substitution variable (as in one set by define) inside a pl/sql
: code based on a substituion variable (as in set by a var/declare).

: --- so here i am setting a define for dbid
: SQL> column mydbid new_value dbid

This tells SQL*PLus what to do when it sees that column in the query
results.

: --- and here i try to set the define above from inside a pl/sql code

: --- based on a declare variable x - in other words, the objective
: --- was to set dbid (defined above) based on value select in
: --- in x variable inside a pl/sql code
: SQL> declare
: 2 x number;
: 3 begin
: 4 execute immediate 'select dbid mydbid from v$database' into x;

But this SQl query is not run by SQL*Plus.

Instead is run by the PL/SQL engine on the server as part of running the
anonymous block. The results are only "seen" within the server, "out of
sight" of the client, so to speak.

: 5 end;
: 6 /

: PL/SQL procedure successfully completed.

: -- when i try to check if dbid got initialized, it did not
: SQL> define dbid
: SP2-0135: symbol dbid is UNDEFINED

: Thanks again.
: Regards,
: Suvinay

Another example showed how to get the results into a bind variable. I
think you can then SELECT the bind variable FROM DUAL to get it into a
SQL*PLus define variable. So, two steps instead of one.

--

This programmer available for rent.

[HansF]

SQL> variable this_dbis number
SQL> begin
2 select dbid
3 into :this_dbis
4 from v$database;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print this_dbis

THIS_DBIS
----------
2454348050

SQL> define mydbid=:this_dbis
SQL> print &mydbid

THIS_DBIS
----------
2454348050

SQL>

But that's not quite what you intend ... time to hit the manuals again?
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting relies guarantees I won't respond. ***

[suvinay]

That makes sense - I mean the note about being run on the
server vs. running in sql*plus.

thanks :-)

[suvinay]

Hans, the example you mention above worked as expected.
My issue was trying to mix up local variables in sql*plus with variable
declared and execute on server via pl/sql engine - and as Malcolm
pretty much pointed out, does not make sense. Got to keep
my scope visible :-)

thanks.