create table xmldocument ( ID NUMBER(1), XML_TEXT CLOB);

create table emp_byxml as select * from scott.emp;
----------------------------------------------------------





create or replace PROCEDURE FILE2CLOBCONVERT
IS

   v_file      utl_file.file_type;
    C           CLOB := NULL;
    str         VARCHAR2(32767);
    str1        VARCHAR2(32767) := NULL; 
    TEMP_VAR NUMBER(12); 
    
   
 BEGIN
    v_file := utl_file.fopen('C:\TRY\','XMLTRY.XML' ,'R');
  IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('FILE CANT OPEN');
   END IF;
   WHILE (TRUE) LOOP
     BEGIN
     utl_file.get_line(v_file, STR);
     DBMS_OUTPUT.PUT_LINE(STR);
     STR1 := STR1 || STR;
    
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         utl_file.fclose(v_file);
         EXIT;
     END;

   END LOOP;

   DELETE FROM XMLDOCUMENT; 
   COMMIT;
     	
     	SELECT  LENGTH('STR1') INTO TEMP_VAR  FROM DUAL;
     	
     	DBMS_OUTPUT.PUT_LINE(' LENGTH FETCHED : ' || TEMP_VAR); 
     	 
     	
     INSERT INTO XMLDOCUMENT VALUES(1,STR1);  -- INSERT XML CLOB DATA INTO A TABLE I.E. XMLDOCUMENT (TEMP)
     IF SQL%FOUND THEN
     		DBMS_OUTPUT.PUT_LINE('RECORD INSERTED SUCESSFULLY');
     commit;

   	insert_final_data;  --CALL PROCEDUARE WHICH FETCH DATA FROM CLOB FORMAT AND INSERT INTO VARCHAR FORMAT 
     ELSE
    			DBMS_OUTPUT.PUT_LINE('RECORD CANT BE INSERTED');
  END IF;
  
 
 END;
*********************************************************************


create or replace  procedure insert_final_data is

v_xml_text clob;

begin
			--- SELECT REC LIKE XML TEXT  FROM 'XMLDOCUMENT'  TABLE AND STORE INTO VARIABLE  
 			
 			select xml_text
			into v_xml_text
			from xmldocument ;
			--where docid = 1;


			insproc(v_xml_text,'emp_byxml');  

exception
when no_data_found then
dbms_output.put_line('Sorry no data found...');

when others then
dbms_output.put_line('other exception raised....');

end;

******************************************************

create or replace  procedure insProc(xmldoc in CLOB, tablename in varchar2)  is
   insCtx DBMS_XMLSave.ctxType;
   rows number;
 begin
 	
 	
 	  DELETE FROM EMP_BYXML; 
 	  COMMIT;
 	 
    insCtx := DBMS_XMLSave.newContext(tablename); -- get the context handle
    rows := DBMS_XMLSave.insertXML(insCtx,xmldoc); -- this inserts the document
    DBMS_XMLSave.closeContext(insCtx);
commit;          -- this closes the handle
end;