Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: READ UTL_FILE using separator
PL/SQL: READ UTL_FILE using separator [message #247432] Mon, 25 June 2007 21:17 Go to next message
eeekin07
Messages: 2
Registered: June 2007
Location: melaka
Junior Member

hi, i am now using utl_file to read/write data to/from textfile.txt.. it is succeed to read the data but i dont know how to read a record which is separated by separator '|'..
eg: column1|column2|column3
column1|column2|column3
column1|column2|column3

anyone could help me on this..?

thank u =)

below is the code i used:

create or replace PROCEDURE READ_UTL_EKIN(p_dir_path VARCHAR2,p_filename VARCHAR2) IS
v_filename UTL_FILE.FILE_TYPE;
v_NewLine VARCHAR2(200);
BEGIN
v_filename := UTL_FILE.FOPEN (p_dir_path, p_filename, 'r');
BEGIN
LOOP
UTL_FILE.GET_LINE(v_filename, v_NewLine);
DBMS_OUTPUT.PUT_LINE('The record is:'||v_NewLine);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
UTL_FILE.FCLOSE(v_filename);
END READ_UTL_EKIN;
Re: PL/SQL: READ UTL_FILE using separator [message #247434 is a reply to message #247432] Mon, 25 June 2007 21:22 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
It looks to me like you solved your problem.
Re: PL/SQL: READ UTL_FILE using separator [message #247437 is a reply to message #247434] Mon, 25 June 2007 21:40 Go to previous messageGo to next message
eeekin07
Messages: 2
Registered: June 2007
Location: melaka
Junior Member

no, my output my to be like this:

The record is : column1|column2|column3
The record is : column1|column2|column3
The record is : column1|column2|column3

acctually, i dont want to display the "|" symbol..
What if i want to save to a table? i dont know how to recognize each column which is separate by "|"..
any idea..?

Re: PL/SQL: READ UTL_FILE using separator [message #247440 is a reply to message #247432] Mon, 25 June 2007 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>acctually, i dont want to display the "|" symbol..
Then don't display the "|".
You could TRANSLATE or REPLACE them to any other character.
It is your program & you can do as you desire.

>What if i want to save to a table?
You simply store them as a single VARCHAR2.

>i dont know how to recognize each column which is separate by "|"..
LOOP across the string & when you find a "|" strip off the variable just found.

any idea..?
Trial & error is how most folks learn to program. So start learning.
Re: PL/SQL: READ UTL_FILE using separator [message #247469 is a reply to message #247432] Tue, 26 June 2007 01:00 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an "external table" it will to do it for you.

Regards
Michel
Previous Topic: query problem
Next Topic: Working with CLOB
Goto Forum:
  


Current Time: Mon Dec 05 14:44:12 CST 2016

Total time taken to generate the page: 0.20427 seconds