Home » SQL & PL/SQL » SQL & PL/SQL » Outputing parameters
Outputing parameters [message #5940] |
Thu, 13 March 2003 08:48  |
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  |
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
|
|
|
Goto Forum:
Current Time: Mon Jul 07 23:21:19 CDT 2025
|