Oracle Export/Import in XML/ASCII format

[anitak]

Hi,

I would like to know if Oracle provides any feature to import data in ASCII/XML files directly.

I would not wish to go for Oracle import in Binary format.

I found such provision provided by MYSQL.Please refer the below URL for further details.

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.htm l

I am looking for some similar feature in Oracle.

Please do let me know if any such feature exists in Oracle.

[debasisdas]

Qestion moved to Oracle Forum.

[amitpatel66]

Hi,

I would like to know if Oracle provides any feature to import data in ASCII/XML files directly.

I would not wish to go for Oracle import in Binary format.

I found such provision provided by MYSQL.Please refer the below URL for further details.

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.htm l

I am looking for some similar feature in Oracle.

Please do let me know if any such feature exists in Oracle.

SPOOL command will help you to create data file in ascii format..

[Altasen.com]

Exporting data as XML can be done as follows:
SET SERVEROUTPUT ON
DECLARE
v_file Utl_File.File_T ype;
v_xml CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML document from query.
v_xml := DBMS_XMLQuery.G etXML('SELECT table_name, tablespace_name FROM user_tables WHERE rownum < 6');

-- Output XML document to file.
v_file := Utl_File.FOpen( 'C:\Development \XML', 'test1.xml', 'w');
WHILE v_more LOOP
Utl_File.Put(v_ file, 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(SQ LERRM,1,255));
Utl_File.FClose (v_file);
END;
/

HTH
Roel Hogendoorn
http://www.altasen.com Oracle Application Books: Workflow, System Administration and BI Publisher