import/export data into/from oracle database

[ociruj]

hi,

i want to import/export data into/from oracle database. how will i do this using i think text.io or text_io...

please give me sample code...

thanks....

 

[Turkbear]

Hi, A confusion of terms here;
Do you want to INSERT data from some non-oracle source (like a delimited text file) or
do you want to IMPORT data from one Oracle table/user/instance to another?

Same thing the other way..Export to Oracle or Extract to some non-oracle type file?

Can you clarify what you want to do?
Thanks,

 

[ociruj]

thanks for the reply Turkbear...

its the same thing.... import from text delimited file and/or export from oracle to text file...

regards..

 

[Turkbear]

Ok,
To export to text file you will need to create a spool file and use a select statement like
select fieldnamme1 ||','||fieldname2||','||filedname3
and like that or I believe you can set COLSEP To a , ( or other delimiter if you data has ,s in some fields) and save the concatenation part..

It is even easier with some reporting tool like Crystal Reports than can export to a delimited file..

To Insert the data look at the docs on SqlLoader..

http://tahiti.oracle.com

 

[ociruj]

thank you for your suggestion.... but what i need is to use text.io..... how do i do it.... please help me...

thanks again...

 

[martincab]

Text_io only can be used from oracle forms, if you want to export text from server you can use file_utl package instead.

A litle text_io code:

PROCEDURE WRITE_DEPT_BLOCK (FILENAME IN VARCHAR2) IS
W1 MYFILE TEXT_IO.FILE_TYPE;
BEGIN
GO_BLOCK('DEPT');
IF :SYSTEM.BLOCK_STATUS != 'NEW' THEN
FIRST_RECORD;
W2 MYFILE := TEXT_IO.FOPEN(FILENAME, 'W');
LOOP
W3 TEXT_IO.PUTF(MYFILE, '%s,%s,%s\n', TO_CHARDEPT.DEPTNO),
EPT.DNAME, EPT.LOC);
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
NEXT_RECORD;
END LOOP;
W4 TEXT_IO.FCLOSE(MYFILE);
END IF;
END;



Martin Cabrera
Oracle DBA/Programmer

 

[ociruj]

thanks mr. martin,

ur right this is what i needed.... actually it will be used in forms... so the user does not need to use pl/sql..

can u give me an example of importing text delimited file using text_io??

thanks again and regards...