shell script - insert oracle

Hi Frnds,

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

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

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

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

Hi.

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

i.e.

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

 
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

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

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

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

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:

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?

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 :(

The technique for the eof label works fine on my system :

GRAKD00CGSB002:kd20sic1:/tmp>cat x.ksh
#! /usr/bin/ksh
    
        X=`sqlplus -s apps/apps <<-eof
                select sysdate from dual;
                eof`
        echo "$X"
GRAKD00CGSB002:kd20sic1:/tmp>x.ksh

SYSDATE
---------
05-AOU-09
GRAKD00CGSB002:kd20sic1:/tmp>

scottn is right, your SQL INSERT syntax is invalid, I think that it must be :

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

Jean-Pierre.

Can you post the results of running this command below. Did you manually type in the same sqlplus command and it inserted okay?

#! /usr/bin/sh
 while read record
   do
        echo $record
    
       sqlplus STN/errrmddb20@MAHFDR <<EOF
insert into STN.STN_ERROR_TABLE values($record);
EOF
     X=$?
 
        echo $X
   done < ora.txt

This is what your script executes once per iteration of that while loop:

$
$ cat testscr.sh
#! /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`
         X="sqlplus -s STN/errrmddb20@MAHFDR <<eof
         insert into STN.STN_ERROR_TABLE values($record);
         eof"
        echo $X
   done < ora.txt
$
$ . testscr.sh
sqlplus -s STN/errrmddb20@MAHFDR <<eof 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); eof
sqlplus -s STN/errrmddb20@MAHFDR <<eof 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); eof
sqlplus -s STN/errrmddb20@MAHFDR <<eof 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); eof
$
$

• Is that the form of INSERT statement that you want to execute ?
• Does it run successfully from the "SQL>" prompt of sqlplus ?

Note that the string delimiter in Oracle is the single-quote character, and in its absence, Oracle assumes the data to consist of identifiers instead of strings.

The "eof" error comes from sqlplus (not the Oracle server), because sqlplus does not understand "eof".

tyler_durden

[quote]
Hi frnd,
u r right . there is a wrong with my record in the file. i want fileds in the records with sigle qouted..
ie.,

'STN','STN_SAP_MATERIAL_ATTR', 'SMA_FEED_RECEIVED_ID'..
'STN','STN_SAP_MATERIAL_ATTR', 'SMA_FEED_RECEIVED_ID'..
.
.
 

now iam creating this ora.txt file throuw awk script as below

[code]
print Target_schema,Target_table,Values_detail >ora.txt

[code]
but this way how can attach single qoutes to each fields ? plz suggest

A possible technique :

awk -v Q="'" ' 
{ 
   Values_detail = Q Value_1 Q OFS Q Value_2 Q OFS Q Value_3 Q;
   print Q Target_schema Q, Q Target_table Q, Values_detail >ora.txt
}
' inputfile

Jean-Pierre.

In a production environment I suggest NOT to use this approach for following reasons:
a) you connect to the database for every single record in your file.
b) your code may fail depending on your input file. Especially single quotes as data will give you trouble.
c) you kill your DB-performance by flooding it's SGA with your statements. Ask your DBA about the advantage of bind-variables.

I recommend to use SQL*Loader for this task. Depending on your DB-Version a construct inside the DB called "External Tables" may also be used to solve your problem.