?
Solved

UTL_FILE & XML parser

Posted on 2005-05-07
3
Medium Priority
?
856 Views
Last Modified: 2012-06-27
I give my scenario below:
-----------------------------

I have 2 tables with almost identical structure and data. Based on the join condition [composite primary keys in the 2 tables], I need to compare the data in these 2 tables row by row and write the comparison results to flat file{using UTL_FILE} and xml file{using XML parser}. Please tell me how this can be achieved with sample PL/SQL code.

thanks

0
Comment
Question by:chandua
1 Comment
 
LVL 21

Accepted Solution

by:
oleggold earned 400 total points
ID: 13955405
Try to use this:
CREATE OR REPLACE PROCEDURE loadxml
(PTABLE IN VARCHAR2 DEFAULT 'HAZMANA_AV_LDR' )
IS
ERR_MESS VARCHAR2(700);
fil    BFILE;
v_clob   CLOB;
fille UTL_FILE.FILE_TYPE;
LINESNUM INTEGER;
VARBUFF VARCHAR2(32767);
buffer RAW(32767);
BBUFF CLOB;
len     FLOAT;    
insrow INTEGER;
rowchunk INTEGER;
pos INTEGER;
pose INTEGER;
indx INTEGER;
posver INTEGER;
ins_num INTEGER:=650;
/*XMLROWS_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('<ROW>');
XMLROOTS_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('<ROWSET>');
XMLROWE_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('</ROW>');
XMLROOTE_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('</ROWSET>');*/
XMLROWB VARCHAR2(1777);
DATAROWB VARCHAR2(27777);
XMLVER VARCHAR2(377);
XMLROWS_TAG VARCHAR2(377):='<ROW>';
XMLROOTS_TAG VARCHAR2(377):='<ROWSET>';
XMLROWE_TAG VARCHAR2(377):='</ROW>';
XMLROOTE_TAG VARCHAR2(377):='</ROWSET>';
XMLROWD RAW(32767);
insCtx DBMS_XMLSave.ctxType;
BEGIN
fil:=BFILENAME('XML_DIR',PTABLE||'.XML');
DBMS_LOB.FILEOPEN(fil,DBMS_LOB.FILE_READONLY);
len := DBMS_LOB.GETLENGTH(fil);
-- LINESNUM:=DBMS_LOB.GETCHUNKSIZE(fil);
dbms_lob.createtemporary(v_clob, cache=>FALSE);
if len<ins_num then
ins_num:=len;
end if;

  -- Private type declarations
--  type <TypeName> is <Datatype>;
   
-- xmlgen.setmaxrows(ins_num);
/*xmlgen.resetOptions;  
xmlgen.setIgnoreTagCase(xmlgen.IGNORE_CASE); */
-- xmlgen.setRowsetTag('ROWSET') ;

-- ins_num:=len/100000000;


-- DBMS_LOB.READ(fil,rowchunk,1,buffer);
-- UTL_FILE.GET_LINE(fille,VARBUFF);
 DBMS_LOB.LOADFROMFILE(v_clob,fil,len);
-- ins_num:=dbms_lob.getchunksize(v_clob);
-- buffer:=dbms_lob.converttoclob(
/*posver:=DBMS_LOB.INSTR(v_clob,XMLROOTS_TAG)-1;
XMLVER:=dbms_lob.substr(v_clob,posver);*/
 insCtx := DBMS_XMLSave.newContext(PTABLE); -- get the context handle
insrow := dbms_xmlsave.insertXML(insCtx,v_clob);
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
indx:=1;
/*rowchunk:=len/ins_num;
for i in 1..ins_num   loop
pos:=DBMS_LOB.INSTR(v_clob,XMLROWS_TAG,1,i);
pose:=DBMS_LOB.INSTR(v_clob,XMLROWE_TAG,1,i)-50;
DATAROWB :=dbms_lob.substr(v_clob,pose,pos);
ins_num:=LENGTH(DATAROWB);
VARBUFF:=XMLVER||XMLROOTS_TAG||DATAROWB||XMLROOTE_TAG;
-- insrow := xmlgen.insertXML(''||PTABLE||'',VARBUFF);
-- v_clob:=dbms_lob.substr(v_clob,pose,);
-- len:=dbms_lob.getlength(v_clob);
len:=len-ins_num;
end loop;*/
  dbms_lob.freetemporary(v_clob);
-- len:=len-ins_num;
 
-- insrow := xmlgen.insertXML(''||PTABLE||'',UTL_RAW.CAST_TO_VARCHAR2(buffer));


-- DBMS_OUTPUT.PUT_LINE(insrow);

EXCEPTION   WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
ERR_MESS:=SQLERRM;
/*IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);   END IF;*/
end loadxml;
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question