Re: Maddening - Creating a stored procedure for use in Crystal Reports
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 body some_package_body
as
select orders.order_number, lineitems.product, lineitems.qtyfrom
orders, lineitemswhereorders.order_id =+ lineitems.order_idand
orders.order_date >= start_date;
When you execute this (in sql plus, for ex) you could:
create or replace package some_package
as
type rcursor is ref cursor;
procedure some_proc (start_date in date, r_rcursor our rcursor);
end;
procedure some_proc (start_date in date, r_rcursor our rcursor) is
r_cursor is
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.
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