| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
