Re: Maddening - Creating a stored procedure for use in Crystal Reports

Ken,
I see there has been a lot of interesting response to your call for Stored Procedure assistance, almost all incomplete. If you are writing stored procedure code without bind variables you become a terrible SQL Citizen, period. Not using bind variables means you pollute the shared pool, over use latches, cause too much hard parsing at the Oracle server level and ultimately cause serialization of your application all of which are absolutely terrible. This terrible practice has been on the top ten list of poor Oracle Development practices (#2 on the list) for as long as I can remember. The bottom line is when you write ANY stored procedure that takes variable input use bind variables. Anyone who tells you different does not know what they are talking about.

Your code SHOULD look like:
procedure some_proc (start_date in date) is
begin
execute immediate 'select orders.order_number, lineitems.product, lineitems.qty
from orders, lineitems where orders.order_id = lineitems.order_id(+) and orders.order_date >= :start_date' USING start_date;

end;
/

or if you want a ref_cursor type return

create or replace package p_ref_cursor is

   type ret_ref_cursor is ref cursor;
end p_ref_cursor;
/

create or replace function some_func (start_date in date) return p_ref_cursor.ret_ref_cursor is
l_cursor p_ref_cursor.ret_ref_cursor;
 begin
  open l_cursor for

     'select orders.order_number, lineitems.product, lineitems.qty 
     from orders, lineitems where orders.order_id =

This kind of construct will ensure irrespective of how many times this SQL gets called (even with different start_date values) you SQL is parsed once (hard) and executed many times. This is fundamental building block to building scalable applications on Oracle using stored procedures. Also, all production PL/SQL is put in a package not separate functions or procedures that can potentially leave you with a dependancy chain and a real maintenance headache. Received on Sun Mar 10 2002 - 20:30:33 CET