Home » SQL & PL/SQL » SQL & PL/SQL » retrieving data from text file using utl_file
retrieving data from text file using utl_file [message #291652] Sat, 05 January 2008 12:11 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
i am having a text file as folows

BTM 5008516 0010CTCTLEDPINO INV OR5008516
BTM 5008516 0020A3TH1
BTM 5008516 0030A4TH2
BTM 5008516 1000ADBT100020201 BTM

i have to access a data where the bold letter header is a mark. i have to access data from the line where the given header is available. i have to get the data which is at a perticular position wrt the start of the header. for example i have to get the data from header with 1000AD. i have to retrieve BTM which is at 30th position.

i have attached the input file plz have a look at this and give a soln how to retrieve it?
Re: retrieving data from text file using utl_file [message #291653 is a reply to message #291652] Sat, 05 January 2008 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSTR
SUBSTR

Regards
Michel
Re: retrieving data from text file using utl_file [message #291683 is a reply to message #291653] Sat, 05 January 2008 23:25 Go to previous messageGo to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
declare
count number;
head varchar2(20):='1000AD';
Position number:=103;
width number:=4;
data varchar2(4000);
file1 UTL_FILE.FILE_TYPE;

BEGIN
file1:= UTL_FILE.fopen('ORALOAD','invoice_input.txt','r');
LOOP
UTL_FILE.get_line(file1,data);
-- DBMS_OUTPUT.PUT_LINE(data);
EXIT WHEN LENGTH(data) <=0;
IF(substr(data,92,4) = '1000') THEN
data:= SUBSTR(data,position,width);
DBMS_OUTPUT.PUT_LINE(data);
-- return data;
END IF;


END LOOP;

UTL_FILE.fclose(file1);
exception
when others then
utl_file.fclose(file1);

end;



I am using the above code to retrieve data but i am not geting any thing as output.There is nothing in the data please suggest a solution.
Re: retrieving data from text file using utl_file [message #291686 is a reply to message #291652] Sat, 05 January 2008 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
While you may think what you have posted is easily read & understood, I for one says that it hurts my eyes.
http://www.orafaq.com/forum/t/88153/0/
If you would read & FOLLOW posting guidelines as stated in above URL, then you might get more complete & useful responses.
Pay attention for how to FORMAT code & providing table DDL & test data DML.
Re: retrieving data from text file using utl_file [message #291691 is a reply to message #291683] Sun, 06 January 2008 00:33 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you get nothing then either (or both):
1/ IF( w"xwsubstr(data,92,4) = '1000') is always false
2/ you don't execute "set serveroutput on"
3/ you have an exception but you don't see as your "when others" clear it

NEVER EVER use "when others" without RAISE or RAISE_APPLICATION_ERROR.

Regards
Michel
Previous Topic: 'ORA-30487: ORDER BY not allowed here'
Next Topic: Textarea tag using Oracle HTP package
Goto Forum:
  


Current Time: Fri Dec 09 21:46:05 CST 2016

Total time taken to generate the page: 0.10542 seconds