Forum Stats

  • 3,837,263 Users
  • 2,262,245 Discussions
  • 7,900,240 Comments

Discussions

Procedure call in ORDS

user8632739
user8632739 Member Posts: 12 Green Ribbon

When I call the procedure through PL/SQL , it works fine but when i call the same through ORDS with same input , it throws the below error. Please correct me.

SQL Error Code: 6550, Error Message: ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to ‘BALA_payc_details’ ORA-06550: line 2, column 8: PL/SQL:

Below is complete code

----------------------------------

BEGIN

ORDS.ENABLE_SCHEMA(

p_enabled => TRUE,

p_schema => ‘TestAPI’,

p_url_mapping_type => ‘BASE_PATH’,

p_url_mapping_pattern => ‘TestAPI’,

p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE(

p_module_name => ‘pay’,

p_base_path => ‘/pay/’,

p_items_per_page => 0,

p_status => ‘PUBLISHED’,

p_comments => NULL);

ORDS.DEFINE_TEMPLATE(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_priority => 0,

p_etag_type => ‘HASH’,

p_etag_query => NULL,

p_comments => NULL);

ORDS.DEFINE_HANDLER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_source_type => ‘plsql/block’,

p_items_per_page => 5,

p_mimes_allowed => ”,

p_comments => NULL,

p_source =>

‘begin

TCTD.PKG_BALA.BALA_payc_details(

finp_type => :nptype,

finp_value => :npvalue,

fIsPaycard => :card,

p_verrcode => :code,

p_verrdesc => :description,

v_refcur => :v_refcur);

end;’

);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘nptype’,

p_bind_variable_name => ‘nptype’,

p_source_type => ‘HEADER’,

p_param_type => ‘STRING’,

p_access_method => ‘IN’,

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘npvalue’,

p_bind_variable_name => ‘npvalue’,

p_source_type => ‘HEADER’,

p_param_type => ‘STRING’,

p_access_method => ‘IN’,

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘card’,

p_bind_variable_name => ‘card’,

p_source_type => ‘HEADER’,

p_param_type => ‘STRING’,

p_access_method => ‘IN’,

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘code’,

p_bind_variable_name => ‘code’,

p_source_type => ‘RESPONSE’,

p_param_type => ‘INT’,

p_access_method => ‘OUT’,

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘description’,

p_bind_variable_name => ‘description’,

p_source_type => ‘RESPONSE’,

p_param_type => ‘STRING’,

p_access_method => ‘OUT’,

p_comments => NULL);

ORDS.DEFINE_PARAMETER(

p_module_name => ‘pay’,

p_pattern => ‘/inquiry’,

p_method => ‘POST’,

p_name => ‘v_refcur’,

p_bind_variable_name => ‘v_refcur’,

p_source_type => ‘RESPONSE’,

p_param_type => ‘RESULTSET’,

p_access_method => ‘OUT’,

p_comments => NULL);

COMMIT;

END;

----------------------------------------

  1. Postman JSON Input…

{

“nptype” : “S”,

“nptypevalue” : “23456”,

“card” : “Y”

}


This throws error ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to ‘ ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to ‘BALA_payc_details’’

------------------------------------------------------------------------------------

2.. When I call the same procedure with below input in PL/SQL , it is working fine.

DECLARE

verrcode Number(10);

verrdesc varchar2(100);

v_refcur SYS_REFCURSOR;

BEGIN

TCTD.PKG_BALA.BALA_payc_details (‘S’,’23456′,’Y’,verrcode,verrdesc,v_refcur);

END;

-----------------------------------------------------------------------


Please let me know what i need to change in ORDS call

Tagged:

