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
# 8  
Old 08-05-2009
The technique for the eof label works fine on my system :
Code:
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 :
Code:
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.
# 9  
Old 08-05-2009
Can you post the results of running this command below. Did you manually type in the same sqlplus command and it inserted okay?
Code:
#! /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

# 10  
Old 08-05-2009
Quote:
Originally Posted by Gopal_Engg
...
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
...
This is what your script executes once per iteration of that while loop:

Code:
$
$ 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
# 11  
Old 08-06-2009
Quote:
Originally Posted by aigles
The technique for the eof label works fine on my system :
Code:
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 :
Code:
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.
[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.,
Code:
'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
# 12  
Old 08-06-2009
A possible technique :
Code:
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.
# 13  
Old 08-06-2009
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.

Previous Thread | Next Thread
Test Your Knowledge in Computers #69
Difficulty: Easy
Grep stands for 'global regular expression process'.
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