OraFAQ Forum: General » Stored Procedure Question. HELP

Home » Other » General » Stored Procedure Question. HELP (ORACLE DATABASE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stored Procedure Question. HELP [message #672371]

Fri, 12 October 2018 13:05

gethariprasad
Messages: 2
Registered: October 2018

Junior Member

Hi All,

I am new to Stored Procedure's, Package's and Cursor's. We have a basic requirement to create a stored procedure which takes one input parameter and return 7 out parameters.

The Stored Procedure has compiled properly. But when we try to execute it, it is giving me 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;

Errors:
ORA-06550: line 1 column 7:
PLS-00306: wrong number or types of arguments in call to 'HK_KFX_PO_SP'

When i ran the SQL inside the stored procedure in TOAD, it executes fine.

Please find the attached screenshots.

I do not know what is wrong with this Proc.

Any help would be highly appreciated.

/foru/forum/fa/13949/0/

Attachment: HK_KFX_PO_SP_Error.jpg
(Size: 96.11KB, Downloaded 2776 times)

Report message to a moderator

Re: Stored Procedure Question. HELP [message #672372 is a reply to message #672371]

Fri, 12 October 2018 13:34

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

The error message is pretty obvious: you didn't call the procedure with the same parameters than you declare it.
Indeed, your procedure has 2 or 8 parameters and you call it with only one parameter.

Report message to a moderator

Re: Stored Procedure Question. HELP [message #672373 is a reply to message #672372]

Fri, 12 October 2018 13:45

gethariprasad
Messages: 2
Registered: October 2018

Junior Member

Thanks for the quick response.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

I only has 1 input Parameter(PO_NUMBER).

That's why i have passed 1 input variable.

SET SERVEROUTPUT ON;
EXEC sysadm.HK_KFX_PO_SP('GEN0000146');

Correct me, if i am wrong.

Report message to a moderator

Re: Stored Procedure Question. HELP [message #672375 is a reply to message #672373]

Fri, 12 October 2018 14:05

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You are wrong. Smile

Version 1: Your procedure has 1 input parameter and 7 output parameters, you have to call it with 1 input parameter (maybe a constant as you did it) and 7 output parameters that must be variables.
Version 2: same thing but with 1 output parameter which must be a ref cursor variable.

[Edit: missing word]

[Updated on: Mon, 15 October 2018 04:12]

Report message to a moderator

Re: Stored Procedure Question. HELP [message #672422 is a reply to message #672375]

Mon, 15 October 2018 04:00

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

https://community.oracle.com/thread/4178040

Report message to a moderator

Previous Topic:	Basic question
Next Topic:	Problem with timezone date (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 05 12:05:43 CST 2025