Getting error while Calling Oracle Stored Procedure with output Parameter

[Former Member]

HI All,

From long days i am working on this but i unable to solve it.

Even i have studied so many forums in SAP but i didn't find the solution.

I am calling Oracle Store procedure with 3 inputs and 1 output without cursor.

Store Procedure:-

CREATE OR REPLACE PROCEDURE PDS.send_rm

IS

proc_name VARCHAR2(64) := 'send_rm';

destination_system VARCHAR2(32) := 'RAWMAT';

xml_message VARCHAR2(4000);

status_code INTEGER;

status_message VARCHAR2(128);

debug_message VARCHAR2(128);

p_ret INTEGER;

BEGIN

DBMS_OUTPUT.PUT_LINE( proc_name || ' started' );

xml_message := '<RAW_MATERIAL>'||

'<BAR_CODE>10000764601</BAR_CODE>'||

'<MATERIAL>1101448</MATERIAL>'||

'<VENDOR_CODE/>'||

'<PRODUCTION_DATE>0000-00-00</PRODUCTION_DATE>'||

'<EXPIRE_DATE>0000-00-00</EXPIRE_DATE>'||

'<BATCH/>'||

'<PO_NUM/>'||

'<MATERIAL_DESCRIPTION>POWER SUPPLY</MATERIAL_DESCRIPTION>'||

'<SPEC_NAME/>'||

'<STOCK_CODE>BSW-JH</STOCK_CODE>'||

'<INSPECTION_LOT>00</INSPECTION_LOT>'||

'<USAGE_DECISION_CODE/>'||

'<MATERIAL_GROUP>031</MATERIAL_GROUP>'||

'</RAW_MATERIAL>';

dbms_output.put_line('XML '||xml_message);

-- vp_interface.load_rawmat@cnprpt1_pds(SYSDATE, destination_system,

-- xml_message, p_ret);

vp_interface.load_rawmat(SYSDATE, destination_system,

xml_message, p_ret);

dbms_output.put_line('Return Code '||p_ret);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

status_code := SQLCODE;

status_message := SUBSTR(SQLERRM, 1, 64);

-- Extract_Error_Logger(proc_name, 'LOCAL', SYSDATE, -999,

-- status_message, 0, debug_message);

ROLLBACK;

END send_rm;

And while i am calling this Store procedure in MII, I am facing error.

I have tried different ways but didnt solved

In SQL Query, i kept mode as: FixedQueryOutput

Can anyone tell me or send code for calling above store procedure

And onemore thing, While creating store procedure in Oracle for MII. Do we need to Create output parameter as cursor or normal.

Thanks,

Kind Regards,

Praveen Reddy M

[former_member381378]

Hi,

FixedQueryWithOutput should be selected as mode if your Stored procedure returns some output. I am not sure about what error you are getting. Are you getting the error to call all the stored procedures or for this specific one. You can try by modifying the stored procedure so that it returns a cursor(in your case it will have single field).

Thanks,

Soumen

[Former Member]

Hi Soumen,

Can you please confirm me, as we need to create store procedure which having output parameters as cursor or not.

And is it mandatory to use cursor for output parameter in store procedure.

please if there any chance, write a code for calling SP in MII.

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Hi All,

I am trying to call store procedure in MII with code

call <pkgname>.<SPname>(SYSDATE,'TH','[Param.2]',:p_ret)

Here i am passing 3 input parameters and 1 output parameter(r_pet)

This is the error i get when i use above SP:

java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'SPname'

Any help would be grateful.

Thanks,

Kind Regards,

Praveen Reddy M

Edited by: praveen2425 on Feb 23, 2012 7:13 AM

[Former Member]

Hi All,

Is we need to declare output parameter with cursor in Oracle SP for calling in MII

Need help

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Try using EXEC spname @SYSDATE='[Param.1]',@destination_system='[Param.2]',@xml_message='[Param.3]'

Query mode : FixedQuery

and try giving the inputs in input parameters tab and test the query template.

Param.1 would be GETDATE() which gives you your system date.

Param.2 would be TH

Param.3 would be xml

Regards,

Priyanka

[Former Member]

Hi Priyanka,

Thanks for your response

I have tried as you said,but i am facing below error

exec <SPname> @SYSDATE='[Param1]',@destination_system='TH',@xml_message='[Param.2]'

error:

java.sql.SQLException: ORA-00900: invalid SQL statement

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Hi Praveen,

Please see that you have coded the statement properly .Check for quotes and syntax's. End the statement with ; .

