RE: DBI -> PL/SQL question

["Rajesh Khot" <rajeshkhot@hotmail.com>]

All PL/SQL variables are in fact bind variables.

So when you have

SELECT foo
INTO    v_intovar
FROM    bar
WHERE somecol = v_somevar;

The v_somevar is treated as a bind variable. The SELECt statement is parsed 
only once.

On the other hand, if the statement is written like this

EXECUTE IMMEDIATE 'SELECT foo FROM bar WHERE somecol = ' || 
TO_CHAR(v_somevar) INTO v_intovar;

This does not make use of bind variables, and is reparsed everytime it is 
executed.



This one does use bind variables

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



Rajesh


"All you need in this life is ignorance and confidence, and then success is 
sure."
-- Mark Twain (1835 - 1910)





----Original Message Follows----
From: Phil Lawrence <prlawrence@Lehigh.EDU>
Reply-To: phl@lists.pm.org
To: phl@lists.pm.org
Subject: DBI -> PL/SQL question
Date: Thu, 04 Mar 2004 11:49:29 -0500

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

_________________________________________________________________
Get a FREE online computer virus scan from McAfee when you click here. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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