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.

thatjeffsmith
Author

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

1 Comment

Write A Comment