Error while trying to insert a record to oracle database table via shell script in Linux

Question

Could anyone help me on resolving this error which I am getting when I try to run a shell script test.sh with parameter as 20211007 The code is as below:

#!/bin/sh

ODATE=${1}
file_source_sys="abc"
File_name="xyz"
file_deliver_time="20210218:12:56:76"

HOME_DIR="/var/tmp/Sneha"

source ~/env/INFORMATICA.env
source ${HOME_DIR}/db.properties

cd ${HOME_DIR}

cat <<EOD > temp.sql
INSERT INTO test VALUES 
('${file_source_sys}','${File_name}','${file_deliver_time}','$ODATE');

commit;

quit
EOD

sql ${USER}/\"${PASS}\"@${DB_INSTANCE} @temp.sql

The error is as below:

Last Successful login time: Sat Nov 06 2021 18:53:25 +01:00

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

Error starting at line : 1 File @ /var/tmp/Sneha/temp.sql In command - INSERT INTO test VALUES ('abc','xyz','20210218:12:56:76','20211005') Error at Command Line : 1 Column : 13 File @ /var/tmp/Sneha/temp.sql Error report - SQL Error: ORA-00942: table or view does not exist

Commit complete.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.11.0.0.0

Note I double checked and confirmed that table test is created in DB. Also I manually checked my inserting this insert statement generated into DB it is working fine. But via Shell script it is not happening.

Also I see the temp.sql file is generated with below content:

INSERT INTO test VALUES ('abc','xyz','20210218:12:56:76','20211005');

commit;

quit

I am not sure where in the code I am going wrong, as I can see the script is connecting to database successfully and also doing commit and disconnecting itself as well properly.

Your help is much appreciated. Many thanks

Did you check that statement via SQL*Plus with the same credentials? — astentx
– astentx, Commented Nov 6, 2021 at 19:30
Given that you can connect and the Oracle error is quite clear, I can only imagine that you are using a different user or your user misses some privilege. What happens if you replace the insert with a select * from test;? — doberkofler
– doberkofler, Commented Nov 7, 2021 at 8:18
As a diagnostic test, how about having it print out the current instance and schema name? — William Robertson
– William Robertson, Commented Nov 7, 2021 at 19:13

Jakub Klimek · Accepted Answer · 2021-11-07 15:58:36Z

0

Are you sure that you are connected to the right database, it says that the table 'test' doesn't exist, try doing:

INSERT INTO DB_NAME.test VALUES (...);

answered Nov 7, 2021 at 15:58

Jakub Klimek

63311 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sneha Over a year ago

I am connecting to a simulation database hence this error was coming, the actual schema and simulation schema are so much alike that I missed one one character in the hostname while connecting to actual schema. Many thanks for your suggestions. :)

Sneha · Accepted Answer · 2021-11-08 14:55:33Z

0

I am connecting to a simulation database hence this error was coming, the actual schema and simulation schema are so much alike that I missed one one character in the hostname while connecting to actual schema. Many thanks for your suggestions. :)

answered Nov 8, 2021 at 14:55

Sneha

453 silver badges12 bronze badges

Collectives™ on Stack Overflow

Error while trying to insert a record to oracle database table via shell script in Linux

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related