DBMS_SQL, native dsql, static sql performance

From: Matt Houseman <mhousema_at_ix.netcom.com>
Date: 2000/03/16
Message-ID: <8asf4c$i0n$1@slb7.atl.mindspring.net>#1/1

All,

If I expect to get several hundred rows back in a stored procedure, which method am I better off using:

1) DBMS_SQL so that I can perform array fetches using DBMS_SQL.DEFINE_ARRAY
2) drop into a loop using native dsql and build my result set row by row
3) drop into a loop using static sql and build my result set row by row

I always thought that if I had parameters that ended up being part of my where clause in static sql,
that this would trigger a recompile of the SQL if the parameter values happened to be different for
each dispatch of the stored procedure. For example:

                select ename from emp where deptno = IN_deptno;

The first dispatch might pass 30 as the IN_deptno and the second dispatch might pass 40 as the
IN_deptno.

I always thought I had to use some form of DSQL (native or DBMS_SQL) and a bind variable so that the
query looked the 'same' to the query processor.

Also, if I use static SQL, should I be explicitly opening and closing my cursors?

TIA,
Matt Houseman Received on Thu Mar 16 2000 - 00:00:00 CST