| Export XML from oracle table [message #91898] |
Wed, 16 July 2003 03:22  |
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  |
 |
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. 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
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
