Re: What's Wrong with this Procedure??

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 09 Mar 2002 22:05:47 +0100
Message-ID: <kltk8ugmpi7ttb1pbfti2l8pl624n62tp4_at_4ax.com>

[Quoted] [Quoted] 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
[Quoted] >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,
[Quoted] > 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 Sat Mar 09 2002 - 22:05:47 CET