Home » SQL & PL/SQL » SQL & PL/SQL » Outputing parameters
- Outputing parameters [message #5940] Thu, 13 March 2003 08:48 Go to next message
Shak
Messages: 13
Registered: May 2002
Junior Member
Hi,
I am trying to output from a stored procedure and am always getting a error. I am trying to check the existence of a transaction. If transaction exists then return 1 else 0. The error I get is

(1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted
(2): PL/SQL: Item ignored
(3): PLS-00323: subprogram or cursor 'SP_tranout' is declared in a package specification and must be defined in the package body

Help needed. Thanks

PACKAGE PK_TEMP
AS
PROCEDURE SP_tranout(v_id_tran IN tblTransaction.id_tran%TYPE,
var_check OUT NUMBER);


END PK_TEMP;

PACKAGE BODY PK_TEMP
AS

PROCEDURE SP_tranout(v_id_tran IN tblTransaction.id_tran%TYPE,
var_check OUT NUMBER)
IS
var_check NUMBER;
var_tran NUMBER;

BEGIN
SELECT COUNT(*) INTO var_tran
FROM tblTransaction
WHERE id_tran = v_id_tran;

IF (var_tran = 0)
THEN
var_check := 0; --NOTHING EXISTS
ELSE
var_check := 1; --TRANS EXISTS
END IF;



END SP_CashTranVal;

END PK_TEMP;
- Re: Outputing parameters [message #5941 is a reply to message #5940] Thu, 13 March 2003 09:16 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Shak,

You're getting your error because you have var_check defined in both your parameter list and in the DECLARE section of your procedure:
          PROCEDURE SP_tranout (
              v_id_tran   IN  tblTransaction.id_tran%TYPE
          ,   <b>var_check</b>   OUT NUMBER)
          IS
              <b>var_check</b> NUMBER;
              var_tran NUMBER;
You would want to get rid of the second one.

But please note, Shak, that the following version of your procedure will be more efficient, and I would highly recommend that you instead code:
PACKAGE pk_temp AS
    
    PROCEDURE SP_tranout (
        v_id_tran   IN  tblTransaction.id_tran%TYPE
    ,   var_check   OUT NUMBER
    );
END pk_temp;
  
PACKAGE BODY pk_temp AS 
  
    PROCEDURE SP_tranout (
        v_id_tran   IN  tblTransaction.id_tran%TYPE
    ,   var_check   OUT NUMBER)
    IS
    BEGIN 
        SELECT COUNT(*)
        INTO   var_check
        FROM   DUAL
        WHERE  EXISTS (SELECT NULL
                       FROM   tblTransaction   tt
                       WHERE  tt.id_tran = v_id_tran);
    END SP_tranout;
       
END pk_temp; 
See here for more information on the modification I made.

Good luck, Shak.

A
Previous Topic: Getting previous records
Next Topic: table/array
Goto Forum:
  


Current Time: Mon Jul 07 23:21:19 CDT 2025