Re: SQLPLUS Question

From: Tim X <timx_at_nospam.dev.null>
Date: Sun, 17 Jan 2010 12:40:19 +1100
Message-ID: <87r5ppbjb0.fsf_at_lion.rapttech.com.au>

Jeremy <jeremy0505_at_gmail.com> writes:

> Hi, real simple question and one I suspect the answer to which is "no".
>
> Can you create sqlplus scripts with "conditions" such that if for
> example a SQL statement returns a particular value or error condition
> then path A or path B is followed?

It depends a little on exactly what you mean.

If you are asking if a single specific sql script can have conditionals inside it that react based on the results from some SQL, either DDL or DML, and as a consequence perform additional SQL /within the same session/, the short answer is no.

Generally, thre are two possible solutions you can use, both have some drawbacks.

1. You can wrap your calls to sqlplus inside a shell script, such as a bash script. The script can then capture the results from your sql and use OS level commands i.e. bash built-ins, awk, sed, etc to process that information and possibly execute another sqlplus session and script based on what it finds. For very simple SQL, you can use shell 'here documents', which will at least mean all your code is in one file. However, here docs can get difficult to manage/maintain once they reach a certain level of complexity and it is difficult to get any code reuse. Alternatively, the sql is put in its own files, which makes it a bit easier to maintain, but now you have multiple file dependencies, which can be a source of bugs or more complex maintenance. A way to simplify things a bit is to actually put the SQL in PL/SQL procedures/functions and then just have small bits of SQL in your shell script that is essentially a begin/end block that calls the pl/sql

Some of the limitations of this approach are

1. It cannot be used to do things like commits and rollbacks of the previous sql because all the SQL runs in separate sessions.
2. It can be a little tricky to manage sensitive data, such as passwords using this approach.
3. The scripts can begin to get difficult to manage quite quickly. once you pass a certain level of complexity.

These days, my preferred approach is to use perl and the perl Oracle DBD driver for perls DBI infrastructure. Going this route, you get a lot more flexibility and have it all backed up with a far more powerful scripting language than just a relatively simple shell script. The main advantage are that all your sql can be executed inside a single oracle session, so you can manage sessions and do rollbacks/commits based on the results from other SQL. You can also call pl/sql procedures, retrieve ref cursors and process them etc and you have access to all the CPAN modules for ding things like generating reports, parsing data such as dates, perl regexp, etc

The main disadvantages to this approach are

1. The perl DBI connection specification can be a little sensitive to Oracle site changes. There are a number of different ways to specify the connect string. Some methods appear to be more sensitive to site changes than others and it can depend on whether your using a straight TNS Listner config or something like Oracle names etc.
2. The Perl that comes bundled with Oracle tends to be quite old compared to the version that you would normally find on a current OS distribution.
3. Perl's ability to support multiple styles/ways of ding the same thing can lead to maintenance issues if you have a large team and everyone has their own perl 'style'.

Many of these limitations can be avoided through the use of Perl modules. For example, I setup a perl OracleDB connection module. Scripts that need to coonnect to our databases use this module. The DBAs control who can access this module through a combination of file system controls and a perl script registry module. i.e. the perl script must have the right access permissions to load the module AND the name of that script must be in a registry file managed by the DBAs (the script name is the full pathname). This allows developers to use the scripts and even update/modify them, but they do not know the passwords and cannot obtain the passwords unless they can access files managed by the DBAs. It also means that if we upgrade perl, Oracle etc, only one module may need to be updated and all the scripts will continue to work.

We don't tend to use the perl that comes with Oracle. Instead, we use the version that comes with the OS and build the Oracle DBD driver ourselves. This allows us to run the latest code, so if we run into a perl bug, we can report it and hopefully get action. Using the older Oracle bundled versions, you tend to just get the standard "upgrade to the latest version" for any bugs reported.

We also encourage people to use PL/SQL procedures/functions to modularise SQL rather than have SQL coded into the perl scripts. While this isn't always practicle, it does mean for frequent/common SQL tasks, we have one central version to maintain rather than multiple copies in various scripts.

Reading between the lines of your question, I would suggest you really want to go the Perl route rather than the shell script route.

HTH Tim

-- 
tcross (at) rapttech dot com dot au

Received on Sat Jan 16 2010 - 19:40:19 CST