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

suvinay (suvinay_at_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.