Shell Script - SQL Post: 302122915

Sponsored Content

Top Forums Shell Programming and Scripting Shell Script - SQL Post 302122915 by bhush782003 on Friday 22nd of June 2007 02:58:46 AM

06-22-2007

Registered User

Shell Script - SQL

Guys,Please look at a simple shell which validates presence of a customer table in OASIS schema. SQL Query for validation is wrong(Table name in quotes should not have schema name OASIS.). And shell is working correctly (with exit 1) as it will never get count=1 with this query.

But it is failing today in our application.and it was not failing in last 6 months with the same code.(This shell is running as host - concurrent program in oracle applications ERP) . Any suggetions/thoughts.

Thanks and Regards,
Bhushan

#!/bin/sh
# Parameter Name Description
# ------------ --------------------
# $1 = Ora apps/user ID
# $2 = User id
# $3 = User Name
# $4 = Request Id

ORA_USERPASS=${1}
#----------------------------------------------#
Validate Database table exists or not
#----------------------------------------------#

TP_PASS=`sqlplus -s /nolog <<end_stmt1
set heading off
set feedback off
set verify off
set termout off
set pages 0
connect $ORA_USERPASS
select count(*)
from all_objects
where object_name = 'OASIS.CUSTOMER_STAGE_TABLE'
and owner='OASISAR'
and status='VALID';
end_stmt1`

TP_PASS=`echo $TP_PASS|cut -d" " -f2`

if ! [ $TP_PASS = 1 ]
then
echo " Staging table OASIS.CUSTOMER_STAGE_TABLE is not present in the schema OASIS" >> $OUTPUT
ERROR_FLAG="Y"
echo " " >> $OUTPUT
exit 1
fi

bhush782003

View Public Profile for bhush782003

Find all posts by bhush782003

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script and sql

Hi, I have an issue. There is a file that has got some values seperated by commas. I have to write a shell script that would extrct these values and insert them into a table. How can this be done Thanx in advance

2. Shell Programming and Scripting

SQL in shell script

Hi, I am able to insert the values into a database table from a flat file using SQL loader. Now can I do the same using only shell script.Can this be done without using sql loader and only with shell scripting(ie use of awk). Please put some light on this. Thanks in advance

3. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert...

4. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given...

5. Shell Programming and Scripting

How to grep sql error in shell script and exit the script?

I need help in the following script. I want to grep the sql errors insert into the error table and exit the shell script if there is any error, otherwise keep running the scripts. Here is my script #!/bin/csh -f source .orapass set user = $USER set pass = $PASS cd /opt/data/scripts echo...

6. Shell Programming and Scripting

LIKE in SQL shell script

sqlplus -s <<__END__ ${USER}/${PASS}@${DB} WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK WHENEVER OSERROR EXIT FAILURE ROLLBACK SET ECHO ON SET TIMING ON SET SERVEROUTPUT ON SIZE 1000000 select count(*) from table where start LIKE '10-JUN-10'; EXIT __END__ result=$? echo $resultThis gives...

7. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script...

8. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that...

9. UNIX for Dummies Questions & Answers

Help with SQL Shell script

Hi, I need to excute two .sql scripts sequentially within file.sh sqlplus user/pass@db1 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy1.sql THEN sqlplus user/pass@db2 @/opt/Infor/Outbound_Marketing/7.2.2/custom/iadeploy3.sql Thanks, Soph

10. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash ....

LEARN ABOUT DEBIAN

alzabo::schema

Alzabo::Schema(3pm)					User Contributed Perl Documentation				       Alzabo::Schema(3pm)

NAME

       Alzabo::Schema - Schema objects

SYNOPSIS

	 use base qw(Alzabo::Schema);

DESCRIPTION

       This is the base class for schema objects..

METHODS

       name

       Returns a string containing the name of the schema.

       table ($name)

       Returns an "Alzabo::Table" object representing the specified table.

       An "Alzabo::Exception::Params" exception is throws if the schema does not contain the table.

       tables (@optional_list)

       If no arguments are given, this method returns a list of all "Alzabo::Table" objects in the schema, or in a scalar context the number of
       such tables.  If one or more arguments are given, returns a list of table objects with those names, in the same order given (or the number
       of such tables in a scalar context, but this isn't terribly useful).

       An "Alzabo::Exception::Params" exception is throws if the schema does not contain one or more of the specified tables.

       has_table ($name)

       Returns a boolean value indicating whether the table exists in the schema.

       begin_work

       Starts a transaction.  Calls to this function may be nested and it will be handled properly.

       rollback

       Rollback a transaction.

       commit

       Finishes a transaction with a commit.  If you make multiple calls to "begin_work()", make sure to call this method the same number of
       times.

       run_in_transaction ( sub { code... } )

       This method takes a subroutine reference and wraps it in a transaction.

       It will preserve the context of the caller and returns whatever the wrapped code would have returned.

       driver

       Returns the "Alzabo::Driver" object for the schema.

       rules

       Returns the "Alzabo::RDBMSRules" object for the schema.

       sqlmaker

       Returns the "Alzabo::SQLMaker" object for the schema.

AUTHOR

       Dave Rolsky, <autarch@urth.org>

perl v5.8.8							    2007-12-23						       Alzabo::Schema(3pm)