Difficulty using "execute immediate" in shell

novice82 · 10-11-2009, 09:21 PM

Hello members,

I get an unexpected "end of file" error while trying to execute the following piece of code in the bash / ksh shell. I'm assuming the problem is with using the "execute immediate statement"

Code:

#! /bin/bash

tname="table"

for i in *
do
sqlstr="create table $tname$i as select account_no, balance from records_all"
sqlplus username/password@db_name<<EOF
  execute immediate $sqlstr;
EOF

ksh Error:
+ tname=table
try.ksh[7]: syntax error at line 10 : `<<' unmatched

bash Error:
tname=table
try.sh: syntax error at line 15: `end of file' unexpected

Appreciate your guidance with the correct syntax.

kris

lutusp · 10-11-2009, 09:44 PM

Quote:

Originally Posted by novice82

Hello members,

I get an unexpected "end of file" error while trying to execute the following piece of code in the bash / ksh shell. I'm assuming the problem is with using the "execute immediate statement"

Code:

#! /bin/bash

tname="table"

for i in *
do
sqlstr="create table $tname$i as select account_no, balance from records_all"
sqlplus username/password@db_name<<EOF
  execute immediate $sqlstr;
EOF

ksh Error:
+ tname=table
try.ksh[7]: syntax error at line 10 : `<<' unmatched

bash Error:
tname=table
try.sh: syntax error at line 15: `end of file' unexpected

Appreciate your guidance with the correct syntax.

kris

It's not clear whether your script is identical to the original, but one of the few ways this error can come up is if the second EOF is not against the left margin.

On a second look, I see there is a "do" in your script but no "done". Something is seriously wrong with this script. Maybe you should abandon this script and just tell us what the script is supposed to do. Either that you you didn't post the entire script, in which case there's no point in trying to analyze it.

chrism01 · 10-11-2009, 11:36 PM

Why not use the techniques we discussed in your previous qn http://www.linuxquestions.org/questi...hlight=sqlplus

novice82 · 10-12-2009, 12:09 AM

Thanks folks. The following code snippet seems to work for me:

Code:

#! /bin/ksh
tname="table"
for i in temp*
do
    OUTPUT=$(sqlplus -silent username/password@db_name << OK
    set pages 0 feedback off
    create table $tname$i as select account_no, balance from records_all;
OK)
echo " $tname$i table creation is successful "
done

Thanks again!

pradeepparamasivam · 01-16-2010, 01:19 AM

please help me

how to execute .sql file for postgresql database in ubuntu environment without restoring

pradeepparamasivam · 01-16-2010, 01:53 AM

how to give full rights to user for creating and restore database in postgresql