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

Again, thanks for your response.

I modified the statement to accomodate our db, and while the package creates, the package body gets
compilation errors. I'm including the revised statement in hopes that you'll be able to spot the problem...

**Works Fine:
CREATE OR REPLACE PACKAGE pkgFIRES_BY_CAUSE AS   TYPE rcursor IS REF CURSOR;
  PROCEDURE procFIRES_BY_CAUSE (prmBATTALION IN VARCHAR2, r_rcursor OUT rcursor);
 END;  **Compilation Errors:
 CREATE OR REPLACE PACKAGE BODY bodFIRES_BY_CAUSE   AS
   PROCEDURE procFIRES_BY_CAUSE (prmBATTALION IN VARCHAR2, r_rcursor OUT rcursor) IS

   r_cursor IS

      SELECT CAUSE.CAUSE, INCIDENT.INC_NUM, INCIDENT.CDF_ID,
          INCIDENT.BATTALION, INCIDENT.FIRST_RPT_DATE,
              INCIDENT.PROT_RESP_1, INCIDENT.PROT_RESP_2

   BEGIN
     OPEN r_cursor;
   END;
 END;
/

I hope that I explained well enough that I need to be able to execute this and have multiple rows returned.

• Original Message ----- From: "Brent Pathakis" <bpathakis_at_yahoo.com> To: <thesynner_at_hotmail.com> Sent: Saturday, March 09, 2002 1:14 PM Subject: Maddening -Re:Creating a stored procedure for use in Crystal Reports

> Hi,
>
> I'm not sure how you would do in crystal reports,
> but for the pl/sql version will look somethng 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;
>
> ----
> For the package definition This
> 1. defines a type - rcursor.
> 2. Defines a proc to take in a date and return the
> results of the cursors
>
> For the body:
>
> 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;
>
> ----
> For the proc, i've defined the cursor to return your
> select statement and in body opened it.
>
> To execute it in sql plus:
>
> set autoprint on
> variable r refcursor
>
> exec some_package.some_proc(to_date('01-JAN-2001',
> 'DD-MON-YYYY'), :r)
>
> The example I've is is for Oracle 8.
>
> I'm not sure the syntax is %100 correct, but it
> should get you close.
>
> __________________________________________________
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free email!
> http://mail.yahoo.com/
>

"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 - 23:47:08 CET