RE: DBI -> PL/SQL question

["Wilson, Douglas" <dgwilson@sonomasystems.net>]

-----Original Message-----
From: Phil Lawrence [mailto:prlawrence@Lehigh.EDU]

IIRC, in PL/SQL, bare SQL statements ARE prepared, so
I don't know why you wouldn't do the following dynamic
statement:

> FUNCTION ...
>    ...
>    EXECUTE IMMEDIATE
>     'SELECT foo
>        INTO v_result
>        FROM some_table
>       WHERE bar1 = :1
>         AND bar2 = :2'
>      USING v_val1, v_val2;
>    RETURN v_result;
> END...

like this constant statement:
     SELECT foo
        INTO v_result
        FROM some_table
       WHERE bar1 = v_val1
         AND bar2 = v_val2

In the first example, the statement would be 'prepared' every time,
though if executed repeatedly, it should be found in Oracle's
SQL cache. It is inefficient when you build the same dynamic queries
with different literal values, but the first example is
relatively ok since you are using arguments.

> CURSOR c_cursor ( in_bar1 VARCHAR2, in_bar2 VARCHAR2 )
> IS
>    SELECT foo
>      FROM some_table
>     WHERE bar1 = in_bar1
>       AND bar2 = in_bar2;

> FUNCTION f_get_result ( in_bar1 VARCHAR2, in_bar2 VARCHAR2 )
> AS
>    v_result VARCHAR2(30);
> BEGIN
>    OPEN c_cursor( in_bar1, in_bar2 );
>    FETCH c_cursor INTO v_result;
>    -- DON'T CLOSE CURSOR (?)
>    --  (so Oracle doesn't have to re-prepare next
>    --   time f_get_result is called???)
>    RETURN v_result;
> END f_get_result;

That cursor should only be prepared once also, closing/not closing
the cursor shouldn't make a difference. I would go ahead and
close it.

HTH,
Doug
-
**Majordomo list services provided by PANIX <URL:http://www.panix.com>**
**To Unsubscribe, send "unsubscribe phl" to majordomo@lists.pm.org**