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

From: Some person <thesynner_at_hotmail.com>
Date: Sat, 09 Mar 2002 21:33:13 GMT
Message-ID: <tcvi8.11081$uA5.15500_at_rwcrnsc51.ops.asp.att.net>


Thanks for this response.

This was precisely what I sought, simple and to the point.

I will be using this as a data source in Crystal Reports, so I don't need to define anything from within Crystal, I just point at the procedure, and Crystal will automatically set up the stored procedure and handle the results.

I'm just VERY rusty with Oracle, and as you must know, Oracle isn't intuitive nor brief in how you accomplish things, the old line about Oracle was that they're real legacy won't be that they created databases, rather that they create high paying jobs ;)

I reckon it's time to grab another PL/SQL book...

Thanks again!

"Brent" <bpathakis_at_yahoo.com> wrote in message news: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:33:13 CET

Original text of this message