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

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

CodePudding user response：

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 (...);

CodePudding user response：

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. :)