Re: Procedure help for a newbie

"Some person" <thesynner_at_hotmail.com> wrote in message news:<6Lti8.10693$uA5.14090_at_rwcrnsc51.ops.asp.att.net>...
> I'm new to Oracle, and am trying to create a stored procedure. The
> statement compiles, but when I try to execute it, I get the message:
>
> The following error has occurred:
>
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at "JMOUNT.FIRES_BY_CAUSE", line 14
> ORA-06512: at line 21
>
>
> ALSO, how do I then turn this into an accessible package? The code for both
> the procedure and package are below. Any help would be oh-so-greatly
> appreciated.
>
> This will eventually be called by Crystal Reports.
>
> ***
> CREATE OR REPLACE PROCEDURE FIRES_BY_CAUSE(
>
> BATTALION_NUM IN VARCHAR2,
> FIRE_CAUSE OUT VARCHAR2,
> INC_NUM OUT VARCHAR2,
> CDF_ID OUT VARCHAR2,
> BATTALION OUT VARCHAR2,
> FIRST_RPT_DATE OUT VARCHAR2,
> PROT_RESP_1 OUT VARCHAR2,
> PROT_RESP_2 OUT VARCHAR2) AS
>
> BEGIN
>
> SELECT CAUSE.CAUSE, INCIDENT.INC_NUM, INCIDENT.CDF_ID,
> INCIDENT.BATTALION, INCIDENT.FIRST_RPT_DATE,
> INCIDENT.PROT_RESP_1, INCIDENT.PROT_RESP_2
> INTO FIRE_CAUSE, INC_NUM, CDF_ID, BATTALION, FIRST_RPT_DATE, PROT_RESP_1,
> PROT_RESP_2
> FROM CAUSE, INCIDENT
> WHERE((CAUSE.CAUSE(+) = INCIDENT.CAUSE) ) ;
>
> END FIRES_BY_CAUSE;
> /
> ***
>
> ***
> CREATE OR REPLACE PACKAGE NUM_FIRES IS
> PROCEDURE FIRES_BY_CAUSE (BATTALION_NUM IN VARCHAR2,
> FIRE_CAUSE VARCHAR2,
> INC_NUM VARCHAR2,
> CDF_ID VARCHAR2,
> BATTALION VARCHAR2,
> FIRST_RPT_DATE VARCHAR2,
> PROT_RESP_1 VARCHAR2,
> PROT_RESP_2 VARCHAR2);
> END NUM_FIRES;
> /
> ***

You're getting the error because your out variable is returning a varchar ... it's expecting a single value.

  The kind of variable you want to use is a ref cursor. The sql for the package definition will look like :

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

This defines the type RCURSOR for a variable of type ref cursor.

The package body (using the select statement from you previous post) 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;

This is the variable rcursor, a defines it using the select statment. When this variable is passed out of the proc, it will open the select query and return the result set one row at a time.

  Hope this helps. Received on Sat Mar 09 2002 - 23:15:07 CET