Assign variable for INSERT INTO statement

Hello,

Can anyone tell me that,
How can I assign variable to shell script variable, which i need to use in INSERT INTO statement?

my shell script variables are,

 
EMPNAME=`regular expression`
EMPID=`regular expression`
EMPBDATE=`regular expression`

Now through ksh script I am connecting to database and want to use above variable values in my INSERT INTO statement.

 
INS_REC=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set serveroutput on
declare
v_cnt NUMBER:=0;
name VARCHAR(6):=$EMPNAME;
id      NUMBER(6):=$EMPID;
date  NUMBER(14):=$EMPBDATE;
begin
select count (*) into v_cnt from TBL1;
dbms_output.put_line(v_cnt);
if v_cnt >= 0 then
INSERT INTO TBL1( EMP_NAME,
    EMP_ID,
    BIRTH_DATE
    )
    VALUES (
    name,
    id,
    TO_DATE (date , 'YYYYMMDDHH24MISS')
    );
end if;
EXCEPTION
     WHEN OTHERS THEN
     NULL;
end;
/
EOF)

but I am getting errors as,

ORA-06550: line 3, column 21:
PLS-00201: identifier "EMPNAME" must be declared
ORA-06550: line 3, column 9:
PL/SQL: Item ignored
ORA-06550: line 4, column 21:
PLS-00201: identifier "EMPID" must be declared
ORA-06550: line 4, column 9:
PL/SQL: Item ignored
ORA-06550: line 4, column 21:
PLS-00201: identifier "EMPBDATE" must be declared
ORA-06550: line 4, column 9:
PL/SQL: Item ignored
ORA-06550: line 18, column 25:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 1:
PL/SQL: SQL Statement ignored

Please help me out.

IMO, you are better off to use a scripting language with built-in database support, like PERL or PHP.

I do a lot of database programming (MySQL) and use PHP (and never use KSH or other shell scripts for SQL DB support).

My whole project is in ksh script with Oracle database. I have no choice to use any other scripting as well as DB programming languages.
Please provide any solution for above problem.

Here's a very short testcase that demonstrates a technique of passing shell variables to Oracle SQL -

$
$
$ # display the content of the shell script "f36"
$ cat -n f36
    1  #!/usr/bin/bash
    2  EMPNAME="Napoleon Bonaparte"
    3  EMPID="1"
    4  EMPBDATE="15-Aug-1769"
    5  sqlplus -s test/test <<EOF
    6    INSERT INTO TBL1 (EMP_NAME, EMP_ID, BIRTH_DATE)
    7    VALUES ('$EMPNAME', $EMPID, TO_DATE ('$EMPBDATE','DD-Mon-YYYY'));
    8  EOF
$
$
$ # execute the shell script
$ ./f36
 
1 row created.
 
$
$
$ # verify that the record was inserted
$ echo "select emp_name, emp_id, to_char(birth_date, 'dd-Mon-yyyy') bdate from tbl1;" | sqlplus -s test/test
 
EMP_NAME                 EMP_ID BDATE
-------------------- ---------- -----------
Napoleon Bonaparte            1 15-Aug-1769
 
1 row selected.
 
$
$

I'll let you figure out the details of tailoring it for your scripts.

A few observations about your script:

(a) The Oracle documentation recommends the use VARCHAR2 datatype instead of VARCHAR. Check the Concepts Guide or the SQL Reference guide in the documentation set.

(b) Your "if" condition is redundant. The record count of a table is always 0 or something greater than 0. It can *never* be negative. So your "if" condition will always be true. In fact, I am not sure why you'd want to run a count(*) query before inserting a record into a table!!

(c) The exception handler "when others then null;" is a classic logical bug. That's because "when others" is a catch-all for all exceptions that you either don't know about or don't care about.

These exceptions could be something completely unrelated to your code; serious things like:

ORA-01000: maximum open cursors exceeded due to cursor leakage that could bring your Oracle database to a grinding halt,

or

ORA-1652: unable to extend temp segment by 1024 in tablespace <blah>
which means your temp tablespace is full and requires DBA intervention

or

the dreaded ORA-00600 internal kernel errors that might require you to contact Oracle support

etc. You don't want to "swallow" such exceptions (NULL; statement does exactly that). You want to raise such exceptions to your client program as soon as possible so you or your DBA could do something about it.

Instead of a NULL; there should be a RAISE; statement in "when others then" exception handler. Or better yet, leave it out altogether, and Oracle will do the escalation for you.

It's something like this - if you are on a vacation, and your house catches fire, you'd want to know that as soon as possible! You'd want to let go of your vacation and get back as soon as possible. The NULL statement gives you the impression that everything is fine and dandy, whereas you'd come back to a charred house!

Finally, Neo's suggestion is noteworthy. Small Oracle projects (such as yours apparently) that deal mainly with strings or "string-like" data may be fine with Unix shells. But as the project increases in scope and volume, and as you keep on adding more and more complex datatypes - LOBs, XML, timestamps, Collections - nested tables, varrays etc. the use of a wrapper API like Perl DBI or PHP or JDBC will make your life easier. They are very robust, well tested and bind quite tightly with complex Oracle types.

tyler_durden

4 Likes

Thanks for the information and your time.

still I am facing error from below code,

INSERT INTO BIOS_TRANSCODING ( EMPNAME,EMPID,EMPBDATE)
VALUES('$EMPNAME','$EMPID',TO_DATE('$EMPBDATE','YYYY-MM-DD'));
    

error is,

 TO_DATE("$EMPBDATE","YYYY-MM-DD")
                                                   *
ERROR at line 14:
ORA-06550: line 14, column 24:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored

For point (b), I want to check the table is exists in database or not, If it exists then only do for INSERT INTO statement, So i used if condition with select count(*) statement.
For point (c), I'll try to use raise statement instead of NULL.

Please help me out.

Post the contents of your entire script over here.

Do you have a reason to believe that someone/something will drop that table ?
If you do, then there's something seriously wrong with either your application or its security.

tyler_durden

You are better off with "echo 'junk' | sqlplus . . ." than "<<", especially with tricky shell constructs like this. Also get off "`", as $() is so much prettier, and nests! You can tinker with the echo until you like the final generated code, and then know "|" will not touch it, unlike the "<<" that expands and removes metadata.

If this simple form gets too busy:

echo '...'"$( whatever )"'...'| sqlplus . . .

then go subshell:

(
echo 'this'  # single quotes allow least change, use whenever possible
that # command output
echo "$the_other"  #double quotes allow most expansions but not globbing or removal of single quote: '
) | sqlplus . . . 

---------- Post updated at 10:02 AM ---------- Previous update was at 10:00 AM ----------

Its a mind-set problem. You are not "putting $whatever into your Oracle", you are generating a hunk of text to send to an app, which happens to the the Oracle Command line client.

I am processing input file data, I am checking the 7th field of input file. If its "1" then taking data from that line and inserting it into database table.Here is my script,

#!/bin/sh
usage="`basename $0` <file_name>"

Insert_Record()
{
set -x
INS_REC=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
declare
v_cnt NUMBER:=0;
begin
select count (*) into v_cnt from TBL1;
if v_cnt >= 0 then

INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES('$EMPNAME','$EMPID',TO_DATE('$EMPBDATE','YYYY-MM-DD'));
end if;
end;
/
EOF)

TBL_ERR=$(echo "$INS_REC" | grep -e "SP2-" -e "ORA-")
echo $TBL_ERR
}