I feel we dont have to pass any parameter for SYSDATE as it automatically returns the current date and time for the OS.Its a keyword.so no param for SYSDATE. only pass 2 params for destination system and xml-message.

your statement should look something like this -

---

EXEC udp_WIP_getResyCounts @Complex='[Param.1]',@Brand_code='[Param.2]';

in this above example udp_WIP_getResyCounts is the stored procedure name. Replace it with your stored procedure name.

Replace

Complex with destination_system

Brand_code with xml_message.

Give some inputs in the input parameters tab when you test the query template.

Did you change the query mode to FixedQuery?

Regards,

Priyanka

[Former Member]

Hi Priyanka,

As you said, exactly i have done but no luck

Here we have 3 input parameters and 1 output parameter

As of my knowledge we have to us CALL syntax for calling oracle store procedure, not EXEC

And for calling oracle store procedure we need to keep mode as fixedqueryoutput, if we have output parameters in the SP

Any more help

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Hi praveen,

Its not that way.... if you are using an Oracle db then you must use FixedQueryWithOutput.....and if its an sql server then use fixedquery as the mode. It actually depends on what database your using.

And if this stored procedure is returning a ref cursor as an output and if its the oracle db....then use fixedquerywithoutput and CALL statement.

CALL syntax

CALL STOREDPROCEDURENAME(SYSDATE,'[PARAM.1]','[PARAM.2]',:PACKAGENAME)               
                                         

or sometimes it might work even if dont include the package name...it would be something like this

CALL STOREDPROCEDURENAME(SYSDATE,'[PARAM.1]','[PARAM.2]')     

else if the SP's output is a data or a resultset...then use fixedquery as the mode and EXEC statement.

so please find out what this stored procedure is exactly returning then i thnk it will be helpful.

Regards,

Priyanka Balla

Edited by: Michael Appleby on Feb 24, 2012 1:44 PM adding for better viewing

[Former Member]

Hi Priyanaka,

Thanks for your fast reponse

I am calling Oracle store procedure with 1 output parameter

But in oracle store procedure, they didnt declared output parameter as cursor

So, that mate be an issues. And client is not willing to change store procedure

Even i have tried all the scenarios which you have specified in the forum

And can you give clarity like, is 100% do we need to declare output parameter as with cursor

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Hi Praveen

We encountered a similar scenario as yourself.

- The output parameter MUST be a cursor.

Also: Our solution was to ask for a "wrapper" procedure to be created for us.

regards

Amrik

[Former Member]

Hi Amrik,

Is it solved your problem

Can you please explain more on "wrapper" Procedure

And if possible post the SP code and calling the SP

Thanks,

Kind Regards,

Praveen Reddy M

[Former Member]

Hi Praveen

Our wrapper was created because we could not modify the procedure we call (it was not returning a cursor).

CREATE OR REPLACE PROCEDURE CHECK_PUT_IN_USE
(STRCMPNAME in varchar2,
 STRSCANLABEL in varchar2,
 RCT1 out SYS_REFCURSOR
)
AS
  charDispo		Char(1);
  charStatus		Char(1);
  intCatNo		Integer;
  charCatDispo	Char(1);
  strCatQual		VarChar2(2);
  strCatDesc		VarChar2(30);
  strMsg		VarChar2(128);

BEGIN
 qa.check_put_in_use@AR(STRCMPNAME,
                                          STRSCANLABEL,
                                          charDispo,
                                          charStatus,
                                          intCatNo,
                                          charCatDispo,
                                          strCatQual,
                                          strCatDesc,
                                          strMsg);
 
OPEN RCT1 
FOR Select charDispo,charStatus,charDispo,charStatus,intCatNo,charCatDispo,strCatQual,strCatDesc,strMsg from Dual;
END;

Hope this helps

Regards

Amrik

then with a FixedQueryWithOutput

call mixar.qasap.wrapper_update_put_in_use('[Param.1]','[Param.2]',[Param.3],?) 

Hope this helps.

[Former Member]

Hi Amrik,

Thanks for you response

How many inputs your are passing from MII to that SP and getting outputs from SP to MII

And have look to our SP as mentioned below

CREATE OR REPLACE PROCEDURE PDS.send_rm

IS

proc_name VARCHAR2(64) := 'send_rm';

destination_system VARCHAR2(32) := 'RAWMAT';

xml_message VARCHAR2(4000);

status_code INTEGER;

status_message VARCHAR2(128);

debug_message VARCHAR2(128);

p_ret INTEGER;

for more detail check 1st post of the forum

And from above, p_ret is the output for SP

Thanks,

Kind Regards,

Praveen Reddy