Home » Developer & Programmer » JDeveloper, Java & XML » XML file from Blob datatype table
XML file from Blob datatype table [message #261905] Fri, 24 August 2007 00:41
adroit.ramesh
Messages: 14
Registered: November 2005
Junior Member
Hi All,

I have requirement to generate the XML file from Blob datatype table.

Step1:Table Creation
CREATE TABLE lobtable(employee_id NUMBER,employee_name VARCHAR2( 20),photo BLOB DEFAULT EMPTY_BLOB ());

Step2:Insertion
DECLARE
f_lob BFILE;
b_lob BLOB;
BEGIN
INSERT INTO lobtable
(employee_id, employee_name, photo
)
VALUES (1, 'log.jpg', EMPTY_BLOB ()
)
RETURN photo
INTO b_lob;

f_lob := BFILENAME ('EXAMPLE_LOB_DIR', 'log.jpg');
DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));
DBMS_LOB.fileclose (f_lob);
COMMIT;
END;

Step3:Table Select
SELECT * FROM LOBTABLE

Step4:Xml Creation
DECLARE
v_file UTL_FILE.file_type;
v_xml CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML document from query.
v_xml := SYS.DBMS_XMLQUERY.getxml ('SELECT * FROM LOBTABLE;');
-- Output XML document to file.
v_file := UTL_FILE.fopen ('/usr/tmp', 'test.xml', 'w');

WHILE v_more
LOOP
UTL_FILE.put (v_file, SUBSTR (v_xml, 1, 32767));

--DBMS_OUTPUT.PUT_LINE(Substr(v_xml, 1, 32767));
IF LENGTH (v_xml) > 32767
THEN
v_xml := SUBSTR (v_xml, 32768);
ELSE
v_more := FALSE;
END IF;
END LOOP;

UTL_FILE.fclose (v_file);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 255));
UTL_FILE.fclose (v_file);
END;

Xml File Contions the following Message.
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00911: invalid character
</ERROR>


Please provide me how I can generate the XML file.

Regards
RameshKumar S

Previous Topic: use of javascript:window.open to call reports
Next Topic: use jar file
Goto Forum:
  


Current Time: Tue Dec 06 08:23:26 CST 2016

Total time taken to generate the page: 0.07506 seconds