Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE (Oracle 19c)
UTL_FILE [message #685373] Fri, 17 December 2021 12:19 Go to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Hi, I have a requirement where I was using current logic to load data into table. Issue is now we have a table that have 200 fields, so is there a better way to code this dynamically rather than defining 200 variables ..

create table A
(
  sno    VARCHAR2(30),
  name   VARCHAR2(30),
  mark   VARCHAR2(30),
  result VARCHAR2(1),
  dept   VARCHAR2(5),
  year   VARCHAR2(30)
); 

create or replace procedure read_file is 
f_line varchar2(2000); 
f utl_file.file_type; 
f_dir varchar2(250); 
fname varchar2(50); 
Pipe1 varchar(10); 
Pipe2 varchar(10); 
Pipe3 varchar(10); 
Pipe4 varchar(10); 
Pipe5 varchar(10); 
f_sno a.sno%type; 
f_sname a.name%type; 
f_mark a.mark%type; 
f_result a.result%type; 
f_dept a.dept%type; 
f_year a.year%type; 
begin  
f := utl_file.fopen('UTL_FILE_DIR','student.txt','r'); 
loop 
begin 
utl_file.get_line(f,f_line); 
exception when no_data_found then 
exit; 
end;  
Pipe1 := INSTR(f_line, '|' ,1 , 1); 
Pipe2 := INSTR(f_line, '|' ,1 , 2); 
Pipe3 := INSTR(f_line, '|' ,1 , 3); 
Pipe4 := INSTR(f_line, '|' ,1 , 4); 
Pipe5 := INSTR(f_line, '|' ,1 , 5); 
f_sno := SUBSTR(f_line, 1, Pipe1-1); 
f_sname := SUBSTR(f_line, Pipe1+1, Pipe2-Pipe1-1); 
f_mark := SUBSTR(f_line, Pipe2+1, Pipe3-Pipe2-1); 
f_result := SUBSTR(f_line, Pipe3+1, Pipe4-Pipe3-1); 
f_dept := SUBSTR(f_line, Pipe4+1, Pipe5-Pipe4-1); 
f_year := SUBSTR(f_line, Pipe5+1); dbms_output.put_line(f_sno ||' '|| f_sname || ' ' || f_mark || ' ' || f_result ||' ' || f_dept||' '|| f_year); 
insert into a VALUES (f_sno,f_sname,f_mark,f_result,f_dept,f_year); 
end loop; 
utl_file.fclose(f); 
commit; 
end;  

begin
  read_file();
end; 

Select * from a;

  • Attachment: student.txt
    (Size: 0.25KB, Downloaded 1205 times)
Re: UTL_FILE [message #685374 is a reply to message #685373] Fri, 17 December 2021 12:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
External table.

SY.
Re: UTL_FILE [message #685375 is a reply to message #685374] Fri, 17 December 2021 12:47 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Yeah I gave that option, but they wanted to use only UTL and asked me to do this dynamically . I am not sure if we can do this dynamically.
Finding number of pipes and creating table based on it..with columns c1.....c99
Re: UTL_FILE [message #685376 is a reply to message #685375] Fri, 17 December 2021 12:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Why would they insist on UTL_FILE? What is business reason to use UTL_FILE?

SY.
Re: UTL_FILE [message #685377 is a reply to message #685376] Fri, 17 December 2021 12:57 Go to previous messageGo to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
They have been using UTL for so many years and dont want to deviate from it. No justification.
Is there a possibility that we can create table with columns based on pipes and insert into it?
Re: UTL_FILE [message #685378 is a reply to message #685377] Fri, 17 December 2021 12:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
With that logic we would be in stone age forever.

SY.
Re: UTL_FILE [message #685379 is a reply to message #685378] Fri, 17 December 2021 13:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can use UTL_FILE + DBMS_SQL but again, it would be way more complex than simple

INSERT
  INTO A
  SELECT  *
    FROM  EXTERNAL_TBL
/
SY.
Re: UTL_FILE [message #685380 is a reply to message #685375] Fri, 17 December 2021 13:06 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Finding number of pipes and creating table based on it..with columns c1.....c99

Yes you can do it this way but this is ugliest and slowest one.
External table or SQL*Loader are the ones to use, they have been created for that purpose.

Anyway, if you want to keep this way, I advise you read again your previous topics where solutions and advice have been given:
regexp replace
Count occurrences of String in field
Regexp_like

Previous Topic: XMLtype throwing an error ORA-00932
Next Topic: ORA 12545 | UTL_HTTP
Goto Forum:
  


Current Time: Thu Mar 28 10:29:27 CDT 2024