Visit Our UNIX and Linux User Community


Assign variable for INSERT INTO statement


 
Thread Tools Search this Thread
Top Forums Programming Assign variable for INSERT INTO statement
# 1  
Old 10-18-2010
Question 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,
Code:
 
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.

Code:
 
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,
Code:
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.

Last edited by Poonamol; 10-18-2010 at 05:15 AM.. Reason: subject is changed
# 2  
Old 10-18-2010
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).
# 3  
Old 10-18-2010
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.
# 4  
Old 10-18-2010
Here's a very short testcase that demonstrates a technique of passing shell variables to Oracle SQL -

Code:
$
$
$ # 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
These 4 Users Gave Thanks to durden_tyler For This Post:
# 5  
Old 10-19-2010
Thanks for the information and your time.

still I am facing error from below code,
Code:
INSERT INTO BIOS_TRANSCODING ( EMPNAME,EMPID,EMPBDATE)
VALUES('$EMPNAME','$EMPID',TO_DATE('$EMPBDATE','YYYY-MM-DD'));

error is,
Code:
 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.

Last edited by Poonamol; 10-19-2010 at 04:33 AM.. Reason: spell correction
# 6  
Old 10-19-2010
Post the contents of your entire script over here.

Quote:
...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. ...
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
# 7  
Old 10-19-2010
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:
Code:
echo '...'"$( whatever )"'...'| sqlplus . . .

then go subshell:
Code:
(
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.

Previous Thread | Next Thread
Test Your Knowledge in Computers #491
Difficulty: Easy
In general, interpreted languages are slower than compiled languages.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How can I assign awk's variable to shell script's variable?

I have the following script, and I want to assign the output ($10 and $5) from awk to N and L: grdinfo data.grd | awk '{print $10,$5}'| read N L output from gridinfo data.grd is: data.grd 50 100 41 82 -2796 6944 0.016 0.016 3001 2461. where N and L is suppose to be 3001 and 100. I use... (8 Replies)
Discussion started by: geomarine
8 Replies

2. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

3. Shell Programming and Scripting

How to insert a space and assign the value to another variable?

Hello, I need to insert a space between 2 strings. I used many techniques and all of them worked but when I assign the value to another variable then the inserted space vanishes, strange! Please advise. # dat=`date |awk '{print $2,$3}'` # echo $dat Nov 3 The above is perfectly fine. Now... (4 Replies)
Discussion started by: prvnrk
4 Replies

4. Shell Programming and Scripting

How to insert date in a statement?

Hi Guys, Can somebody help me in inserting today's DATE format (20110709) in my awk statement. I have a script but its not working. inputfile.txt: 269,1,0,AAA,430 231,2,0,BBB,430 252,3,0,CCC,430 214,4,0,DDD,430 script.sh #!/bin/bash DATE="`date +%Y%m%d`" cd /var/opt/ (8 Replies)
Discussion started by: pinpe
8 Replies

5. Shell Programming and Scripting

How to insert numbers to a in between statement

Hi Guys, I want to create a shell script that will give me the output below. I want to insert the numbers from the input file to my url addresses below. And from the numbers below, I want to separate the last digit with a period (i.e. from 222222222222 to 22222222222.2). Appreciate any help.... (14 Replies)
Discussion started by: pinpe
14 Replies

6. Shell Programming and Scripting

assign awk's variable to shell script's variable?

Dear All, we have a command output which looks like : Total 200 queues in 30000 Kbytes and we're going to get "200" and "30000" for further process. currently, i'm using : numA=echo $OUTPUT | awk '{print $2}' numB=echo $OUTPUT | awk '{print $5}' my question is : can I use just one... (4 Replies)
Discussion started by: tiger2000
4 Replies

7. Shell Programming and Scripting

How can i assign an select statement into a variable?

I am trying to assign an select statement into a variable. Can someone hel me with this. example : a='select * from dual' echo $a should give me select * from dual But this is not working. I trying with \ before * and quotes too. (1 Reply)
Discussion started by: rdhanek
1 Replies

8. Programming

Dynamic Insert statement

I have a form , where i will put the values to a table. I wrote a insert statement for the same. Table structure is ename | character varying(30) | eadd | character varying(30) | eid | integer | sal | integer In the statements, i don't... (1 Reply)
Discussion started by: pritish.sas
1 Replies

9. Shell Programming and Scripting

error in insert statement

hi, When i try to run the code below, i get the following error "ksh: syntax error: `(' unexpected" i am not able to figure it out. Can anyone help me? Code: (2 Replies)
Discussion started by: ragavhere
2 Replies

10. Shell Programming and Scripting

How is use sselect statement o/p in insert statement.

Hi All, I am using Unix ksh script. I need to insert values to a table using the o/p from a slelect statement. Can anybody Help! My script looks like tihs. ---`sqlplus -s username/password@SID << EOF set heading off set feedback off set pages 0 insert into ${TB_NAME}_D... (2 Replies)
Discussion started by: nkosaraju
2 Replies

Featured Tech Videos