DBI -> PL/SQL question

[Phil Lawrence <prlawrence@Lehigh.EDU>]

This question is about bind variables and performance.

With Perl/DBI, I use bind variables in my SQL statements.  Then I 
prepare them only once, and execute (with varying bind values) 
iteratively.  This is efficient.

Cool.  So now I'm in PL/SQL.  But I don't see that PREPARE is available. 
 All examples are like this:

FUNCTION ...
  ...
  OPEN c_cursor( v_val1, v_val2 );
  FETCH c_cursor INTO v_result;
  CLOSE c_cursor;
  RETURN v_result;
END...

or this:

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...

or this:

FUNCTION ...
  ...
  TYPE cv_type IS REF CURSOR;
  dyncur   cv_type;
  ...
  OPEN dyncur
      FOR f_gimme_a_str_of_sql('bar1','bar2')
    USING v_val1, v_val2;
  FETCH dyncur INTO v_result
  RETURN v_result;
END...

etc., etc., there are lots of variations.  So my question is this:  How 
do I ensure that Oracle will be preparing my SQL statements *only once*? 
 Or do I not need to worry about this at all?  Or do I need to be 
careful not to CLOSE my cursors, and then Oracle will do the right thing?

Also, would a package level cursor be relavent to this question?  (I am 
puttign enerything into a package.)  Perhaps somethign like this?

PACKAGE BODY...

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;

Or does all that fanciness gain me nothing over just using native 
dynamic SQL everytime f_get_result is called?

Thanks,
prl


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