I found this: https://www.thatjeffsmith.com/archive/2018/12/executing-pl-sql-with-array-inputs-via-ords/
And I've been able to successfully accomplish the same - or at least it doesn't error out like in the non-auto mode.
However, what I'm trying to do receive an array on a non-auto rest enabled handler, but I just get this error whenever I pass in the bind:
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: Invalid column type, Error Message: {2}.
Am I missing anything basic in my setup?
Full details on my implementation:
I defined a simple package that receives an array.
create or replace package ords_array_test
as
TYPE vc\_arr IS
TABLE OF VARCHAR2(30) INDEX BY BINARY\_INTEGER;
procedure loop\_array(
p\_names in vc\_arr
);
end ords_array_test;
/
create or replace package body ords_array_test
as
procedure loop\_array(
p\_names in vc\_arr
)
as
begin
null;
end loop\_array;
end ords_array_test;
/
Then, in my payload I pass in a variable which is an array- and usually you can access that variable using bind syntax, so I try passing that.
My install script in the end looks like this:
declare
l\_module\_name ords\_metadata.user\_ords\_modules.name%type;
l\_module\_base\_path ords\_metadata.user\_ords\_modules.uri\_prefix%type;
l\_post\_pattern ords\_metadata.user\_ords\_templates.uri\_template%type;
begin
l\_module\_name := 'foo';
l\_module\_base\_path := 'v1/arrEntity';
l\_post\_pattern := '/';
ords.enable\_schema(true);
ords.define\_module(
p\_module\_name => l\_module\_name,
p\_base\_path => l\_module\_base\_path);
ords.define\_template(
p\_module\_name => l\_module\_name,
p\_pattern => l\_get\_pattern);
ords.define\_handler(
p\_module\_name => l\_module\_name,
p\_pattern => l\_post\_pattern,
p\_method => 'POST',
p\_source\_type => ords.source\_type\_plsql,
p\_source => q'!
declare
begin
:content\_type := 'application/json';
ords\_array\_test(
p\_names => :names
);
:success := 'true';
end;
!'
);
ords.define\_parameter(
p\_module\_name => l\_module\_name
, p\_pattern => l\_post\_pattern
, p\_method => 'POST'
, p\_name => 'Content-Type'
, p\_bind\_variable\_name => 'content\_type'
, p\_source\_type => 'HEADER'
-- , p\_param\_type => p\_param\_type
, p\_access\_method => 'OUT'
);
--
ords.define\_parameter(
p\_module\_name => l\_module\_name
, p\_pattern => l\_post\_pattern
, p\_method => 'POST'
, p\_name => 'success'
, p\_bind\_variable\_name => 'success'
, p\_source\_type => 'RESPONSE'
-- , p_param_type => 'STRING'
, p\_access\_method => 'OUT'
);
end;
/