Reputation: 93
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