OraFAQ Forum: Server Utilities » XML EXPORT FROM ORACLE TABLE!

Home » RDBMS Server » Server Utilities » XML EXPORT FROM ORACLE TABLE!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML EXPORT FROM ORACLE TABLE! [message #72552]

Wed, 16 July 2003 03:19

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?

Report message to a moderator

Re: XML EXPORT FROM ORACLE TABLE! [message #72553 is a reply to message #72552]

Wed, 16 July 2003 05:36

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

is the utl_file properly configured?
did u test it with any simple read/write operations?
add the following code to yours, which will help to identifiy any erros in utl

EXCEPTION
        WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('no_data_found');
	WHEN UTL_FILE.INVALID_PATH THEN
		DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
	WHEN UTL_FILE.WRITE_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
      	WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('other stuff');

Report message to a moderator

Re: XML EXPORT FROM ORACLE TABLE! [message #72556 is a reply to message #72553]

Wed, 16 July 2003 07:34

Micheal
Messages: 17
Registered: July 2003

Junior Member

we opened the init.ora file and we added
UTL_FILE_DIR = *
however, we are still getting an invalid path exception;
What do you think i should do?
And how to set the UTL_FILE_DIR so it can access and write/read my files??
Thank you for your help...

Report message to a moderator