Home » Developer & Programmer » JDeveloper, Java & XML » Export XML from oracle table
Export XML from oracle table [message #91898] Wed, 16 July 2003 03:22 Go to next message
Micheal
Messages: 17
Registered: July 2003
Junior Member
hi,
I am using oracle 8.1.7 database on windows2000 server and i have installed XSU in order to retrieve data from tables and transform them to XML document.
i am using the following code:

declare

xmlString CLOB := null;
amount integer:= 1000;
position integer := 1;
charString varchar2(1000);
fileHandle UTL_FILE.FILE_TYPE;

begin

--we want the result document root to be "Bonus"
--to follow our DTD structure
xmlgen.setRowsetTag('Bonus');

--we want the row element to be named "mappings" to follow our DTD structure
xmlgen.setRowTag('mappings');

--open the file in "write" mode
fileHandle := utl_file.fopen('E:oracleora81bin','XML_For_Bonus.XML', 'w');

--set the ERROR tag to be ERROR_RESULTS
xmlgen.setErrorTag('ERROR_RESULT');

--set the id attribute in the ROW element to be Record - so that it shows the number
--of records fetched
xmlgen.setRowIdAttrName('Record');

--do not use the null indicator to indicate nullness
xmlgen.useNullAttributeIndicator(false);

--attach the stylesheet to the result document
--xmlgen.setStyleSheet('XSL_FOR_Bonus.XSL');

--This gets the XML out - the 0 indicates no DTD in the generated XML document
--a value of 1 will provide a DTD description in the XML document
xmlString := xmlgen.getXML('select * from Scott.Bonus',0);

--Now open the lob data..
dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);
loop
-- read the lob data
dbms_lob.read(xmlString,amount,position,charString);
utl_file.put_line(fileHandle, charString);
position := position + amount;
end loop;
exception
when no_data_found then
-- end of fetch, free the lob
dbms_lob.close(xmlString);
dbms_lob.freetemporary(xmlString);
xmlgen.resetOptions;
utl_file.fclose(fileHandle);
when others then
xmlgen.resetOptions;
end;
Once i run the code i get a successfull message but there is no written file as output.Can someone help me please?
Re: Export XML from oracle table [message #91899 is a reply to message #91898] Thu, 17 July 2003 00:21 Go to previous message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
It might be due to your exception handling. I think your program might encounter an error without you knowing about it.

Look at the example below:
SQL> Begin
  2    Raise NO_DATA_FOUND;
  3  Exception
  4    When NO_DATA_FOUND Then
  5       Null; -- here you'd do some treatment.
  6  End;
  7  /

PL/SQL procedure successfully completed.
I know the no_data_found is being raised, and still it seems fine. Therefor I'd suggest to at least do some dbms_output in your exception handling, or re-raise the exception. Most definitely in your when others part because there's no way in telling what exception you might have encountered:
SQL> ed
Wrote file afiedt.buf

  1  Begin
  2    Raise NO_DATA_FOUND;
  3  Exception
  4    When NO_DATA_FOUND Then
  5       Null;  -- here you'd do some treatment.
  6       RAISE; -- After processing RAISE the exception again, we want to know about it!
  7* End;
SQL> /
Begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
HTH,
MHE
Previous Topic: How can an Oracle9i Lite DB receive connections via JDBC?
Next Topic: Transforming XML into another XML document !!!
Goto Forum:
  


Current Time: Fri Mar 29 00:49:18 CDT 2024