Intelligent Script to Insert Records in Database Table
Hello All Members,
I am new to this forum and to the shell scripting. I want to write a shell script to do the following:
Scenario: I have a pipe delimited .txt file with multiple fields in it. The very first row of the file contains the name of the column which resembles the name of the column in the corresponding database table. I am providing the example of the file below:
Create table EMPLOYEE (
Name VARCHAR2(255),
Dept NUMBER,
Empno NUMBER,
Salary NUMBER,
DOB DATE,
DOJ DATE);
Requirement: I want to write a script which can read the first row of the text file and create a dynamic insert statement based on the columns available in the file. The reason for this requirement is because:
I do not know whether same number of columns will arrive in file every time or not. File may contains all the columns or very few of them.
The order of the column is also not fixed. They could be in random order e.g. File can arrive with ”Dept”|”Empno”|”DOB”|”DOJ”|“Name”|”Salary”
Currently I am using external table but I need to change the table definition every time files arrive in different column order. I cannot force the format of the file on the supplier of this file. So I have to work with this.
PsuedoCode: With my limited knowledge, I think below would be the pseudo code.
Read the first line of the file.
Read the columns available in the first line.
Create insert statement using the columns available in the first line.
And put the values to the above insert statement from the second row onwards.
I know this will not be a simple script (atleast for me). Hence I am posting this in forum. I would appreciate if any of the senior unix member can help me out with this.
Thanks.
Last edited by rbatte1; 01-05-2015 at 12:32 PM..
Reason: Added CODE, ICODE & LIST=1 tags
Did you consider the threads proposed at the bottom of this page?
Some questions reg. your requirement:
- How does the script know the table name?
- Is it always the same?
- Will the table be deleted somewhere else?
- How does the script know the data type of the columns?
- Will the date format always be the same?
Please see my reply below in blue. I hope I am making sense.
Quote:
Originally Posted by RudiC
Did you consider the threads proposed at the bottom of this page?
Yes, I have seen the links below. Most of them proposing to use the sqlldr which is something I cannot use in this case. I am currently using External tables (via OWB). But there is a restriction for that too. When file arrives with random order of the column name, the implicit sqlldr of the external table starts throwing error.
Some questions reg. your requirement:
- How does the script know the table name?
Yes, I can arrange for script to know the name of the table based on the filename. I can pass name of the table as input parameter to script which can them embedded into the dynamic sql created by the script.
- Is it always the same?
Yes, table name will always be the same. And so will be the column name. Ofcourse tablename and columns will be different from source file to source file but I think if we can achieve the basic functionality - that will not matter anymore (?)
- Will the table be deleted somewhere else?
The nature and source of the data is such that I do not have any visibility of it. I do not know from where it is coming or how the files is created. Otherwise , I would have asked source side to provide file in a fixed format which can be uploaded into the database table (via sqldr or external table) straight way.
- How does the script know the data type of the columns? Script doesn't need to know the datatype of the column. I can arrange all columns to be of VARCHAR2 type. So that records are loaded irrespective of the data. This can further interpreted or casted into their respective datatypes.
- Will the date format always be the same? Yes. Date format will be same for all the records and for all the files.
I will run this in my environment and revert back to you. The table will already exist in the database so I do not need to add table creation/drop part of the code.
I am trying to understand how this will cater to the changing order of the columns (and column names)? I needed this to read the first row of the file to establish the number (and list) of columns arrive in the file. This list will then be added to the into the INSERT statement followed by the values.
Quote:
Originally Posted by pravin27
Could this help you ?
Code:
#!/bin/sh
_fileName=$1
_tableName=$(basename ${_fileName})
_SQLFile=${_tableName}".sql"
tr "|" "," < ${_fileName} | awk -F"," -v tableNM=$_tableName -v sqlfn=${_SQLFile} 'BEGIN{sqlInsert="INSERT INTO table (columns) values (cValues)"}
{ if( NR==1)
{
gsub("\"","",$0)
clName=$0
next
}
gsub("table",tableNM,sqlInsert)
gsub("columns",clName,sqlInsert)
gsub("cValues",$0,sqlInsert)
print sqlInsert";\n" > sqlfn;
}'
sqlplus -s user/passwrd <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
@${_SQLFile}
select sysdate from dual;
exit;
ENDOFSQL
RetCode=$?
#Check the return code from SQL Plus
if [ $RetCode != 0 ]
then
echo "********************"
echo "ERROR: The SQL Plus Command Failed. RetCode: $RetCode"
else
echo "********************"
echo "SQL Plus Successfully Ran. RetCode: $RetCode"
fi
Invocation
Code:
/path/toscript/InsertSql.sh /file/path/employee
---------- Post updated at 09:56 AM ---------- Previous update was at 09:54 AM ----------
I think i have lost the second post of yours which used awk command. I put that code in action but was expecting output as below:
Code:
insert into EMPLOYEE (Name, Deptno,Empno, Salary, DOB, DOJ) values("Alexander","10","200","$1000","25/05/1977","01/01/2015");
insert into EMPLOYEE (Name, Deptno,Empno, Salary, DOB, DOJ) values("Graham","11","300","$1500","05/09/1970","19/02/2011");
insert into EMPLOYEE (Name, Deptno,Empno, Salary, DOB, DOJ) values("Bell" ,"12","400","$2000","31/01/1965","01/10/1999");
I ran the script in my environment. The output I am getting from it is shown below.
1.I ran the script and it is populating correct insert statement in the file. But also giving below error after execution.
Code:
./InsertSQL.sh : line 30: [:0:unary operator expected
2.Besides, I am also getting error that it is not able to find the employee.sql file.
But when I looked into the directory, I can see a file {_SQLFile}
I think the script wants to create an employee.sql file and then run this file against sqlplus command which will eventually fire the insert statements created in the file.
Code:
INSERT INTO TABLE (NAME,DEPT,EMPNO,SALARY,DOB,DOJ) values ("Alexander","10","1000","10000","25/05/1977","01/01/2015");
---------- Post updated at 12:19 PM ---------- Previous update was at 11:58 AM ----------
I ran Pravin's code. After correcting many typos, which I introduced, I manage to run the code. And getting below two errors:
Code:
INSERT INTO EMPLOYEE (NAME, DEPT,EMPNO,SALARY,DOB,DOJ) VALUES ("Alexander","10","1000","10000","25/05/1977","01/01/2015");
ORA-00984: Column not allowed here.
I ran the command in sqlplus and realised the values part of the INSERT statement is carrying double quotes around VARCHAR columns. These should be single quotes. So Ideally the Insert Statement should look like below. I am amazed with help from your guys, I am getting there very fast. I think this is one of the last few hurdles and the script will be ready to roll.
Code:
INSERT INTO EMPLOYEE (NAME, DEPT,EMPNO,SALARY,DOB,DOJ) VALUES ('Alexander','10','1000','10000','25/05/1977','01/01/2015');
ORA-00984: Column not allowed here.
Last edited by chetanojha; 01-05-2015 at 01:32 PM..
Reason: Corrected reply
I have this code with me but the condition is If any of the mandatory columns are null then entire file will be rejected.
LOAD DATA
infile ' ' #specifies the name of a datafile containing data that you want to load
BADFILE ' ' #specifies the name of... (1 Reply)
I have a test file that I want to read and insert only certain lines into the
the table based on a filter.
1. Rread the log file 12 Hours back Getdate() -12 Hours
2. Extract the following information on for lines that say "DUMP is
complete"
A. Date
B. Database Name
C.... (2 Replies)
Hi Friends,
I looking for the script for the count of the records in table.
and then it's containg the zero records then should get abort.
and should notify us through mail.
Can you please help me out in this area i am lacking. (5 Replies)
I have a table in an Informix DB into which I want to insert multiple records at a time. Data for one of the column should be unique & other column data may be the same for all the records I insert
Typical Insert Statement I use to insert one row :
insert into employee(empid, country, state)... (5 Replies)
Hi All,
I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table.
Eg: insert into tbl_daily_status values(STAT,ENDTIME);
Please help me on this.
#!/bin/ksh
joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det"
for i in $joblist
do... (8 Replies)
I need to write a unix shell script to connect to oracle database and retrieve data from a table, the script should count the total number of rows till date in that table
number of rows which are getting filled per hour on current day and the peak hour of the current day
number of rows which are... (5 Replies)
hi,
I need to create a bash shell script which picks up data from a text file and in the output file puts it into an html made table. I have to use sed and awk utilties to do this
the input text file will contain data in the format:
job name para1 para2 para3 para4 para4
1 ... (1 Reply)
If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries
LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (0 Replies)
Hi,
I have delimited file(|).
Sample data:
1|name|50009|DS24|0|12
2|name|30009|DS24|0|13
3|name|20409|DS24|0|14
4|name|20009|DS24|0|15
5|name|10009|DS24|0|16
I want to load this data into a oracle table (update and insert)
Please help me the commands and also... (1 Reply)
Hi ,
I am new to linux and also also to shell scripting.
I have one shell script which unpacks .tgz file and install software on machine.
When this script runs I want to insert id,filename,description(which will be in readme file),log(which will be in log file) and name of unpacked folder... (1 Reply)
01-05-2015
