Read Text File to Insert Data into Oracle SQL Table

How to load data to oracle table from text file and check if record already exists using shell script?

Doing the solution the hardest way

$ cat test.dat
id , jobname , started, ended , time
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10

In Oracle

SQL>  create table t ( id number, jobname varchar2(1) , started number, ended number, elapsed number ) ;

Table created.

SQL> insert into t values ( 1 , 'A' , 1130 , 1200 , 30 ) ;

1 row created.

SQL> insert into t values ( 2, 'B' , 1120 , 1130 , 10 ) ;

1 row created.

SQL> select * from t ;

        ID J    STARTED      ENDED    ELAPSED
---------- - ---------- ---------- ----------
         1 A       1130       1200         30
         2 B       1120       1130         10

Under this scenario our program needs to update the record with ID 2 and insert the record with ID 3. This is just a basic shell program to do so, but by far this is the worst way to handle this problem. An external table using the oracle sql loader driver would be much better, easier and faster.

Code

#/bin/bash 

records=$(cat /home/ftpcpl/test.dat | tail -n +2 | wc -l)
echo "Number of records in file: $records" 

record_exists ()
{

record_id=$1

counter=`sqlplus -S "/ as sysdba" << eof
whenever sqlerror exit 2;
set echo off verify off head off feed off
select count(*) from t where t.id = ${record_id} ;
eof`

if [[ $? -eq 2 ]]; then exit 2; fi 

export counter=$(echo ${counter} | tr -d '\n')

}

UpdateRecord ()
{

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
update t set jobname = '${jb}' , 
   started =  ${st}  , 
   ended   =  ${en}  , 
   elapsed =  ${ti}  
where id = ${id} ;
commit;
eof

if [[ $? -eq 2 ]]; then exit 2; fi 

}

InsertRecord ()
{

${ORACLE_HOME}/bin/sqlplus "/ as sysdba" << eof >> $logfile
whenever sqlerror exit failure;
insert into t values ( ${id} , '${jb}' , ${st}  , ${en}  , ${ti} );
commit;
eof

if [[ $? -eq 2 ]]; then exit 2; fi 

}

logfile=test.log 

tail -n +2 /home/ftpcpl/test.dat | 
while read -r line
do
  echo $line    
  export id=$(echo $line | awk -F ',' '{print $1}' | xargs echo -n )
  export jb=$(echo $line | awk -F ',' '{print $2}' | xargs echo -n )
  export st=$(echo $line | awk -F ',' '{print $3}' | xargs echo -n )
  export en=$(echo $line | awk -F ',' '{print $4}' | xargs echo -n )
  export ti=$(echo $line | awk -F ',' '{print $5}' | xargs echo -n )
 
  record_exists ${id}
  
  if [[ ${counter} -eq 1 ]];
  then
    UpdateRecord 
  else 
    InsertRecord 
  fi 
done 

Demo program

./test.sh
Number of records in file: 3
1 , A , 1130 , 1200 , 30
2 , A , 1120 , 1130 , 10
3 , C , 1120 , 1130 , 10
[ftpcpl@scglvdoracd0006 ~]$ cat test.log

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:34 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>   2    3    4    5
1 row updated.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:36 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>   2    3    4    5
1 row updated.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 13 15:46:38 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> SQL>
1 row created.

SQL>
Commit complete.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

Final records

SQL> select * from t ;

        ID J    STARTED      ENDED    ELAPSED
---------- - ---------- ---------- ----------
         1 A       1130       1200         30
         2 A       1120       1130         10
         3 C       1120       1130         10

Want to insert data in oracle table from 2nd row of a text file in php

Just add following line at first place of you loop (if you are starting from $i = 1):

if($i==1){
    continue;
}

Not tested, But Hope It will work;

Logic:
Skip the first iteration of loop...:)

Reading text file line by line in SQL*Plus

You can try to use Oracle External Tables.

For example, you have next file:

$ cat employee.dat

smith   clerk       800
scott   analyst     3000
adams   clerk       1100
miller  clerk       1300

Create external table:

create table employees (
ename varchar2(10),
title varchar2(10),
salary number(8))
organization external(
type oracle_loader default directory work_dir
    access parameters (record delimited by new line fields(
        ename char(10), title char(10), salary char(8)))
location ('employee.dat'))
parallel

Now you can use this file as SQL-table:

select * from employees;

Is it possible to read the contents of a flat file into a pl/sql table (not a regular table)?

Something like this might help, however it needs manual processing.

declare
  f UTL_FILE.FILE_TYPE;
  ldir varchar2(100) := 'DATA_PUMP_DIR';
  lfile varchar2(100) := 'writefile.sql';
  vtable m_code_tabletype := m_code_tabletype();
BEGIN
f := UTL_FILE.FOPEN(ldir,lfile,'R');
  IF UTL_FILE.IS_OPEN(f) THEN
    LOOP
      BEGIN
        UTL_FILE.GET_LINE(f,lfile);
        IF lfile IS NULL THEN
          EXIT;
        END IF;
         --do parsing
         -- col1, col2, ...
         vtable.extend;
         vtable(vtable.count) := m_code_object(col1, col2, ...);
      END;
    END LOOP;

  COMMIT;
  END IF;
END;

It is possible insert dummy (dash) into first row data use select statement in oracle SQL?

One option is to UNION two data sets; one contains dummy dashes, while another contains "real" data. Note that dashes are considered to be strings, which means that you'll have to cast other datatypes to character datatype (see to_char(deptno) in my example):

SQL> with temp as
  2    (select 1 rn, '-' deptno     , '-' dname, '-' loc from dual
  3     union all
  4     select 2 rn, to_char(deptno), dname    , loc     from dept
  5    )
  6  select deptno, dname, loc
  7  from temp
  8  order by rn, deptno;

DEPTNO     DNAME          LOC
---------- -------------- -------------
-          -              -
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

SQL>

The rn column is used to correctly sort the output (dashes first, the rest of data next).

---

Related Topics