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
