How to define persistent variable in SQL*PLUS

Question

I am trying to do the following in SQL* PLUS in ORACLE.

-Create a variable -Pass it as output variable to my method invocation -Print the value from output variable

I get the error "undeclared variable" error. I am trying to create a variable that persists in the session till i close the SQL*PLUS window.

variable subhandle number;
exec MYMETHOD - 
(CHANGE_SET => 'SYNC_SET', - 
DESCRIPTION => 'Change data for emp', - 
SUBSCRIPTION_HANDLE => :subhandle);

print subhandle;

Answer 1

Please can you re-post, but formatting the code with the code tag.... (ie the 101 010 button) I think some extra "-" characters came through which means it more difficult to interpret.

Might also be helpful to see SQL*Plus reporting the error if you could copy the contents of the SQL*Plus window instead/too?

But it looks correct.

Answer 2

It should be OK - check what you did carefully against this:

SQL> create procedure myproc (p1 out number)
  2  is
  3  begin
  4     p1 := 42;
  5  end;
  6  /

Procedure created.

SQL> variable subhandle number
SQL> exec myproc(:subhandle)

PL/SQL procedure successfully completed.

SQL> print subhandle

 SUBHANDLE
----------
        42

Answer 3

I'm not sure if this is what you're looking for, but did you try the &&variable syntax? You could do

select &&subhandle from dual

or some such at the start of the script, then subhandle should be bound to that value for the remainder of the session.