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

Go to http://asktom.oracle.com/pls/ask/f?p=4950:1: and search for ref cursor. He has tons of actual working examples and is very helpful. Jim
"Some Person" <thesynner_at_hotmail.com> wrote in message news:kFQi8.17446$ZR2.8951_at_rwcrnsc52.ops.asp.att.net...
> 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 - 23:39:02 CET