Read XML data from a CLOB file which is already stored into Database

CREATE TABLE XML_LOAD_IN
(
FILE_ID VARCHAR2(13 BYTE),
FILE_NAME VARCHAR2(200 BYTE),
XML_CFILE CLOB,
);


ALTER TABLE XML_LOAD_IN ADD (PRIMARY KEY (FILE_ID));

--==========================================================================


CREATE TABLE BCR
(
"ItemsWithinBundleCount" NUMBER(4) NOT NULL,
"BundleTotalAmount" NUMBER(12) NOT NULL,
"MICRValidTotalAmount" NUMBER(12),
"ImagesWithinBundleCount" NUMBER(5),
"UserField" VARCHAR2(5 BYTE)
);

--==========================================================================

/*** We assume that a XML file is Stored into your table ***/

CREATE OR REPLACE procedure Dpr_Read_Xml_Data(p_file_id in varchar2)
is
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;

TYPE tab_type IS TABLE OF BCR%ROWTYPE;
t_tab tab_type := tab_type();

BEGIN

Begin

select xml_cfile
into l_clob
from xml_load_in
where file_id = p_file_id;


Exception
When no_data_found then
raise_application_error(-2001,'XML File Not Found.');
When others then null;
End;


l_parser := dbms_xmlparser.newParser;
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

dbms_xmlparser.freeParser(l_parser);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/OCE/CashLetter/Bundle/BundleControl');
/* Edit above xml node as your required */

FOR CUR_BCR IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, CUR_BCR);
t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.

dbms_xslprocessor.valueOf(l_n,'ItemsWithinBundleCount/text()' ,t_tab(t_tab.last)."ItemsWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'BundleTotalAmount/text()' ,t_tab(t_tab.last)."BundleTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'MICRValidTotalAmount/text()' ,t_tab(t_tab.last)."MICRValidTotalAmount" );
dbms_xslprocessor.valueOf(l_n,'ImagesWithinBundleCount/text()' ,t_tab(t_tab.last)."ImagesWithinBundleCount" );
dbms_xslprocessor.valueOf(l_n,'UserField/text()' ,t_tab(t_tab.last)."UserField" );
END LOOP;


FOR CUR_BCR IN t_tab.first .. t_tab.last LOOP

INSERT INTO BCR
("ItemsWithinBundleCount" ,
"BundleTotalAmount" ,
"MICRValidTotalAmount" ,
"ImagesWithinBundleCount" ,
"UserField"
)
VALUES
(t_tab(CUR_BCR)."ItemsWithinBundleCount" ,
t_tab(CUR_BCR)."BundleTotalAmount" ,
t_tab(CUR_BCR)."MICRValidTotalAmount" ,
t_tab(CUR_BCR)."ImagesWithinBundleCount" ,
t_tab(CUR_BCR)."UserField"
);

END LOOP;

COMMIT;

dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;
/

Show Database Version

Create or Replace Function Fnc_Database_version Return char IS

v_data varchar2(1000);
ShowData varchar2(4000);
p_data varchar2(4000);

Begin

For i in (select banner
from sys.v_$version)
Loop
v_data := i.banner;
ShowData:= ShowData||v_data||chr(10);
End Loop;

p_data := 'Connected to :'||chr(10)||ShowData;
p_data := substr(p_data,1,length(p_data)-1);

Return p_data;

Exception
When others then null;
End;

Manual Ordering Block Data

PROCEDURE Prc_ordering_REF ( pBlock VARCHAR2, pClause VARCHAR2, pExec VARCHAR2 default 'NO') IS
vOrder_by VARCHAR2(5);
V_ORDER VARCHAR2(500);
begin
-- Checking whether pBlock is DB Block or not
if get_block_property(pBlock, query_data_source_type) = 'TABLE' then

-- Finding out " ASC" or " DESC" from the existing "null" or "1 ASC" order clause.
-- Set " DESC" if null


vOrder_by := nvl(substr(get_block_property(pBlock, order_by),2), ' a');

-- Assigning vice-versa
if Upper(vOrder_by) = ' A' then
vOrder_by := ' ASC';
elsif Upper(vOrder_by) = ' ASC' then
vOrder_by := ' DESC';
else
vOrder_by := ' ASC';
end if;

