Re: Oracle trigger and XML file

From: patil <patil33_at_hotmail.com>
Date: 22 Feb 2002 12:37:50 -0800
Message-ID: <76f660bd.0202221237.2f0d3831@posting.google.com>

There is a tool available:
http://www.soft-r-us.com/dbtoxml.asp
that allow you to generate Oracle PL/SQL triggers in this format:

CREATE OR REPLACE TRIGGER trgTUSER_INSERT AFTER INSERT ON TUSER FOR EACH ROW
DECLARE

	iFile_Handle UTL_FILE.FILE_TYPE; 
	i BINARY_INTEGER;

BEGIN
  i := DBMS_RANDOM.RANDOM;

  --Update with valid exchange folder
  iFile_Handle := UTL_FILE.fopen('c:\temp\', 'TUSER'|| to_char(i) || '.xml','w',32000);
  UTL_FILE.put(iFile_Handle,'<?xml
version=""1.0""?><TUSERDOC><TUSER>');

  UTL_FILE.put(iFile_Handle,'<USER_ID>'||to_char(:new.user_id)||'</USER_ID>');
  UTL_FILE.put(iFile_Handle,'<FIRST_NAME>'||:new.first_name||'</FIRST_NAME>');
  UTL_FILE.put(iFile_Handle,'<LAST_NAME>'||:new.last_name||'</LAST_NAME>');
  UTL_FILE.put(iFile_Handle,'<TITLE>'||:new.title||'</TITLE>');
  UTL_FILE.put(iFile_Handle,'<EMAIL>'||:new.email||'</EMAIL>');
  UTL_FILE.put(iFile_Handle,'<PHONE>'||:new.phone||'</PHONE>');
  UTL_FILE.put(iFile_Handle,'<FAX>'||:new.fax||'</FAX>');
  UTL_FILE.put(iFile_Handle,'<LOGIN>'||:new.login||'</LOGIN>');
  UTL_FILE.put(iFile_Handle,'<PASSWORD>'||:new.password||'</PASSWORD>');
  UTL_FILE.put(iFile_Handle,'<DEPARTMENT_ID>'||to_char(:new.department_id)||'</DEPARTMENT_ID>');
  UTL_FILE.put(iFile_Handle,'<ROLE_ID>'||to_char(:new.role_id)||'</ROLE_ID>');
  UTL_FILE.put(iFile_Handle,'<ACTIVE>'||:new.active||'</ACTIVE>');
  UTL_FILE.put(iFile_Handle,'<CREATE_BY>'||to_char(:new.create_by)||'</CREATE_BY>');
  UTL_FILE.put(iFile_Handle,'<CREATE_TIME>'||to_char(:new.create_time,'mm/dd/yyyy')||'</CREATE_TIME>');
  UTL_FILE.put(iFile_Handle,'<LAST_CHANGE_BY>'||to_char(:new.last_change_by)||'</LAST_CHANGE_BY>');
  UTL_FILE.put(iFile_Handle,'<LAST_CHANGE_TIME>'||to_char(:new.last_change_time,'mm/dd/yyyy')||'</LAST_CHANGE_TIME>');
  UTL_FILE.put(iFile_Handle,'<PWD_REMINDER>'||:new.pwd_reminder||'</PWD_REMINDER>');

  UTL_FILE.put(iFile_Handle,'</TUSER></TUSERDOC>');   UTL_FILE.fclose(iFile_Handle);   

  EXCEPTION
  When NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_line('no_data_found');     UTL_FILE.FCLOSE(iFile_Handle);
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_line('UTL_FILE.INVALID_PATH');     UTL_FILE.FCLOSE(iFile_Handle);
  WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_line('UTL_FILE.READ_ERROR');     UTL_FILE.FCLOSE(iFile_Handle);
  WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_line('UTL_FILE.WRITE_ERROR');     UTL_FILE.FCLOSE(iFile_Handle);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_line('UTL_FILE.OTHER_ERROR');     UTL_FILE.FCLOSE(iFile_Handle);
END;   Thanks,
Raj.

pr_kale_at_yahoo.com (Prasad) wrote in message news:<9e62bcff.0202040640.2f273698_at_posting.google.com>...
> I have a need to create a XML file from table data. Each time data in
> a table changes, XML file reflecting the table data should be
> overwritten. For this i am hopeful of using oracle triggers. Is there
> a way to create XML files from PL/SQL?.
>
> Any ideas are welcome.
>
> Thank you.
Received on Fri Feb 22 2002 - 14:37:50 CST