Hi to all,
This is first posting in this forum. I quite new to this KSH....i guess i ll have some fun...
Well, I have two indivdual korn shell scripts. i m calling PL/SQL stored procedure from korn shell script. It works absolutely fine. Once its completed the log is updated and data is loaded into tables. These two loades the data into two different tables. Then have dependency.
So i have to run A.ksh and Later B.ksh. If i run B.ksh first then data will not be loaded since it has dependency on A.ksh.
So i was trying to merge into one korn shell script. First run 1st procedurer if successfully completed then run 2nd procedure. if 1st procedure is failed then dont run 2nd procedure.
So how do i accomplish this in korn shell script ??? Any idea guys
I'm assuming that you are using sqlplus... You have not specified any details.
Check the sqlplus manual... Since it is very limited, you would need to parse its output.
I would guess that PL/SQL might be the right choice... but you need to check it.
In a brief: According to POSIX application should return ($?) a value of 0 if it was finished with a success. SQLPLUS can perform multiple operations and you won't get the result from a single operation. You might want to try perl or python or java.
General idea: Korn shell should be used for simple things (although it can perform complex things either). If you do anything that is complex then you need to use some real programming language (perl, python, java, c++,...).
At work we have many scripts (ksh) that integrate with DB2 and perform many complex DB operations, way beyond simply running SQL scripts.
If you do want to perform complex DB operations from shell it is good to break the code down into specific operations, such as: -
Connect to the database
set schema
update table
delete
insert
disconnect
and so on. Each of these actions are performed by specific functions written in a common library file that is dotted into scripts running under sudo. Expected return codes are passed to each function so they can fail if a code is out of range.
One script for example connects to a database with over 200 tables and verifies: -
No tables have changed their name
No field names have changed
No data types have changed
No field sizes have changed.
As a manual exercise this was a day's work for the testers and very error prone. Now it is performed in a few minutes with any changes presented in a report.
So don't let any one tell you you cannot perform complex operations in shell. At the last line count the tool suite had almost 2,000,000 lines, albeit a lot of that standing data.
What is here to understand? He needs an interaction with the database - and it might be better way to do it within a single transaction (or several related transactions if DDL is performed). Since it might be difficult in sqlplus - you need another option. Perl's DBI might be a choice. I believe that a PL/SQL code (might be anonymous) might be a better choice.
I have written that you should not (note the difference between "should not" and "cannot") use korn shell for complex code. Not that it cannot be used for complex things - it can do artificial inteligence if you want it to. However for complex things other choices (perl, python, java, ...) are usually much more optimal.
If you don't agree then I wish you luck in writing 3D accelerated games in ksh
Complex ksh code might be difficult to read and maintain. Code might be distributed across thousands of scripts executed as functions named like a_my_function which is in fact "${x}_${y}_${z}". There is no separation between modules and people tend to use variables without declaration (try running "set -u"). You don't have references, objects, streams, inheritance, overloading, debugging, libraries, private methods, multi-threading, ...
You can find several games written in shell... try reading that mess. Then compare it to other game sources.
As for the original question, you wanted to:
Run some script, let say A.sql
Check if A.sql failed
Run B.sql if A.sql did not fail... probably rollback the changes if A.sql failed
In shell you would need to spool the output from sqlplus, grep it and quess what might fail (ex. grep the output by "error" string and be lucky to have there nothig like "create table my_errors(error char(10))") with checking the return code and then execute the second script.
PL/SQL script might try to work in a single transaction and have "rollback" in the exception handler. If you use any DDL there then flashback mechanism might be used.
