10 Replies Latest reply: Feb 24, 2012 2:07 PM by Praveen Reddy M RSS

Getting error while Calling Oracle Stored Procedure with output Parameter

Praveen Reddy M
Currently Being Moderated

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

  • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
    Soumen Mondal
    Currently Being Moderated

    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

    • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
      Praveen Reddy M
      Currently Being Moderated

      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

      • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
        Praveen Reddy M
        Currently Being Moderated

        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

        • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
          Praveen Reddy M
          Currently Being Moderated

          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

          • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
            Chandra Priyanka Balla
            Currently Being Moderated

            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

            • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
              Praveen Reddy M
              Currently Being Moderated

              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

              • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                Chandra Priyanka Balla
                Currently Being Moderated

                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

                • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                  Praveen Reddy M
                  Currently Being Moderated

                  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

                  • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                    Chandra Priyanka Balla
                    Currently Being Moderated

                    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

                    • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                      Praveen Reddy M
                      Currently Being Moderated

                      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

                      • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                        Amrik Sandhu
                        Currently Being Moderated

                        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

                        • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                          Praveen Reddy M
                          Currently Being Moderated

                          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

                          • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                            Amrik Sandhu
                            Currently Being Moderated

                            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.

                            • Re: Getting error while Calling Oracle Stored Procedure with output Parameter
                              Praveen Reddy M
                              Currently Being Moderated

                              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

Actions