Home » Developer & Programmer » JDeveloper, Java & XML » How to Generate Xml file from Oracle
How to Generate Xml file from Oracle [message #92533] Fri, 03 September 2004 15:04 Go to next message
Shankar Chowhan
Messages: 1
Registered: September 2004
Junior Member
Hi,

 

Could any one tell me how to generate a XML file from Oracle? If I have to pass as an output in a procedure or directly create a file?

 

Thx,

 

Shankar

Report message to a moderator

Re: How to Generate Xml file from Oracle [message #92538 is a reply to message #92533] Mon, 06 September 2004 14:22 Go to previous messageGo to next message
Magnar Johannessen
Messages: 12
Registered: February 2004
Junior Member
Try this:

CREATE OR REPLACE PROCEDURE xml_emp
(x_xml OUT CLOB)
IS

BEGIN
x_xml := dbms_xmlquery.getxml('SELECT * FROM emp');
END;

OR

SELECT dbms_xmlquery.getXML('SELECT * FROM emp') FROM dual;

Report message to a moderator

Re: How to Generate Xml file from Oracle [message #92539 is a reply to message #92533] Mon, 06 September 2004 15:05 Go to previous messageGo to next message
Tak Tang
Messages: 142
Registered: May 2004
Senior Member
One method would be to use the XML functions XMLElement(); XMLAttributes(); XMLForest() to construct an XMLType object; convert it to a string using '.getStringVal()'; then save this to a file using utl_file.

These are covered in Chapter 10 of 'XML Database Developer’s Guide - Oracle XML DB', which also covers other ways of generating XML, such as DBMS_XMLGEN.

Example use of XMLElement(); XMLAttributes() and XMLForest()
http://www.svgopen.org/2003/papers/VisualizingDatabaseManagementwithSVG/index.html

Report message to a moderator

Re: How to Generate Xml file from Oracle [message #92719 is a reply to message #92539] Mon, 20 December 2004 01:14 Go to previous messageGo to next message
parveen jain
Messages: 1
Registered: December 2004
Junior Member
plz send the how to genarte xml from pl/sql

Report message to a moderator

Re: How to Generate Xml file from Oracle [message #112666 is a reply to message #92533] Mon, 28 March 2005 11:35 Go to previous messageGo to next message
bsc7080oqc
Messages: 2
Registered: March 2005
Location: Shelbyville, KY
Junior Member
I created the following to generate an XML file from a provided query which then writes the file out. It can generate small or large all depending on your needs.

Also you may want to change the header components to suit your needs :

<TRANSACTION_SET><TRANSACTIONS><TRANSACTION>

Each row is encapsulated by <TROW>.



FUNCTION dump_xml (
p_query IN VARCHAR2
, p_dir IN VARCHAR2
, p_filename IN VARCHAR2
, p_max_linesize IN NUMBER DEFAULT 32000
, p_mode IN VARCHAR2 DEFAULT 'w'
)
RETURN NUMBER
IS
l_output UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_colcnt_array humcust.hum_toolbox.vc80_table;
l_colcnt_empty_array humcust.hum_toolbox.vc80_table;
l_cnt NUMBER DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_line LONG;
l_desctbl DBMS_SQL.desc_tab;
v_sqlerrm VARCHAR2 (32000);
l_mode CHAR (1) := 'w';
BEGIN
l_colcnt_array := l_colcnt_empty_array;

IF p_mode NOT IN ('w', 'a')
THEN
l_mode := 'w';
ELSE
l_mode := p_mode;
END IF;

l_output :=
UTL_FILE.fopen (p_dir
, p_filename
, l_mode
, p_max_linesize
);
DBMS_SQL.parse (l_thecursor
, p_query
, DBMS_SQL.native
);
DBMS_SQL.describe_columns (l_thecursor
, l_colcnt
, l_desctbl
);

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.define_column (l_thecursor
, i
, l_columnvalue
, 4000
);

IF (l_desctbl (i).col_type = 2) /* number type */
THEN
l_desctbl (i).col_max_len := l_desctbl (i).col_precision
+ 2;
ELSIF (l_desctbl (i).col_type = 12) /* date type */
THEN
/* length of my date format */
l_desctbl (i).col_max_len := 20;
ELSIF (l_desctbl (i).col_type = Cool /* LONG type */
THEN
l_desctbl (i).col_max_len := 2000;
END IF;

l_colcnt_array (i) := l_desctbl (i).col_name;
END LOOP;

l_status := DBMS_SQL.EXECUTE (l_thecursor);
UTL_FILE.put (l_output, '<TRANSACTION_SET><TRANSACTIONS><TRANSACTION>');

LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
l_line := NULL;
UTL_FILE.put (l_output, '<TROW>');

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.column_value (l_thecursor
, i
, l_columnvalue
);
UTL_FILE.put (l_output
, '<'
|| l_colcnt_array (i)
|| '>'
|| l_columnvalue
|| '</'
|| l_colcnt_array (i)
|| '>');
END LOOP;

UTL_FILE.put (l_output, '</TROW>');
UTL_FILE.new_line (l_output);
l_cnt := l_cnt
+ 1;
END LOOP;

UTL_FILE.put (l_output
, '</TRANSACTION></TRANSACTIONS></TRANSACTION_SET>');
DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);
RETURN l_cnt;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO_DATA_FOUND');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_PATH');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.READ_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.WRITE_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_mode
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MODE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_filehandle
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_FILEHANDLE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_OPERATION');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.internal_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INTERNAL_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_maxlinesize
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MAXLINESIZE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.VALUE_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN OTHERS
THEN
hum_do.default_exception ('ERROR in dump_csv : ');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
END dump_xml;

Report message to a moderator

Re: How to Generate Xml file from Oracle [message #116190 is a reply to message #92538] Mon, 18 April 2005 09:15 Go to previous message
mmanza
Messages: 1
Registered: April 2005
Junior Member
When I use the first option in SQL, I've not problem:

SELECT dbms_xmlquery.getXML('SELECT * FROM emp') FROM dual;


But if I try with:

x_xml := dbms_xmlquery.getxml('SELECT * FROM emp');

in Forms, my session is closed with:

PDE-PER001 Internal error (de pxc 3)

Could you help me?

Report message to a moderator

Previous Topic: Writing a stored procedure with java.
Next Topic: problem with a sequence visibility...
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 13:54:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.