Mostly Making oracle easy, mostly:
Search for:
In ORDS

Executing PL/SQL with Array INPUTS via ORDS

1 Min Read

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.

The json doc matches up to the procedure INPUT variable name – this is not an accident 🙂
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.

There are my 3 records!

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. Ahmed Haroon Reply

    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.

  2. JD Reply

    “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?

    • thatjeffsmith Post Author

      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.

Reply To JD Cancel Reply