Need to display Oracle table name when tables is created from SQL , executed from UNIX sheel script

[KVS Raju]

Hi Friends,

I need to display a table name in oracle, when a table is created.

Example: If I created a table with name "emp".
After executing the "created table emp ...." command.
I need to display message
"emp table created".
If it is failed, I need to display message "emp table not created" like this from unix shell script

One thing Iam clear that I can check the user_tables/User_objects to check the table is created or not.

I need some idea/sample shell scripts to impliment this.


Thanks in advance.

[Simon Hargrave]

You want to know "on the fly" or you want to check "at a later date" whether it worked or not?

If on the fly, then you will get ORA- errors when executing the command. Your script can grep for ORA-, and display the output in any way you need.

If you want to do this at a later date, you can grep for that ORA- errors in the databases alert log.

If these don't answer the question then I think we need more detail of what you're trying to do.

[Sanjay Kumar Suri]

Does following help:

#!/usr/bin/ksh
OUT1=out1.txt
sqlplus -s / > $OUTFILE < /dev/null

if [ $? -ne 0 ]
then
echo "emp table not created"
else
echo "emp table created"
fi

sks

[Manish Srivastava]

Hi,

I am not aware of database table creations. But I am sure that they must have an exit value associated with the SQL. So am assuming that "create table emp..."
will have a return value.

create table emp..

RET_VAL=$?

if [ $? = ]
then
echo "Table emp created"
else
echo "Error creating table emp"
fi

manish

[Nicolas Dumeige]

Hello,

If you want you session to end when the table creation fails, put those line at the beginning of your script :

WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 1

If you have various table to create, you could make one file per table and loop on the sql files. To do so use some generic Sql script executor.

One example below :

#!/bin/ksh

. ~/init/ksh/env_var.ksh
TMP=/tmp/sql_launcher_$$

[ $# -lt 1 ] && { echo "USAGE : `basename $0` script.sql [arguments] " ; exit 1 ; }

sqlplus -s $ORA_LOGIN@$ORA_BASE/$ORA_PASS << EOF >$TMP 2>&1
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
$@
EOF
if [ $? -ne 0 ] ; then
echo "! `date +'%D %T'` `basename $0` : Error on $1 !"
cat $TMP
rm -f $TMP
exit 1
else
rm -f $TMP
exit 0
fi

Cheers

Nicolas