Answers

  • kdario
    kdario Member Posts: 3,528 Silver Crown

    Your parameters are defined with p_source_type => ‘HEADER’ so you need to pass them as http headers and not as json in request body.

    If you need to send parameters in request body, remove them from ORDS parameter definition, ORDS will automatically map parameters from json body to procedure parameters.

    Also, your parameter name is defined as p_name => ‘npvalue’, but in your postman example you have “nptypevalue” : “23456”

  • user8632739
    user8632739 Member Posts: 12 Green Ribbon

    Removed ORDS request parameter definition. Still I am getting the same error but same is working PL/SQL procedure call. Please let me know where is the problem,

    • "The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 6550, Error Message: ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'BALA_payc_details' ORA-06550: line 2, column 8: PL/SQL: Statement ignored."

    • Postman Input:

    {

        "finptype" :  "S",

        "finpvalue" : "23456",

        "ispaycard" : "Y"


    }

    ----------------------Updated Code--------------------------


    BEGIN

     ORDS.ENABLE_SCHEMA(

       p_enabled       => TRUE,

       p_schema       => 'PAPI',

       p_url_mapping_type  => 'BASE_PATH',

       p_url_mapping_pattern => 'papi',

       p_auto_rest_auth   => FALSE);   


     ORDS.DEFINE_MODULE(

       p_module_name  => 'pay',

       p_base_path   => '/pay/',

       p_items_per_page => 0,

       p_status     => 'PUBLISHED',

       p_comments    => NULL);    

     ORDS.DEFINE_TEMPLATE(

       p_module_name  => 'pay',

       p_pattern    => '/inquiry',

       p_priority    => 0,

       p_etag_type   => 'HASH',

       p_etag_query   => NULL,

       p_comments    => NULL);


     ORDS.DEFINE_HANDLER(

       p_module_name  => 'pay',

       p_pattern    => '/inquiry',

       p_method     => 'POST',

       p_source_type  => 'plsql/block',

       p_items_per_page => 5,

       p_mimes_allowed => '',

       p_comments    => NULL,

       p_source     => 

    'begin

        TCTD.PKG_BALA.BALA_payc_details(finp_type => :finptype,

        finp_value => :finpvalue,

        fIsPay => :ispaycard,

        p_verrcode => :code,

    p_verrdesc => :description,

    v_refcur => :v_refcur);

    end;'

       );

     

    ORDS.DEFINE_PARAMETER(

       p_module_name    => 'pay',

       p_pattern      => '/inquiry',

       p_method       => 'POST',

       p_name        => 'code',

       p_bind_variable_name => 'code',

       p_source_type    => 'RESPONSE',

       p_param_type     => 'INT',

       p_access_method   => 'OUT',

       p_comments      => NULL);    

     

    ORDS.DEFINE_PARAMETER(

       p_module_name    => 'pay',

       p_pattern      => '/inquiry',

       p_method       => 'POST',

       p_name        => 'description',

       p_bind_variable_name => 'description',

       p_source_type    => 'RESPONSE',

       p_param_type     => 'STRING',

       p_access_method   => 'OUT',

       p_comments      => NULL);    


     ORDS.DEFINE_PARAMETER(

       p_module_name    => 'pay',

       p_pattern      => '/inquiry',

       p_method       => 'POST',

       p_name        => 'v_refcur',

       p_bind_variable_name => 'v_refcur',

       p_source_type    => 'RESPONSE',

       p_param_type     => 'RESULTSET',

       p_access_method   => 'OUT',

       p_comments      => NULL);    

     COMMIT; 

    END;

  • kdario
    kdario Member Posts: 3,528 Silver Crown

    Can you show us BALA_payc_details() signature ? Also mention ORDS and database version.

    Any difference if you define handler as:

    begin
      TCTD.PKG_BALA.BALA_payc_details(:finptype, :finpvalue, :ispaycard, :code, :description, :v_refcur);
    end;
    

    (this is real equivalent of your working plsql call example)

  • user8632739
    user8632739 Member Posts: 12 Green Ribbon

    Thanks for your response.

    Original procedure Signature:

    Procedure BALA_payc_details(finp_type IN VARCHAR2,finp_value  IN varchar2,

                  fIsPaycard IN VARCHAR2,

                  verrcode out Number,verrdesc out varchar2,

                  v_refcur OUT SYS_REFCURSOR)


    Database: Oracle 12c (12.1)

    ORDS Version: 22.1



    I have many other Stored Procedure and Function call are working fine in ORDS with same version. I am not sure where is wrong in this particular procedure. I have converted rest of all SPs and Functions except this one. Even I have other similar procedure and functions are working fine.

  • kdario
    kdario Member Posts: 3,528 Silver Crown

    Will it work if you remove :v_refcur parameter from ORDS parameter definition and change handler to something like this:

    declare
      p_cursor sys_refcursor;
    begin
      TCTD.PKG_BALA.BALA_payc_details(:finptype, :finpvalue, :ispaycard, :code, :description, p_cursor);
    end;
    

    If this works, then issue is in result set returned by sys_refcursor (maybe ords can't map some data type).

  • user8632739
    user8632739 Member Posts: 12 Green Ribbon

    No, the above is not working...but you comments is shown lights to my issue and working. Thanks lot for your timely help and support.


    I changed to

     TCTDBS.PKG_CBQ_PAYCARD_ESB.esb_payc_details(:finptype,:finpvalue,:ispaycard,:code,:description,:p_cursor);


    from


     TCTDBS.PKG_CBQ_PAYCARD_ESB.esb_payc_details(finp_type => :finptype,

        finp_value => :finpvalue,

        fIsPaycard => :ispaycard,

        p_verrcode => :code,

    p_verrdesc => :description,

    v_refcur => :v_refcur);

    end;'


    Thanks once again.