Read data from a text file and Instead storing into a table, Can we directly add the data into a cur-CodePudding

Context: I have a text file that may contain a data say :

Employee Salary
name:start_salary:current_salary
emp1:30000:40000
emp2:35000:40000
.
.
Emp details
name:role:experience
emp1:Analyst:2
emp2:DBA:1
emp3:Developer:3

I want to read this text file from a PL/SQL code and I can load the data into a Table and then using a cursor I can utilize that data in my PL/SQL code.

But I want to skip the step of creating a table and want to use the data on the fly, may be Can we directly read the data into cursor?

Can someone please help if that is possible?

CodePudding user response：

You can do that using the UTL_FILE package. This allows PL/SQL to read and write operating system text files.

Once you open the file, you can read its contents into a PL/SQL cursor, then perform all necessary processing directly on the data in the cursor.

Note that you have to know how your file is formatted and the structure of the data you are reading.

CodePudding user response：

Check out implict EXTERNAL TABLE syntax, which lets you query a flat file direcly from within a SELECT statement ,eg

SQL> select * from external (
  2        empno     number(4),
  3        ename     varchar2(10),
  4        ...
 12       ( type oracle_loader
 13         default directory TMP
 14         access parameters
 15         ( records delimited by newline
 16           fields terminated by ','
 17           missing field values are null
 18           ( empno,ename,job,mgr,...)
 19         )
 20         location ('emp20161001.dat')
 21  );