Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

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

KVS Raju
Advisor

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

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.
Time and Tide wait for none
4 REPLIES
Simon Hargrave
Honored Contributor

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

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
Honored Contributor

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

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Manish Srivastava
Trusted Contributor

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

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
Esteemed Contributor

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

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
All different, all Unix