Re: Bind Variable -- Implicit Cursor
- From: Edgar Chupit <chupit@xxxxxxxxx>
- To: fahd-m@xxxxxxxxxxx
- Date: Tue, 21 Dec 2004 07:58:05 +0200
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@xxxxxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Edgar
--
//www.freelists.org/webpage/oracle-l
Other related posts:
