:

Execute a script in SQLPlus

Execute a script in SQLPlus

To put it simply, you execute SQL from the command line using the @ command, for example:

SQL> @script01.sql

However, there are other areas which need to be considered so let’s take a look at them:

  1. Does your script call other scripts from within it, like a harness file?
  2. Have you copied the contents of your script for a text editor or email to your basic text editor when saving it as as .sql file
  3. Do you require any SQLplus environment settings to be configured, such as SET DEFINE OFF

1. Run Script in SQL Plus

This is quite easy to deal with. You have a couple of options when you call multiple sql files from one main one, which is usually called a harness script because it calls all the other SQL Plus files. The short answer to this is that if you have all of the scripts in the same folder or directory as the harness file, you should use the double ‘at-sign’ call (@@) to execute each sql plus script. An example of this is below.

The reason for doing it this way is so that you could actually be calling the harness file from a different folder but the @@ symbols will ensure that the scripts being called are looked for in the same directory as the harness file, regardless of where the harness file is called from. If you only use a single ‘at-sign’ to call your sqlplus scripts from within the harness file the files have to be in the location where you are calling the harness from.

2. Creating a SQL File

Here we are concerned with the actual execution of your script in SQL plus. The short story is, if you have copied your sql commands from somewhere like an email then there may be characters which are not interpretted correctly in a plain text file. One such example is a single quote which often becomes unreadable and causes issues when executing the script. Another example is given below.

3. SQL Plus Environment Settings

With regard to point 3, your environment settings in SQL Plus, sometimes you need to change them and sometimes not. For example, if you are inserting a row into a table like this:

INSERT INTO TABA VALUES('this is a test & it will get stuck');

And you have SET DEFINE ON (default) set then the script will hang because it will be expecting a parameter to be defined where you have used the ampersand symbol. To get around this you would change the session variable and SET DEFINE OFF. This means that it won’t expect there to be a variable and will treat the & symbol as a literal. The downside to this is if you genuinely are wanting to use a variable you need to SET DEFINE ON again beforehand. If you defined a variable at the start of the script you can SET DEFINE ON and OFF as many times as you like without losing the variable definition. A full list of all the SQL Plus settings available can be found in the Oracle documentation.

Execute Script in SQL Plus Example

A real world example of how attempting to run a script in sqlplus can sometimes be a little more challenging that you first thought.

The Problem

This morning I received an email from one of our developers saying the following:

“I’ve had this error twice when attempting to run SQL scripts from a harness in SQLPlus: Error 45 initializing SQL*Plus Internal error. This is in a Migration harness script that is running three migrations. It ran the first two and failed before/as the third was called:

@@00_script01_harness.sql
@@00_script02_harness.sql
@@00_script03_harness.sql

This happened in my deletes harness and the insert one. The only connection is that the scripts are running for a long time (10-12 hours). I’ve checked meta-link and google, and can’t find anything useful. Retrying (i.e. running the third script on its own) works.”

The Cause

It turned out that there were end-of-line characters (EOL) within the scripts where it was failing. This is common when you have copied and pasted from one editor to a notepad. For example, you have an email and you copy from the email into a text file which is going to be run using SQL Plus against your database. Characters, such as single quotation marks can sometimes become invalid characters in the text file. The same applies to end of line characters, which might not be wanted or even seen in the text file but are there nonetheless.

The Solution

To remove the end-of-line characters from the harness file and then it worked without a problem. If you are copying from one place to another, such as from an email, make sure that you convert the text to plain text and not allow it to be rich text or html.

Like it, share it...

Category: SQL Plus


Related Posts

Comments (2)

Trackback URL | Comments RSS Feed

  1. Luis says:

    Yo romplace los \r por \r\n de mi script y me funciono la instalación en Linux.

Leave a Reply

Your email address will not be published. Required fields are marked *