-- Setting order by clause to the supplied block
set_block_property (pBlock, order_by, pClause||vOrder_by);

if upper(pExec) = 'YES' then
go_block(pBlock);
:system.message_level := 5;
execute_query;
:system.message_level := 0;
end if;
set_block_property(pBlock, order_by, '1'||vOrder_by);
end if;
end;

Get Form Path Dynamically

function FORM_PATH return char is
vPath varchar2(200);
vPath_Len pls_integer;
vPath_Sep char(1);
begin
vPath := get_application_property(current_form);

if get_application_property(operating_system) in ('MSWINDOWS', 'MSWINDOWS32', 'WIN32COMMON') then
vPath_Sep := '\';
else
vPath_Sep := '/';
end if;

vPath_len := instr(vPath,vPath_Sep,-1);

if vPath_Len > 0 then
vPath := substr(vPath,1,vPath_Len);
else
vPath_Len := null;
end if;
return vPath;
end;

Create A Stop Watch with Sql

function fnc_hh_mm_ss (p_ss in number) return varchar2
is
v_ss number;
v_mm number;
v_hh number;
v_temp number;
show_hh varchar2(5);
show_mm varchar2(5);
show_ss varchar2(5);
p_show_time varchar2 (500);

begin

select decode (length(floor(floor(p_ss/60)/60)), 1 , ('0'||floor(floor(p_ss/60)/60)) , (floor(floor(p_ss/60)/60)))||':'||
decode (length(mod(floor(p_ss/60),60)) , 1 , ('0'||mod(floor(p_ss/60),60)) , (mod(floor(p_ss/60),60))) ||':'||
decode (length(mod(p_ss,60)) , 1 , ('0'||mod(p_ss,60)) , (mod(p_ss,60)))
into p_show_time
from dual;

return (p_show_time);

exception
when others then
p_show_time:='00:00:00';
return (p_show_time);
end;

WebUtil Package For WebUtil Configuration

CREATE OR REPLACE PACKAGE WEBUTIL_DB AUTHID CURRENT_USER AS

/*********************************************************************************\
* WebUtil_DB - Database functions used by the WebUtil_File_Transfer
* Package. These functions allow reading and writing direct
* to the specified BLOB in the database.
* The functions should not be called externally from WebUtil
*********************************************************************************
* Version 1.0.0
*********************************************************************************
* Change History
* DRMILLS 11/JAN/2003 - Creation
*
\*********************************************************************************/

FUNCTION OpenBlob(blobTable in VARCHAR2,
blobColumn in VARCHAR2,
blobWhere in VARCHAR2,
openMode in VARCHAR2,
chunkSize in PLS_INTEGER default null) return BOOLEAN;

FUNCTION CloseBlob(checksum in PLS_INTEGER) return BOOLEAN;

PROCEDURE WriteData(data in VARCHAR2);

FUNCTION ReadData return VARCHAR;

FUNCTION GetLastError return PLS_INTEGER;

FUNCTION GetSourceLength return PLS_INTEGER;

FUNCTION GetSourceChunks return PLS_INTEGER;

END WEBUTIL_DB;
/



CREATE OR REPLACE PACKAGE BODY WEBUTIL_DB AS
m_binaryData BLOB;
m_blobTable VARCHAR2(60);
m_blobColumn VARCHAR2(60);
m_blobWhere VARCHAR2(1024);
m_mode CHAR(1);
m_lastError PLS_INTEGER := 0;
m_sourceLength PLS_INTEGER := 0;
m_bytesRead PLS_INTEGER := 0;
MAX_READ_BYTES PLS_INTEGER := 4096;

-- internal Program Units
PROCEDURE Reset;

PROCEDURE Reset is
BEGIN
m_blobTable := null;
m_blobColumn := null;
m_blobWhere := null;
m_mode := null;
m_lastError := 0;
m_sourceLength := 0;
m_bytesRead := 0;
END Reset;


FUNCTION OpenBlob(blobTable in VARCHAR2, blobColumn in VARCHAR2, blobWhere in VARCHAR2, openMode in VARCHAR2, chunkSize PLS_INTEGER default null) return BOOLEAN is
result BOOLEAN := false;
stmtFetch VARCHAR2(2000);
hit PLS_INTEGER;
BEGIN
-- New transaction clean up
reset;

