Re: Lexical Variable use in Cursor

The ampersand is specific to SQL*Plus:

SQL*Plus> select * from dual where dummy = '&my_variable'; Enter value for my_variable: X

D
-
X

You can use it in a PL/SQL script (The code format is for non-standard):

SQL*Plus> declare cursor cur is
  2 select * from dual where dummy = '&my_variable';   3 plsql_var varchar2(1);
  4 begin open cur;
  5 fetch cur into plsql_var;
  6 dbms_output.put_line( plsql_var);
  7 close cur;
  8 end;
  9 .
SQL*Plus> set serveroutput on
SQL*Plus> /
Enter value for my_variable: X
X

PL/SQL procedure successfully completed.

But it may not work as you wish in a stored procedure (again, don't copy the line format):

SQL*Plus> create or replace procedure mikejunk is   2 cursor cur is select * from dual where dummy = '&my_variable';   3 plsql_var varchar2(1);
  4 begin open cur;
  5 fetch cur into plsql_var;
  6 dbms_output.put_line( plsql_var);
  7 close cur;
  8 end;
  9 /
Enter value for my_variable: X

Procedure created.

SQL*Plus> execute mikejunk;
X

PL/SQL procedure successfully completed.

I hope this is what you were looking for!

"abdul wahab" <swahab_at_cyber.net.pk> wrote in message news:394f0e82_at_news.cyber.net.pk...
> dear All,
>
> Any one can help us regarding the use of lexical variable in cursor with
 in
> where clause.
>
> A use of variable with "&" sign.
>
> Regards,
>
>
> Abdul Wahab.
>
>
>
>
Received on Tue Jun 20 2000 - 00:00:00 CEST