Hi All,
Hope you are doing great.
I am new to Stored Procedure's. We have a basic requirement to create a stored procedure in Oracle DB which takes 1 parameters as Input and returns 7 Parameter as OUTPUT.
We have tried 2 ways. Both compiled successfully but when we try to execute the Procedure, it errors.
Stored Procedure(Version-1):
CREATE OR REPLACE PROCEDURE sysadm.HK_KFX_PO_SP
(
PO_NUMBER IN VARCHAR2
, LINE_NUM OUT NUMBER
, SCHED_NUM OUT NUMBER
, LINE_DESCR OUT VARCHAR2
, LINE_UOM OUT VARCHAR2
, LINE_UNIT_PRICE OUT NUMBER
, LINE_QUANTITY OUT NUMBER
, LINE_MERCH_AMOUNT OUT NUMBER
) AS
BEGIN
SELECT PL.LINE_NBR
, PS.SCHED_NBR
, PL.DESCR254_MIXED
, PL.UNIT_OF_MEASURE
, PS.PRICE_PO
, PS.QTY_PO
, PS.MERCHANDISE_AMT
INTO
LINE_NUM
, SCHED_NUM
, LINE_DESCR
, LINE_UOM
, LINE_UNIT_PRICE
, LINE_QUANTITY
, LINE_MERCH_AMOUNT
FROM PS_PO_HDR PH
, PS_PO_LINE PL
, PS_PO_LINE_SHIP PS
WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT
AND PH.PO_ID = PL.PO_ID
AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT
AND PL.PO_ID = PS.PO_ID
AND PL.LINE_NBR = PS.LINE_NBR
AND PH.PO_STATUS IN ('A', 'D')
AND PL.CANCEL_STATUS <> 'X'
AND PH.PO_ID = PO_NUMBER;
END HK_KFX_PO_SP;
Stored Procedure(Version-2):
CREATE OR REPLACE PROCEDURE HK_KFX_PO_SP
(
PO_NUMBER IN varchar2,
b_cursor OUT sys_refCURSOR
) AS
BEGIN
OPEN b_cursor FOR
SELECT PL.LINE_NBR "LINE_NUM"
, PS.SCHED_NBR "SCHED_NUM"
, PL.DESCR254_MIXED "LINE_DESCR"
, PL.UNIT_OF_MEASURE "LINE_UOM"
, PS.PRICE_PO "LINE_UNIT_PRICE"
, PS.QTY_PO "LINE_QUANTITY"
, PS.MERCHANDISE_AMT "LINE_MERCH_AMOUNT"
FROM PS_PO_HDR PH
, PS_PO_LINE PL
, PS_PO_LINE_SHIP PS
WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT
AND PH.PO_ID = PL.PO_ID
AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT
AND PL.PO_ID = PS.PO_ID
AND PL.LINE_NBR = PS.LINE_NBR
AND PH.PO_STATUS IN ('A', 'D')
AND PL.CANCEL_STATUS <> 'X'
AND PH.PO_ID = PO_NUMBER;
END HK_KFX_PO_SP;
when we execute the stored Procedure's, we are receiving the same error.
ORA-06550: Line-1, Column 7
PLS-00306:wrong number or type of arguments in call to HK_KFX_PO_SP
Any help is highly appreciated.