Re: What's Wrong with this Procedure??

From: Some Person <thesynner_at_hotmail.com>
Date: Sun, 10 Mar 2002 00:06:55 GMT
Message-ID: <zsxi8.10999$af7.11107_at_rwcrnsc53>

[Quoted] Ah, thanks. The select...into statement didn't seem right in this scenario, but I'm still to green in Oracle to know the proper syntax.

I do want to return all the rows, and below is the syntax I currently have. It's close, but won't quite compile. Any assistance is appreciated...

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;   CREATE OR REPLACE PACKAGE BODY bodFIRES_BY_CAUSE IS     PROCEDURE procFIRES_BY_CAUSE (prmBATTALION IN VARCHAR2, r_rcursor OUT rcursor)
 AS
   BEGIN
     OPEN r_rcursor FOR
 SELECT CAUSE.CAUSE, INCIDENT.INC_NUM, INCIDENT.CDF_ID,

           INCIDENT.BATTALION, INCIDENT.FIRST_RPT_DATE,
               INCIDENT.PROT_RESP_1, INCIDENT.PROT_RESP_2

 FROM CAUSE, INCIDENT
 WHERE((CAUSE.CAUSE(+) = INCIDENT.CAUSE)     AND INCIDENT.BATTALION=prmBATTALION) ;    END;
 END; Thanks!

jem

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:kltk8ugmpi7ttb1pbfti2l8pl624n62tp4_at_4ax.com...
> On Sat, 09 Mar 2002 19:55:30 GMT, "Some Person"
> <thesynner_at_hotmail.com> wrote:
>
> >Hi, there. I'm new to Oracle, and am trying to create a stored
procedure.
> >The statement passes by the SQL editor, but when I try to actually
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.
> >
> >***
> >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;
> >/
> >***
> >
>
> As to your code:
> You have programmed a select into.
> A select into can return *exactly 1* row
> 0 rows will result in ora-1403, no_data_found, more than one row in
> ora-1422 as you already discovered.
> I strongly recommend you always trap those exceptions.
> Isolate your sql into ordinary sql in sql*plus and you will see your
> sql does return more than one row.
> So: either your sql is incorrect (as the data isn't what you expected)
> or you need to turn this into a cursor for loop
>
>
>
> I will show you how to convert this to a package and at the same time
> include exceptions
>
>
>
>
> 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;
> /
>
> create or replace package body num_fires is
> 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) ) ;
> exception
> when no_data_found then -- no_data_found is fairly common
> then
> fire_cause := '';
> inc_num := 0;
> cdf_id := 0;
> -- etc , etc
>
> when others then
> dbms_output.put_line(dbms_utility.format_error_stack);
> END FIRES_BY_CAUSE;
> end num_fires;
> /
>
> Hth
>
>
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Sun Mar 10 2002 - 01:06:55 CET