Vitor Mira
Vitor Mira

Reputation: 93

Oracle ORDS GET handler with associative array as input parameter

I would like to create a handler using http GET to invoke the following PL/SQL procedure

create or replace PROCEDURE test_array (
    ait_string   IN dbms_sql.varchar2_table,
    query_result out SYS_REFCURSOR
) as begin
delete  from v;
for k in 1..ait_string.count loop
insert into v values
    ( ait_string ( k ) );
    end
        loop;
    open query_result
    for select C from v;
end;
/

I attempted to create ORDS handler like that

-- Generated by ORDS REST Data Services 24.1.1.r1201228
-- Schema: ???  Date: Sun Jun 23 11:15:23 2024 -03
--

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'MY_ORDS_SCHEMA',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'MY_ORDS_SCHEMA',
      p_auto_rest_auth      => TRUE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'test',
      p_base_path      => '/test/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'begin
    test_array (
    ait_string   => :ait_string,
    query_result => :queryresult
    );
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'ait_string',
      p_bind_variable_name => 'ait_string',
      p_source_type        => 'URI',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'test',
      p_pattern            => 'test',
      p_method             => 'GET',
      p_name               => 'queryresult',
      p_bind_variable_name => 'queryresult',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT',
      p_comments           => NULL);

    
        
COMMIT;

END;

I made the following request to test it

curl --location "https://<HOST>/ords/MY_ORDS_SCHEMA/test/test?ait_string=gjdklsjf"

and that error occurred

648{
    "code": "UserDefinedResourceError",
    "title": "User Defined Resource Error",
    "message": "The request could not be processed for a user defined resource",
    "o:errorCode": "ORDS-25001",
    "cause": "An error occurred when evaluating a SQL statement associated with this resource. SQL Error Code 6550, Error Message: ORA-06550: line 2, column 5:\nPLS-00306: wrong number or types of arguments in call to 'TEST_ARRAY'\nORA-06550: line 2, column 5:\nPL/SQL: Statement ignored\n",
    "action": "Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource",
    "type": "tag:oracle.com,2020:error/UserDefinedResourceError",
    "instance": "tag:oracle.com,2020:ecid/a70c72733d7312560f172b988f8f294a"
}

what would be the correct way to define the handler? Should value 'STRING' be used on call to define_parameter for the associate array parameter?

Upvotes: 0

Views: 142

Answers (0)

Related Questions