m_blobTable := blobTable;
m_blobColumn := blobColumn;
m_blobWhere := blobWhere;
m_mode := upper(openMode);

if chunkSize is not null then
if chunkSize > 16384 then
MAX_READ_BYTES := 16384;
else
MAX_READ_BYTES := chunkSize;
end if;
end if;

-- check the target row exists
stmtFetch := 'select count(*) from '||m_blobTable||' where '||m_blobWhere;
EXECUTE IMMEDIATE stmtFetch into hit;

if hit = 1 then
if m_mode = 'W' then
DBMS_LOB.CREATETEMPORARY(m_binaryData,false);
DBMS_LOB.OPEN(m_binaryData,DBMS_LOB.LOB_READWRITE);
m_sourceLength := 0;
result := true;
elsif m_mode = 'R' then
stmtFetch := 'select '||m_blobColumn||' from '||m_blobTable||' where '||m_blobWhere;
EXECUTE IMMEDIATE stmtFetch into m_binaryData;
if m_binaryData is not null then
m_sourceLength := dbms_lob.getlength(m_binaryData);
if m_sourceLength > 0 then
result := true;
else
m_lastError := 110;
end if;
else
m_lastError := 111;
end if;
else
m_lastError := 112;
end if; -- mode
else
-- too many rows
m_lastError := 113;
end if; -- Hit
return result;
END OpenBlob;

FUNCTION CloseBlob(checksum in PLS_INTEGER) return BOOLEAN is
sourceBlob BLOB;
stmtFetch VARCHAR2(2000);
stmtInit VARCHAR2(2000);
result BOOLEAN := false;
BEGIN
if m_mode = 'W' then
m_sourceLength := DBMS_LOB.GETLENGTH(m_binaryData);
end if;

-- checksum
if checksum = m_sourceLength then
if m_mode = 'W' then
-- get the locator to the table blob
stmtFetch := 'select '||m_blobColumn||' from '||m_blobTable||' where '||m_blobWhere||' for update';
EXECUTE IMMEDIATE stmtFetch into sourceBlob;

-- Check the blob has been initialised
-- and if it's not empty clear it out
if sourceBlob is null then
stmtInit := 'update '||m_blobTable||' set '||m_blobColumn||'=EMPTY_BLOB() where '||m_blobWhere;
EXECUTE IMMEDIATE stmtInit;
EXECUTE IMMEDIATE stmtFetch into sourceBlob;
elsif dbms_lob.getlength(sourceBlob) > 0 then
dbms_lob.TRIM(sourceBlob,0);
end if;
-- now replace the table data with the temp BLOB
DBMS_LOB.APPEND(sourceBlob,m_binaryData);
DBMS_LOB.CLOSE(m_binaryData);
result := true;
else
-- todo
null;
end if; --mode
else
m_lastError := 115;
end if; --checksum
return result;
END CloseBlob;

PROCEDURE WriteData(data in VARCHAR2) is
rawData raw(16384);
BEGIN
rawData := utl_encode.BASE64_DECODE(utl_raw.CAST_TO_RAW(data));
dbms_lob.WRITEAPPEND(m_binaryData, utl_raw.LENGTH(rawData), rawData);
END WriteData;


FUNCTION ReadData return VARCHAR is
rawData RAW(16384);
bytesToRead PLS_INTEGER;
BEGIN
bytesToRead := (m_sourceLength - m_bytesRead);
if bytesToRead > MAX_READ_BYTES then
bytesToRead := MAX_READ_BYTES;
end if;
DBMS_LOB.READ(m_binaryData, bytesToRead, (m_bytesRead + 1), rawData);
m_bytesRead := m_bytesRead + bytesToRead;
return UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(rawData));
END ReadData;

FUNCTION GetLastError return PLS_INTEGER is
BEGIN
return m_lastError;
END GetLastError;


FUNCTION GetSourceLength return PLS_INTEGER is
BEGIN
return m_sourceLength;
END GetSourceLength;

FUNCTION GetSourceChunks return PLS_INTEGER is
chunks PLS_INTEGER;
BEGIN
chunks := floor(m_sourceLength/MAX_READ_BYTES);
if mod(m_sourceLength,MAX_READ_BYTES) > 0 then
chunks := chunks+1;
end if;
return chunks;
END GetSourceChunks;

