/*create directroy atul as 'd:\try';
create table emp_try and emp_try2 as select * from emp;
have all the grants regarding,create directry,read,write,utl_file,javauserpriv,javasyspriv,dbms_lob,dbms_job etc...*/


/* CREATE FOLLOWING IN SYS AND GRANT EXECUTE TO ALL;

create or replace function getMaxMemorySize return number
is language java name
'oracle.aurora.vm.OracleRuntime.getMaxMemorySize() returns long';
/
create or replace function setMaxMemorySize(num number) return number
is language java name
'oracle.aurora.vm.OracleRuntime.setMaxMemorySize(long) returns long';
/

Runspace
========
create or replace function getMaxRunspaceSize return number
is language java name
'oracle.aurora.vm.OracleRuntime.getMaxRunspaceSize() returns long';
/
create or replace procedure setMaxRunspaceSize(num number) 
is language java name 
'oracle.aurora.vm.OracleRuntime.setMaxRunspaceSize(long)';
/

Increase the memory and runspace size 
=====================================
begin 
/* Add this line to increase the memorysize --- */ 
v_value:= setMaxMemorySize(800000000); 

/* Add this line to increase the runspacesize --- */ 
setmaxrunspacesize(250000000); 

/* You can check MaxMemorySize and Maxrunspacesize using below selects
select getMaxMemorySize from dual;
select getmaxrunspacesize from dual;
*/



CREATE OR REPLACE procedure XMLinsProc(xmldoc in CLOB, tablename in varchar2)  is

   insCtx DBMS_XMLSave.ctxType;
   rows number;


 begin

 	  DELETE FROM EMP_TRY2; 
 	  COMMIT;
 	 
    insCtx := DBMS_XMLSave.newContext(tablename); -- get the context handle
     DBMS_XMLSAVE.SETIGNORECASE(INSCTX,1);
    rows := DBMS_XMLSave.insertXML(insCtx,xmldoc); -- this inserts the document
    DBMS_XMLSave.closeContext(insCtx);
commit;          -- this closes the handle
end;

/
SHOW ERR;

CREATE OR REPLACE procedure XMLINSERT is

v_xml_text clob;

begin
  	
   select xml_text
   into v_xml_text
   from xmldocument ;
   
   XMLinsproc(v_xml_text,'EMP_TRY2');  

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

when others then
dbms_output.put_line(SQLERRM || SQLCODE);

end;
/

create or replace PROCEDURE XMLFILE2XMLDOCUMENT 
 IS
       src_file BFILE;
        C    CLOB;
        lgh_file BINARY_INTEGER; 
   BEGIN
      src_file := bfilename('ATUL','ATUL.XML');
     DELETE FROM XMLDOCUMENT;
     COMMIT;
     INSERT INTO SCOTT.XMLDOCUMENT VALUES(1, EMPTY_CLOB());
     COMMIT;
      SELECT XML_TEXT INTO C FROM SCOTT.XMLDOCUMENT  WHERE ID = 1 FOR UPDATE; 
      lgh_file := dbms_lob.getlength(src_file);
      dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
      dbms_lob.loadfromfile(C, src_file, lgh_file);
      --INSERT INTO SCOTT.XMLDOCUMENT VALUES(1,C);
 
     UPDATE SCOTT.XMLDOCUMENT 
     SET XML_TEXT = C 
     WHERE ID = 1; 
      COMMIT;
          IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('RECORD INSERTED SUCCESSFULLY........ ');
            COMMIT;
         END IF;
     
  END;
/










DECLARE 
V_VALUE NUMBER;

BEGIN
/* Add this line to increase the memorysize --- */ 
v_value:= SYS.setMaxMemorySize(800000000); 

/* Add this line to increase the runspacesize --- */ 
SYS.setmaxrunspacesize(250000000); 
END;
/