Oracle REST Handler Split Payload-CodePudding

I've installed Oracle DB/ORDS and created all the handlers and related procedures by following Oracle-base guides. There is one issue which I was not able to solve, which seems simple but my lack of understanding SQL hinders me.

When I send JSON documents in bulk such as below;

{"tenantId":"wjg79702","userSessionId":"JAFLA....\n
{"tenantId":"wjg79702","userSessionId":"SLPW....\n
\n

This inserts all the records as expected. But I would like to insert every record one by one into individual CLOB columns. There is line break at the end of every record. How can I split them by incorporating additional functionality into my handler or procedure?

Handler

BEGIN
      create_kayit(p_kayit => :body_text);
END;

Procedure

create or replace PROCEDURE create_kayit (
p_kayit IN CLOB
)
AS
BEGIN
    INSERT INTO uemtest (kayit) VALUES (p_kayit);
EXCEPTION
    WHEN OTHERS THEN
        HTP.print(SQLERRM);
END;

CodePudding user response：

Answer your comment, if you want to do split by return carriage, one option might be regexp_substr and connect by

Example

SQL> select  regexp_substr(to_clob('line 1
  2  line 2
  3  line 3') , '. ',1,level,'m') from  dual
  4  connect by level <= regexp_count( to_clob('line 1
  5  line 2
  6* line 3'), '. ',1,'m')
  7  /

REGEXP_SUBSTR(TO_CLOB('LINE1LINE2LINE3'),'. ',1,LEVEL,'M')
--------------------------------------------------------------------------------
line 1
line 2
line 3

You just need to incorporate this logic into your procedure.

CodePudding user response：

I think your suggestion has worked. I modified the procedure the POST handler executes in each request like below. Now every payload data is being split by carriage return and inserted into the table row by row. Thank you very much!

CREATE OR REPLACE PROCEDURE create_kayit (p_kayit IN CLOB) AS
BEGIN
        INSERT INTO UEMTEST (KAYIT)
        SELECT
        REGEXP_SUBSTR(p_kayit, '. ', 1, level, 'm') KAYIT
        FROM DUAL connect by level <= length(REGEXP_REPLACE(p_kayit,'. '));
EXCEPTION
    WHEN OTHERS THEN
        HTP.print(SQLERRM);
END;