Home -> Usenet -> comp.databases.oracle -> Procedure help for a newbie

Procedure help for a newbie

From: Some person <thesynner_at_hotmail.com>
Date: Sat, 09 Mar 2002 19:53:38 GMT
Message-ID: <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;



/


***

Received on Sat Mar 09 2002 - 20:53:38 CET












Original text of this message