Re: Bind Variable -- Implicit Cursor

When you use SQL inside PL/SQL routines, PL/SQL engine preprocesses all SQL's and automatically replaces all PL/SQL variables to bind variables and that's one of the advantages of PL/SQL it helps you to write correct (reusable) SQL statements.

So, in your case you can simply write: select object_name from all_object where object_id=i and PL/SQL engine will do all work for you, but note that if you will write select object_name from all_object where object_id=1 PL/SQL engine will leave all as is without turning =1 into bind variable. You can always check what SQL's was executed using SQL_TRACE facility.

On Mon, 20 Dec 2004 13:58:34 +0500, Fahd Mirza <fahd-m_at_aero.com.pk> wrote:
> Hi listers,
> I am afraid, I am going to ask a very simple question. I am trying to learn the Bind Variables. I know what are they and how they can be used in explicit cursor. But I am at loss at how they would he used in implicit cursor. E.g. the following PL/SQL block uses the bind variable explicit cursor and it works fine.
> declare
> type rc is ref cursor;
> l_rc rc;
> l_dummy all_objects.object_name%type;
> begin
> for i in 1...1000
> loop
> open l_rc for
> 'select object_name from all_object where object_id=:x' using i;
> fetch l_rc into l_dummy;
> close l_rc;
> end loop;
> end;
> /
>
> I want to use the bind variable ':x' in implicit cursor like this
>
> declare
> begin
> for i in (select object_name from all_object where object_id=:x' using i)
> loop
> end loop;
> end;
> /
> but the above code with For Loop cursor is giving errors. I would be extremely grateful for guidance.
>
> regards,
> Fahd
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
  Edgar
--
http://www.freelists.org/webpage/oracle-l