Oracle stored procedures generate xml files

The oracle Stored PROCEDURE generates the xml file create or replace procedure Pro_OracleToXML (personid varchar2, name varchar2, address varchar2, tel varchar2, ip varchar2, email varchar2) AS isql varchar2 (200 ); -- create a temporary table dptable varchar2 (100); -- delete the temporary table I _insert varchar2 (200); -- insert data into the temporary table tableSource CLOB; str varchar2 (500); xmlFile utl_file.file_type; tempsql varchar2 (500); -- the initial query statement ex BOOLEAN; -- whether the file has flen NUMBER; -- the file length? Bsize NUMBER; -- file size BEGIN -- initialize the temporary table creation statement isql: = 'Create global temporary table people_copy (personid VARCHAR2 (4), name varchar2 (50 ), address VARCHAR2 (200), tel VARCHAR2 (20), fax VARCHAR2 (20), email VARCHAR2 (100) on commit delete rows '; -- create a temporary table execute immediate isql; dbms_output.put_line (isql | 'execution successfully'); -- Insert the triggered data to the lele_copy table I _insert: = 'insert into lele_copy values (''' | personid | ''', ''' | name | ''', ''' | Address | ''', ''' | tel | ''', ''' | ip | ''', ''' | email | ''') '; -- execute the insert statement execute immediate I _insert; -- assign the query statement of the temporary table to the tempsql variable tempsql: = 'select * FROM lele_copy where fax = ''' | ip | '''order by personid asc '; dbms_output.put_line (tempsql); -- get content tableSource: = dbms_xmlgen.getXml (tempsql); -- determines whether the file exists utl_file.fgetattr ('People _ FILE_DIR ','/'| ip | '. xml', ex, flen, bsize); -- chr (10) is a line break, -- chr (13) is a carriage return, -- Replace (tableSource, CHR (10), ''), chr (13),''); if ex then -- the file exists and the value of tableSource is <? Xml version = "1.0"?> Replace with space tableSource: = replace (tableSource, '<? Xml version = "1.0"?> ', ''); Else -- the File Does Not Exist. Do Not replace dbms_output.put_line ('file Does Not exist'); end if; -- open the File xmlFile: = utl_file.fopen ('People _ FILE_DIR ', '/' | ip | '. xml ', 'A'); -- assign tableSource content to str string variable str: = tableSource | ''; -- remove the spaces tableSource before str: = trim (leading CHR (10) from str); -- remove the spaces tableSource: = trim (trailing CHR (10) from tableSource); dbms_output.put_line (tableSource ); -- input tableSource content to utl in xml file _ File. put_line (xmlFile, tableSource); -- closes the file utl_file.fclose (xmlFile); -- assigns the statement for deleting the temporary table to the dptable variable dptable: = 'drop table people_copy '; -- delete the temporary table execute immediate dptable; -- an EXCEPTION occurs, and the output EXCEPTION message exception when others then dbms_output.put_line (SQLERRM); END Pro_OracleToXML; create or replace trigger trigger_peopleafter insert or update on peoplereferencingfor each row declarePRAGMA AUTONOMOUS_TRANSACTION; begin d Bms_output.put_line (: new. personid | 'triggered! --- '); Pro_OracleToXML (: new. personid,: new. name,: new. address,: new. tel,: new. fax,: new. email); end;