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]