This Post will having the details to create stored procedure in oracle and parsing the clob data through stored procedure.
employee.xml -------------Above xml will be stored as clob data in Person tablesiva 32 M
Person table Schema -
Id NUMBER,
Name Varchar2(50),
employee_details clob
Above xml will be stored as clob data in Person table
create table PERSON (Id NUMBER, Name varchar2(50), employee_details clob); create table EMPLOYEE (Name varchar2(50), age NUMBER ,sex varchar2(10));We need to parse the clob data using oracle procedure
create or replace PROCEDURE SP_PARSE_CLOB (person_id NUMBER) AS
PERSION_ID Person.id%type;
PERSON_NAME Person.name%type;
EMPLOYEE_DETAILS Person.employee_details%type;
name varchar2(50);
age varchar2(50);
sex varchar2(50);
EMPLOYEE_exc EXCEPTION;
-- Create a cursor
cursor personDataCursor IS SELECT id, Name,employee_details from PERSON;
BEGIN
OPEN personDataCursor;
LOOP
FETCH personDataCursor into PERSION_ID,PERSON_NAME,EMPLOYEE_DETAILS;
EXIT WHEN personDataCursor%notfound;
IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/name/text()') > 0 THEN
name := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/name/text()').getStringVal();
END IF;
IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/age/text()') > 0 THEN
age := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/age/text()').getStringVal();
END IF;
IF XMLTYPE(EMPLOYEE_DETAILS).existSNode('/Employee/sex/text()') > 0 THEN
sex := XMLTYPE(EMPLOYEE_DETAILS).extract('/Employee/sex/text()').getStringVal();
END IF;
dbms_output.put_line('Name:'|| name|| ' ' || 'Age:' || ' ' ||age || ' ' || 'Sex:' || ' ' || sex);
-- Inset into another table
BEGIN
insert into EMPLOYEE(name,age,sex) values(name,age,sex);
EXCEPTION
WHEN OTHERS
THEN
RAISE EMPLOYEE_exc;
END;
commit;
END LOOP;
EXCEPTION
WHEN EMPLOYEE_exc
THEN
rollback;
-- Do what ever you want like insert log details in any another table
commit;
DBMS_OUTPUT.PUT_LINE ('Insertion failed in EMPLOYEE : '|| '' || name);
END;
