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

From: Brent <bpathakis_at_yahoo.com>
Date: 9 Mar 2002 13:03:12 -0800
Message-ID: <1736c3ae.0203091303.14c25816_at_posting.google.com>


Hi there,

    For Oracle 8 and later, you'd probably want to use a ref cursor.    What you'd do is define the procedure in the package, and code the the procedure in the package body. In PL/SQl, it will look like this:



  create or replace package some_package as
 type rcursor is ref cursor;
 procedure some_proc (start_date in date, r_rcursor our rcursor); end;

create or replace package body some_package_body  as
  procedure some_proc (start_date in date, r_rcursor our rcursor) is   r_cursor is

     select orders.order_number, lineitems.product, lineitems.qtyfrom     orders, lineitemswhereorders.order_id =+ lineitems.order_idand      orders.order_date >= start_date;
  begin
    open r_cursor;
  end;
end;



  Okay, a little about the about. I defined a type (rcursor) to be a ref cursor. I defined the stored proc to take IN a param (start_date) to take in a date, and a ref cursor OUT param to return the results. Then, in the package body, i used your select statement to define what the cursor should fetch.

   When you execute this (in sql plus, for ex) you could:

       
       set autoprint on  
       variable r refcursor;               
 
       exec some_package.some_proc('01-JUL-2001', 'dd-mon-yyyy', :r);

     In sql plus, you'd need to define a variable to pass the results
to.

     I'm not sure if my syntax is %100 correct, but that should get you close.

"Some person" <thesynner_at_hotmail.com> wrote in message news:<3Lfi8.7078$ZR2.3544_at_rwcrnsc52.ops.asp.att.net>...
> I've done this before, but I'm very rusty on Oracle.
>
> Can someone supply a link to a resource, or better yet, the SQL to create a
> procedure in Oracle?
>
> Let's say I have 2 tables that I'll want to join, with one parameter and
> return data from:
>
> The parameter is called _at_start_date here
>
> 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
>
> How would I create a stored procedure and whatever packages are required to
> return this data???
>
> I've been trying for hours and I can't seem to get to the point where I have
> a package which correctly references a procedure that can be seen from
> Crystal reports.
>
> The overall procedure/package/package body dynamic is unclear to me, the
> Oracle documentation says brilliant things like "you don't always need a
> Package Body". Well that's fine with me, what do I require, and what is the
> dynamic?
>
> Thanks for any help you might have.
>
> I've done this before, but I'm very rusty on Oracle.
>
> Can someone supply a link to a resource, or better yet, the SQL to create a
> procedure in Oracle?
>
> Let's say I have 2 tables that I'll want to join, with one parameter and
> return data from:
>
> The parameter is called _at_start_date here
>
> 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
>
> How would I create a stored procedure and whatever packages are required to
> return this data???
>
> I've been trying for hours and I can't seem to get to the point where I have
> a package which correctly references a procedure that can be seen from
> Crystal reports.
>
> The overall procedure/package/package body dynamic is unclear to me, the
> Oracle documentation says brilliant things like "you don't always need a
> Package Body". Well that's fine with me, what do I require, and what is the
> dynamic?
>
> Thanks for any help you might have.
Received on Sat Mar 09 2002 - 22:03:12 CET

Original text of this message