Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

insert date into oracle json_Table

Arif2018Apr 4 2021

i am trying to create one procedure to handle multiple inserts using ords and json_table. i have created the following table emp for inserting employees. while creating the procedure i am getting error as below.
ORA-40484 : ivalid data type for JSON_TABLE COLUMN
[code]
CREATE TABLE EMP
( ID NUMBER(2),
NAME VARCHAR2(14 CHAR),
DOB DATE
);
CREATE OR REPLACE PROCEDURE create_employee (p_data IN BLOB)
AS
BEGIN
INSERT INTO dept (id, dname,dob)
SELECT *
FROM json_table(p_data FORMAT JSON, '$.employees[*].employee'
COLUMNS (
id NUMBER PATH '$.ID',
name VARCHAR2 PATH '$.name',
DOB TIMESTAMP PATH '$.dob'
));

COMMIT;

EXCEPTION
WHEN OTHERS THEN
HTP.print(SQLERRM);
END;
/
[/code]

Comments
Post Details
Added on Apr 4 2021
29 comments
2,009 views