Home » Developer & Programmer » JDeveloper, Java & XML » XML to an OS file
XML to an OS file [message #244374] |
Tue, 12 June 2007 08:55 |
marks20101
Messages: 74 Registered: May 2005
|
Member |
|
|
I need the output of the following query into an OS file (.xml).
run in emp:
set long 32000
SELECT XMLElement("SourceGroup", XMLAttributes(ename AS Name, '-1' as RolloverType, 'WMENC_SOURCEGROUP_AUTOROLLOVER_TO_NEXT' as RolloverSourceGroup),
XMLElement("Source", XMLAttributes('WMENC_AUDIO' AS Type, 'file' as Scheme, job as InputSource)),
XMLElement("Source", XMLAttributes('WMENC_SCRIPT' AS Type, 'userscript' as Scheme)),
XMLElement("EncoderProfile", XMLAttributes('Multiple bit rates audio (CBR) / Script High' AS id)))
FROM emp;
model block I started with:
DECLARE
-- Data Variables
v_xml XMLTYPE;
v_blob BLOB;
v_data_length NUMBER;
-- Loop Control Variables
v_offset NUMBER DEFAULT 1;
v_chunk CONSTANT NUMBER DEFAULT 4000;
-- UTL_FILE variables
fh UTL_FILE.file_type;
BEGIN
SELECT XMLElement("SourceGroup", XMLAttributes(ename AS Name, '-1' as RolloverType, 'WMENC_SOURCEGROUP_AUTOROLLOVER_TO_NEXT' as RolloverSourceGroup),
XMLElement("Source", XMLAttributes('WMENC_AUDIO' AS Type, 'file' as Scheme, job as InputSource)),
XMLElement("Source", XMLAttributes('WMENC_SCRIPT' AS Type, 'userscript' as Scheme)),
XMLElement("EncoderProfile", XMLAttributes('Multiple bit rates audio (CBR) / Script High' AS id)))
into v_xml
FROM emp;
-- Turn the XML into a BLOB to bypass any 4k and 32k issues
v_blob := v_xml.getblobval (1);
v_data_length := DBMS_LOB.getlength (v_blob);
-- Open the file
fh := UTL_FILE.fopen ('IO', 'myxml.xml', 'wb', v_chunk);
-- Da loop de loop
LOOP
-- Exit when our file offset is bigger than our file
EXIT WHEN v_offset > v_data_length;
-- Write the output chunk by chunk
UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
TRUE);
-- Increment the offset by the amount written
v_offset := v_offset + v_chunk;
END LOOP;
-- Close the file and say byebye
UTL_FILE.fclose (fh);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- We won't write any data, or even open the file,
-- if the query return no rows
NULL;
END;
I guess the problem I'm having is the v_xml variable that will hold the resulting query. I get this, ORA-01422 error exact fetch returns more than requested number of rows. Error for implicit cursor.
Thanks,
Mark S.
[Updated on: Wed, 13 June 2007 07:11] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:40:09 CDT 2024
|