Visit Our UNIX and Linux User Community


shell script - insert oracle


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting shell script - insert oracle
# 1  
Old 08-05-2009
shell script - insert oracle

Hi Frnds,

in shell script I have one problem while inserting into oracle table .

Code:
my script 
 
#! /usr/bin/sh
 while read record
   do
        echo $record
    
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<eof
        insert into STN.STN_ERROR_TABLE values($record);
        eof`
 
        echo $X
   done < ora.txt

ora.txt will contain records as below
Code:
STN,STN_SAP_MATERIAL_ATTR, SMA_FEED_RECEIVED_ID = "MASTER_MATERIAL_ATTR_090709",ORA-12811,ff,gg, gopal
 
STN,STN_SAP_MATERIAL_ATTR, SMA_FEED_RECEIVED_ID = "MASTER_MATERIAL_ATTR_090709",ORA-12811,ff,gg, gopal
 
STN,STN_SAP_MATERIAL_ATTR, SMA_FEED_RECEIVED_ID = "MASTER_MATERIAL_ATTR_090709",ORA-12811,ff,gg, gopal
...

but the scripts throe error
Code:
ERROR: ORA-01741: illegal zero-length identifier  and
SP2-0042: unknown command "eof" - rest of line ignored.

the records are not getting inserted
Plz suggeset sollution for this errrors
# 2  
Old 08-05-2009
Hi.

The closing eof must be at the start of a line, and on its own.

i.e.
Code:
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<eof
        insert into STN.STN_ERROR_TABLE values($record);
eof
`

# 3  
Old 08-05-2009
Code:
 
hi frnd
what u r teling . i couldnt understand. i have given the samething in my script . its throwing error. even if its same , i copied ur suggested solution and ran the script. still its throws same error

plz clarify my clearly. plz frnd
# 4  
Old 08-05-2009
Code:
my script 
 
#! /usr/bin/sh
 while read record
   do
        echo $record
    
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<eof
insert into STN.STN_ERROR_TABLE values($record);
eof
`
 
        echo $X
   done < ora.txt

or
Code:
my script 
 
#! /usr/bin/sh
 while read record
   do
        echo $record
    
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<-eof
<tab>insert into STN.STN_ERROR_TABLE values($record);
<tab>eof
`
 
        echo $X
   done < ora.txt

Jean-Pierre.
# 5  
Old 08-05-2009
Quote:
Originally Posted by aigles
Code:
my script 
 
#! /usr/bin/sh
 while read record
   do
        echo $record
 
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<eof
insert into STN.STN_ERROR_TABLE values($record);
eof
`
 
        echo $X
   done < ora.txt

or
Code:
my script 
 
#! /usr/bin/sh
 while read record
   do
        echo $record
 
        X=`sqlplus -s STN/errrmddb20@MAHFDR <<-eof
<tab>insert into STN.STN_ERROR_TABLE values($record);
<tab>eof
`
 
        echo $X
   done < ora.txt

Jean-Pierre.
Hi frnd
Code:
you have given 2 option. the 1st option and my code both are 100% same frnd. even i copied the ur code and ran the script . but its not working . :( 
throws same error

# 6  
Old 08-05-2009
The eof thing is a problem, which stands out immediately. So much that I forgot to look at you input file!

Is it valid? It looks strange to me.

One of your insert statements would read:

Code:
insert into STN.STN_ERROR_TABLE 
  values(STN,STN_SAP_MATERIAL_ATTR, SMA_FEED_RECEIVED_ID =
   "MASTER_MATERIAL_ATTR_090709",ORA-12811,ff,gg, gopal);

Is that a valid insert statement?
# 7  
Old 08-05-2009
Quote:
Originally Posted by scottn
The eof thing is a problem, which stands out immediately. So much that I forgot to look at you input file!

Is it valid? It looks strange to me.

One of your insert statements would read:

Code:
insert into STN.STN_ERROR_TABLE 
  values(STN,STN_SAP_MATERIAL_ATTR, SMA_FEED_RECEIVED_ID =
   "MASTER_MATERIAL_ATTR_090709",ORA-12811,ff,gg, gopal);

Is that a valid insert statement?
Code:
yes frnd,
the value from ora.txt and the insert command fine only. thats how the value should get populated. but its not getting inserted. i dont know why :(


Previous Thread | Next Thread
Test Your Knowledge in Computers #794
Difficulty: Medium
1/10 has a finite binary representation.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Help on Oracle insert from shell script

Hi All I am trying to create a shell script to insert in to oracle table so far insert happens with out an issue, but i cant pass message variable to sqlplus script insert. i have highlighted the variable in red. Please help. thanks in advance. #!/bin/sh df -H | grep -vE... (3 Replies)
Discussion started by: LPK
3 Replies

2. Shell Programming and Scripting

Script to ingest a csv, validate data and insert into Oracle

Hi all i would appreciate your help... I am looking for a set of unix commands which i can use to 1) ingest a csv file with a known format 2) validate the filename 3) validate the data/datatypes 4) Insert into an oracle db Can you help get me started? yogz888 (1 Reply)
Discussion started by: yogz888
1 Replies

3. UNIX for Dummies Questions & Answers

Insert text into a file using shell script

Hi, I need to insert "Hello World" text into a file called hai.txt using shell scripting. Kindly help me. For eg: If I open the file hai.txt by giving linux command cat hai.txt, the content of the file should have the text Hello World in it. Thanks (5 Replies)
Discussion started by: karthick nath
5 Replies

4. Shell Programming and Scripting

Shell Script to insert text after Tag

Hello, I'm doing an Shell Script to insert a text on XML file, i tried sed, awk, perl... i'm doing something wrong, please help me :) well, the script is a bit large, i get some infos on script before 'run' this part to insert the text on XML... domobile() { let i++ echo ... (1 Reply)
Discussion started by: tassomanoel
1 Replies

5. Shell Programming and Scripting

Insert script result into Oracle Table

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)
Discussion started by: vichuelaa
8 Replies

6. Solaris

Execution problem in shell script while insert into DB

Hi, am facing some problem while inserting a record into a script Please find script below. `sqlplus -s asdf/asdf123 <<eof! set feedback off; set heading off; set verify off; insert into... (2 Replies)
Discussion started by: senkerth
2 Replies

7. Shell Programming and Scripting

Korn shell program to parse CSV text file and insert values into Oracle database

Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database. I need to write the korn shell program on Red Hat Enterprise Linux server. Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies

8. Shell Programming and Scripting

Insert C code in shell script

Hi, Anybody know on how to insert C code in shell script. I am writing BLOB data to a database table in C but I don't know on how to insert the C code in shell script. Thanks in advance. (1 Reply)
Discussion started by: badbunny9316
1 Replies

9. UNIX for Advanced & Expert Users

unix script for update or insert records from a file to a oracle table

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)
Discussion started by: unihp1
1 Replies

10. Shell Programming and Scripting

Shell Script: want to insert values in database when update script runs

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)
Discussion started by: ring
1 Replies

Featured Tech Videos