I'm looking for a pure sql Cum Shell solution to the problem described below. I'm a beginner in shell and SQL scripting and have no knowledge of PL/SQL. Therefore i'm trying to limit my focus to SQL and Shell Scripting.
I'm using an Oracle 10g database and a Sun Solaris machine
I've figured out the following process to create multiple tables using a shell script :
[code]
#! /bin/bash
for i in *
do
sqlplus username/password@db_name<<EOF
create table1 test select account_no, balance from records_all;
EOF -- create the table and insert records
OUTPUT=$(sqlplus -silent username/password@db_name << DONE
set pages 0 feedback off
select count(1) from test1;
DONE)
echo " Number of records in the table is:$OUTPUT " -- count the number of records in the created table
if [$OUTPUT -eq 2077613 ]; then -- check if table creation is successful
echo "Table creation is successful"
echo "Table has successfully stored $OUTPUT records"
exit 0
else
echo "Un successful in creating the table"
fi
done
[code]
My question is :
How do i go about changing the name of the table, each time the prompt enter's the for loop ? i.e., The first time it creates "table1". I want to create table2, table3 and so on upto table10.
Appreciate if you can guide me with the syntax.
Regards
kris
ANSWER: Hi Kris,
Try the following -
[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 -- create the table and insert records
OUTPUT=$(sqlplus -silent username/password@db_name << DONE
set pages 0 feedback off
select count(1) from $tname$i;
DONE)
echo " Number of records in the table is:$OUTPUT " -- count the number of records in the created table
if [$OUTPUT -eq 2077613 ]; then -- check if table creation is successful
echo "Table creation is successful"
echo "Table has successfully stored $OUTPUT records"
exit 0
else
echo "Un successful in creating the table"
fi
done
[code]
I hope this solution works.
Regards
Suchitra
---------- FOLLOW-UP ----------
QUESTION: Hello Suchi,
Thanks for responding back. I get an unexpected "end of file" error while trying to execute it in the bash / ksh shell for the following piece of code:
[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
[\code]
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
kris
Answer Hi Kris,
Since I do not have access to any Unix m/c, I cannot try and test this code.
But I suggest you do the following changes and test it -
1) Instead of bash, use ksh
2) Replace all EOF with EOSQL
If this still does not work, try just displaying the variable $table$i (instead of the sqlplus code).
