Today’s question:
Does ORDS support PUT/POST with Array or Cursor as part of the payload? I need to call a procedure with 1 IN parameter defined as Array or Cursor (for a list of IDs).
The answer is Yes, and I’ll show with the AUTO Feature.
The Underlying Code:
CREATE TABLE CONTACT ( NAME VARCHAR2(30 BYTE), PHONE VARCHAR2(20 BYTE), SOMETHING DATE ); CREATE OR REPLACE PACKAGE forums_plsql_table AS TYPE vc_arr IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; PROCEDURE ins_c ( p_name IN vc_arr ); END forums_plsql_table; / CREATE OR REPLACE PACKAGE BODY forums_plsql_table AS PROCEDURE ins_c ( p_name IN vc_arr ) AS BEGIN FOR i IN 1..p_name.COUNT LOOP INSERT INTO contact ( name, phone, something ) VALUES ( p_name(i), TO_CHAR(i), SYSDATE - i ); END LOOP; COMMIT; END ins_c; END forums_plsql_table;
Executing FORUMS_PLSQL_TABLE.INS_C() via POST
First, let’s Auto PL/SQL Enable the Package.
BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'HR', p_object => 'FORUMS_PLSQL_TABLE', p_object_type => 'PACKAGE', p_object_alias => 'forums_plsql_table', p_auto_rest_auth => FALSE); COMMIT; END;
Second, let’s call the package.
curl --request POST \ --url http://localhost:8080/ords/hr/forums_plsql_table/INS_C \ --header 'content-type: application/json' \ --data '{ "p_name" : [ "Ugly", "Kid", "Joe"] }'
So in my POST I passed a collection of 3 values, and so I will have 3 records INSERTed.
Now, if I wanted to be nice (i.e. build a PROFESSIONAL web service), I’d capture the inserted record IDs, and set the status code appropriately, and of course I’d have some exception handling bits and capture any errors properly.
But I’m Not Using the Auto Feature…
Then your handler code will need to grab the JSON off the POST body, and you’ll build your own PL/SQL block to make the procedure call.
5 Comments
hi, is there any example to have POST handler which receives array of data in JSON and create records (insert) which has multiple columns like emp: empno, ename, hiredate, sal … we had a requirement and i am here while searching for a solution.
How is what i’m showing here not a viable solution?
“Then your handler code will need to grab the JSON off the POST body, and you’ll build your own PL/SQL block to make the procedure call.”
Do you have (or can you point to) an example of what this looks like?
The first part is easy, read it in as blob or clob using the :body or :body_text, and then you’re going to have to write some SQL or PL/SQL to manipulate that JSON and it’s elements. I’m not the guy to do that for you.
Thank you for this post.