CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle12c
MrM
MrM

Reputation: 399

Oracle JSON_OBJECT_T To Clob

I am creating a procedure which takes a JSON CLOB as IN OUT argument. My question though is how to cast a JSON_OBJECT_T to a CLOB? I wish to maintain the JSON structure in the CLOB and not only store the json value (See JSON_OBJECT_T.GET_CLOB('firstname') below).

My sample testcode: 

CREATE TABLE employees
   ( employee_id NUMBER(6)
   , first_name VARCHAR2(20)
   , last_name VARCHAR2(25)
   CONSTRAINT emp_last_name_nn NOT NULL
   , email VARCHAR2(25)
   CONSTRAINT emp_email_nn NOT NULL
   , phone_number VARCHAR2(20)
   , hire_date DATE
   CONSTRAINT emp_hire_date_nn NOT NULL
   , job_id VARCHAR2(10)
   CONSTRAINT emp_job_nn NOT NULL
   , salary NUMBER(8,2)
   , commission_pct NUMBER(2,2)
   , manager_id NUMBER(6)
   , department_id NUMBER(4)
   , CONSTRAINT emp_salary_min
   CHECK (salary > 0) 
   , CONSTRAINT emp_email_uk
   UNIQUE (email)
   ) ;

INSERT INTO EMPLOYEES VALUES (123,'John','Doe','[email protected]',99999999,TRUNC(SYSDATE),234,60000,null,null,null)


--CREATE OR REPLACE PROCEDURE GETEMPLOYEE (P_CLOB IN OUT CLOB) 
--AS
DECLARE
P_CLOB CLOB :=
'{  
   "employee_id":123
}';
V_JSON_IN JSON_OBJECT_T := JSON_OBJECT_T.PARSE(P_CLOB);
V_EMP_ID NUMBER := V_JSON_IN.GET_NUMBER('employee_id');
V_EMP_REC EMPLOYEES%ROWTYPE;
V_JSON_OUT JSON_OBJECT_T := NEW JSON_OBJECT_T;


BEGIN
  P_CLOB := EMPTY_CLOB();
  SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_ID;

  V_JSON_OUT.PUT('firstname',V_EMP_REC.First_Name);
  V_JSON_OUT.PUT('lastname',V_EMP_REC.Last_Name);
  V_JSON_OUT.PUT('salary',V_EMP_REC.Salary);


  P_CLOB := V_JSON_OUT.GET_CLOB('firstname');  
  DBMS_OUTPUT.put_line(TO_CHAR(P_CLOB)); -- JOHN

  ---How to cast V_JSON_OUT to Clob and store in P_CLOB?       
END;

Upvotes: 6

Views: 21118

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You're using get_clob() which gets a specific key value.

You can use to_string() or to_clob() to serialize the entire JSON object to a single string value:

Serialization is the inverse of the parse function. The serialization operation takes the in-memory representation of the JSON data and prints it to a string. The serialization functions and procedures are:

MEMBER FUNCTION to_String    RETURN VARCHAR2  
...  
MEMBER FUNCTION to_Clob      RETURN CLOB  
...

So for your code:

  P_CLOB := V_JSON_OUT.TO_CLOB;  
  DBMS_OUTPUT.put_line(P_CLOB);

Using the HR-schema version of that table, which already has an ID 123, that prints out:

{"firstname":"Shanta","lastname":"Vollman","salary":6500}

Upvotes: 9

Related Questions