END;
/

Webutil Configuration for Developer10g

It is assumed that You install developer suite in drive D:\ and have webutil tools;


step-1
-----

paste webutil folder in the following path
D:\DevSuiteHome\forms90


step-2
------

Paste webutil.cfg,webutiljini.htm in the following path
D:\DevSuiteHome\forms90\server


step-3
------

Paste additional jar file in the following path
D:\DevSuiteHome\forms90\java


Step-4
------

open deafult.env file (path :>> D:\DevSuiteHome\forms90\server)

go to last line of file ( the line is:>> CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar)
Modify this line as your required jar files
:>> example
CLASSPATH=D:\DevSuiteHome\j2ee\OC4J_BI_Forms\applications\forms90app\forms90web\WEB-INF\lib\f90srv.jar;D:\DevSuiteHome\jlib\repository.jar;D:\DevSuiteHome\jlib\ldapjclnt9.jar;D:\DevSuiteHome\jlib\debugger.jar;D:\DevSuiteHome\jlib\ewt3.jar;D:\DevSuiteHome\jlib\share.jar;D:\DevSuiteHome\jlib\utj90.jar;D:\DevSuiteHome\jlib\zrclient.jar;D:\DevSuiteHome\reports\jlib\rwrun.jar;D:\DevSuiteHome\forms90\java\getclientinfo.jar;D:\DevSuiteHome\forms90\java\frmwebutil.jar;D:\DevSuiteHome\forms90\java\GetImageFileName.jar;D:\DevSuiteHome\forms90\java\images.jar;D:\DevSuiteHome\forms90\java\infobutton.jar;D:\DevSuiteHome\forms90\java\jacob.jar;D:\DevSuiteHome\forms90\java\rolloverbutton.jar


add following line in the end of file

#Webutil Config
WEBUTIL_CONFIG=D:\DevSuiteHome\forms90\server\webutil.cfg


Step-5
------

open forms90.conf file (path :>> D:\DevSuiteHome\forms90\server)

go to line where the following code already written
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"

add following line after this
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"

:>> example
# Virtual path for runform.htm (used to run a form for testing purposes)
AliasMatch ^/forms90/html/(..*) "D:\DevSuiteHome/tools/web90/html/$1"
AliasMatch ^/forms90/webutil/(..*) "D:\DevSuiteHome/forms90/webutil/$1"



Step-6a
------
1. Edit D:\DevSuiteHome\forms90\webutil\sign_webutil.bat file
as:
1. Go to Line
ECHO Generating a self signing certificate for key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........
2. Go to Line
ECHO Signing %1 using key=%JAR_KEY%...
Next Line
edit Line in replace of previous address
D:\DevSuiteHome\jdk.........

Step-6b
Sign additional jar file in the following way........

1.open cmd window
2.type d: and press enter
3.type cd DevSuiteHome\forms90\webutil and press enter
4.type sign_webutil D:\DevSuiteHome\forms90\java\rolloverbutton.jar and press enter

repeat line no. 4 as your required jar files



Step-7
------

Open formsweb.cfg file (path :>> D:\DevSuiteHome\forms90\server)
go to the end of file and then type the following line

[test_config]
form=D:\test\image_browser.fmx usesdi=yes
userid=user_id/pass@database_string
width=100%
height=100%
colorscheme=blue
pageTitle=Abc
imagebase=codebase
archive_jini=f90all_jinit.jar,rolloverbutton.jar,GetImageFileName.jar
webUtilArchive=frmwebutil.jar,jacob.jar
baseHTMLjinitiator=webutiljini.htm
splashscreen=abc_splash.gif
logo=abc_logo.gif
separateFrame=false

---End Configuration---


Now Call Your Configuration From Internet Explorer or Any Other Browser as below:

Ex:http://computer_name(or Ip_address):8889/forms90/f90servlet?config=test_config

Ex:http://abc_computer:8889/forms90/f90servlet?config=test_config

How to install a report server & configuration in Oracle Developer10g…?

Step-01:
========
Open your cmd command prompt & paste the following command. Edit report_server_name.
[Make sure Developer10g install of your computer with named

DevSuiteHome

.]

