Login or Register to Ask a Question and Join Our Community


Programming

Assign variable for INSERT INTO statement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Assign variable for INSERT INTO statement
# 15  
Old 10-21-2010
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.

Quote:
Originally Posted by Poonamol
Thanks for reply,

Ichanged the code as,
Code:
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.
# 16  
Old 10-25-2010
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:
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:
Code:
INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,$DATEFORMAT));

Smilie Smilie Smilie
Last edited by radoulov; 10-25-2010 at 04:37 AM.. Reason: Code tags, please!
# 17  
Old 10-25-2010
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

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
Login or Register to Ask a Question