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.html

I am looking for some similar feature in Oracle.

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

Oct 25 '06 #1

debasisdas

Qestion moved to Oracle Forum.

Nov 3 '07 #2

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.html

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..

Nov 4 '07 #3

Altasen.com

Exporting data as XML can be done as follows:
SET SERVEROUTPUT ON
DECLARE
v_file Utl_File.File_Type;
v_xml CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML document from query.
v_xml := DBMS_XMLQuery.GetXML('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(SQLERRM,1,255));
Utl_File.FClose(v_file);
END;
/

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

Nov 5 '07 #4