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

Thanks for your advice.

The bottom line is I've been trying to create a stored procedure which returns multiple rows based on a parameter which is passed through, and not ONE of the suggestions I've been given work, most don't even compile.

What I had hoped to learn was how to create the equivalent of a 3 line script in SQL Server.

This is what it might look like in SQL Server:

create procedure my_proc
_at_start_date datetime
as
select orders.order_number, lineitems.product, lineitems.qty> from orders, lineitems where orders.order_id = lineitems.order_id and orders.order_date >= _at_ start_Date

If I execute the oracle equivalent in SQL Plus or Worksheet, passing through the parameter, it should return my data.

Please forgive that I'm repeating this request, I'm just a bit frustrated.

"TSomers" <remoteoracledba_at_hotmail.com> wrote in message news:87a4d936.0203101130.7484aa3b_at_posting.google.com...
> 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 =
> lineitems.order_id(+) and orders.order_date >= :start_date' USING
> start_date;
> return l_cursor;
> end;
> /
>
>
> 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 - 22:57:36 CET