rwserver -install server = rep_server_name

Step-02:
========
Open cgicmd.dat (with wordpad or Notepad) file from
.../DevSuiteHome/reports/conf/ folder & write the following line into the last line of that file.

report_key : user_id=user_name/pass@database server=rep_server_name %*

Email Id Verification

CREATE OR REPLACE FUNCTION xx_check_email (l_user_name IN VARCHAR2)
RETURN VARCHAR2
IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := INSTR (l_user_name, '.');
l_at_pos := INSTR (l_user_name, '@');
l_str_length := LENGTH (l_user_name);

IF ( (l_dot_pos = 0)
OR (l_at_pos = 0)
OR (l_dot_pos = l_at_pos + 1)
OR (l_at_pos = 1)
OR (l_at_pos = l_str_length)
OR (l_dot_pos = l_str_length)
)
THEN
RETURN 'FAILURE';
END IF;

IF INSTR (SUBSTR (l_user_name, l_at_pos), '.') = 0
THEN
RETURN 'FAILURE';
END IF;

RETURN 'SUCCESS';
END xx_check_email;


select INSTR (:l_user_name, '.'),
INSTR (:l_user_name, '@'),
LENGTH (:l_user_name)
from dual;


begin
if owa_pattern.match(p_email,'^\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
'@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
dbms_output.put_line('email válido : '||p_email);
return true;
else
dbms_output.put_line('email inválido : '||p_email);
return false;
end if;
end f_valida_email;

xml to Oracle

-- As SYS
CREATE DIRECTORY xml_dir AS 'c:\';
GRANT READ ON DIRECTORY xml_dir TO ;

-- As schema owner
CREATE TABLE EMP (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

The source of the data is the emp.xml document. Extracting the data from the document involves several steps. First the XML document must be parsed and a DOMDocument created from it. Once the DOMDocument is created the parser is no longer needed so it's resources can be freed:
l_parser := dbms_xmlparser.newParser;

dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

dbms_xmlparser.freeParser(l_parser);
Next the XPATH syntax is used to get a DOMNodeList containing all the EMP nodes:
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');
Once we have the DOMNodeList we can loop through it getting the values for each node in turn. The values are returned using the XPATH sytax and placed in a table collection:
l_n := dbms_xmldom.item(l_nl, cur_emp);
dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
With all the data retrieved into the table collection the inserts can be performed. Putting it all together we get:

DECLARE
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
l_temp VARCHAR2(1000);

TYPE tab_type IS TABLE OF emp%ROWTYPE;
t_tab tab_type := tab_type();
BEGIN

l_bfile := BFileName('XML_DIR', 'emp.xml');
dbms_lob.createtemporary(l_clob, cache=>FALSE);
dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
dbms_lob.loadFromFile(dest_lob => l_clob,
src_lob => l_bfile,
amount => dbms_lob.getLength(l_bfile));
dbms_lob.close(l_bfile);

-- make sure implicit date conversions are performed correctly
dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

-- Create a parser.
l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);

-- Free resources associated with the CLOB and Parser now they are no longer needed.
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);

-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

-- Loop through the list and create a new record in a tble collection
-- for each EMP record.
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
l_n := dbms_xmldom.item(l_nl, cur_emp);

t_tab.extend;

-- Use XPATH syntax to assign values to he elements of the collection.
dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
END LOOP;

-- Insert data into the real EMP table from the table collection.
-- Form better performance multiple collections should be used to allow
-- bulk binding using the FORALL construct but this would make the code
-- too long-winded for this example.
FOR cur_emp IN t_tab.first .. t_tab.last LOOP
INSERT INTO emp
(empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
VALUES
(t_tab(cur_emp).empno,
t_tab(cur_emp).ename,
t_tab(cur_emp).job,
t_tab(cur_emp).mgr,
t_tab(cur_emp).hiredate,
t_tab(cur_emp).sal,
t_tab(cur_emp).comm,
t_tab(cur_emp).deptno);
END LOOP;

COMMIT;

-- Free any resources associated with the document now it
-- is no longer needed.
dbms_xmldom.freeDocument(l_doc);

EXCEPTION
WHEN OTHERS THEN
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;

--===================End===============