OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » XML to an OS file

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

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

Previous Topic:	Problem with adf table
Next Topic:	ADF - Jasper

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 06:26:00 CDT 2024