process_input_record ()
{
echo "Input file path is $INPUT_FILE_FOLDER"
if [ "$(ls -A $INPUT_FILE_FOLDER)" ]; then
#echo "Take action $INPUT_FILE_FOLDER is not Empty"
for file in "$INPUT_FILE_FOLDER/"ABC_MN_XYZ*.CSV
do	
# Did lots of things for input file data

# Check each line 
line=`head -1 $file`
nrfields=$(IFS=\;; set -- $line; echo $#)
if [ $nrfields -ne 4 ]; then
echo "Incorrect first record fields"
exit
else			
DATE=`echo $line | cut -d ";" -f 2`						
DATEGEN==`echo $DATE | cut -c1-8`			
while read line
do
fields=$(IFS=\;; set -- $line ; echo $#)
Status=`echo $line | cut -d ";" -f 7`
if [ $fields -eq 12 ]; then
if [ "$Status" -eq 1 ]; then
EMPNAME=`echo $line | cut -d ";" -f 2`
EMPID=`echo $line | cut -d ";" -f 4`
Insert_Record

sed "/$line/d" $file > tmp
mv tmp $file
fi
else
echo "Incorrect DETAIL record fields"
fi
done < $file
fi
else
echo "$INPUT_FILE_FOLDER is Empty"
fi
done
}

#----------------------------------------------------------------------------
# Main
#----------------------------------------------------------------------------

if [ $# -eq 1 ]; then

if [ -f $1 ]; then
. $1  
process_input_record

else 
echo "$ERROR_CODE : file $1 is not available"
exit 1   
fi 
else
echo "usage : $usage"
fi

Please help me out to remove the error while inserting record into table.
Thanks in advance.

The script looks okay. Change the set command to "set -vx", move it to right below the shebang, execute the script and paste the command + the output + the diagnostic messages over here.

Also, this chunk of code -

select count (*) into v_cnt from TBL1;
if v_cnt >= 0 then

is unnecessary. Your table is the infrastructure; and you don't check for infrastructure exceptions. If it is missing, then you have a much bigger problem.

tyler_durden

Thanks for the reply and your time.
I got the problem,

DATE=`echo $line | cut -d ";" -f 2`
echo "Date is : $DATE"			
DATEGEN==`echo $DATE | cut -c1-8`
echo "Generated Date is : $DATEGEN"

and here is the output,

Date is : 20100913115432
Generated Date is : =20100913

I am getting incorrect value as (=20100913) in DATEGEN variable, hence the error message.
How can I remove it. Please help me out.

---------- Post updated at 01:37 AM ---------- Previous update was at 12:40 AM ----------

Still I am facing Column not allowed here error message from oracle database.
Here is the output,

Date is : 20100913115432
Migration Date is : 20100913
Header record is correct
Incorrect DETAIL record fields
Incorrect DETAIL record fields
+ + sqlplus bios/bios@BIOS
+ 0<<
set head off
set feed off
set serveroutput on

INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE($EMPBDATE,'YYYY-MM-DD'));
/
INS_REC=
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 20 08:33:00 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9                                 TO_DATE (20100913,"YYYY-MM-DD")
                                                    *
ERROR at line 8:
ORA-00984: column not allowed here


SQL>                            TO_DATE (20100913,"YYYY-MM-DD")
                                                    *
ERROR at line 8:
ORA-00984: column not allowed here


SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
+ + grep -e SP2- -e ORA-
+ echo
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 20 08:33:00 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Please help me out.

There is a bug in ksh process substitution. Change

$(...)

to

`...`

although the former is preferred.

If you will not take my advice to get off <<, you can at least change to:

tee /tmp/mySQL <<! | sqlplus . . .

so you know what your input looks like, for debugging.

Thanks for reply,

Ichanged the code as,

INS_REC=$(tee /tmp/mySQL <<! |sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
INSERT INTO....
/
EOF)

But the record is not getting inserted into the table.
Could you please provide correct way to do it. Please reply back with Inser_Record() whole function as your sugession.
I already provided most of the code of my script.
Thanks in advance.

Yes, I don't understand why a poster says "My project must be in KSH!" UNLESS it is homework or classwork.

As durden_tyler concurs, there are better ways to work with DB applications than command line shell languages.

I greatly enjoy reading all the creative solutions and debugging, but the fact of the matter is that using a more suitable API to the database, such as PHP or Perl libs/wrappers are best for queries, debugging, array management, row and field selection, etc.

That is why I don't understand why the solution "must be in KSH" unless the project is academic in nature.

Poonamol, why does this project "have to be in KSH"?

You have the SQL you generated in the tee file, now what is the first defect in that SQL? Usually it is a missing quote around a constant or similar.

Thanks a lot to all of you for your time and sugessions.
Now I got the solution of my problem, Here is shell script part of code change

Code:

DATE_FORMAT="YYYYMMDD"
DATE_FORMAT=`echo "'"$DATE_FORMAT"'"`

EMPNAME=`echo "'"$EMPNAME"'"`
EMPID=`echo "'"$EMPID"'"`
DATEGEN=`echo "'"$DATEGEN"'"`

Using above syntax I am able to insert records into my table as,

Code:

INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,$DATEFORMAT));

:slight_smile: :slight_smile: :slight_smile:

If you put the single quotes in the INSERT statement where the values are not numbers, but strings or cast from strings, then you can use unquoted values inside then environment/shell variables; EMPNAME, EMPID, DATEGEN, DATEFORMAT

You are just generating a string of vlaid SQL commands with constants, but valid SQL string constants need single quotes, one way or another.