Wednesday, October 25, 2006

How to Use Variables in PL/SQL or SQLPlus Scripts

I am researching how to set up my SQLPlus script to be able to output a text report using a generic script that reads in a variable.

I want to set variable on the command line and then have the script reference this throughout, giving me customized counts based on the variable entered. The results should output to a text file that can then be used as the actual report. I am familiar with how to do this in a PL/SQL script but not in a regular SQL script that I would use in SQL Plus.

The following link helped me with the setting up of my variable.

Detail on how to use variables in SQLPlus

Specifically here were the instructions:

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

1. The DEFINE command sets an explicit value:

define myv = 'King'

This creates a character variable "myv" set to the value "King".

2. The ACCEPT command:

accept myv char prompt 'Enter a last name: '

prompts you for a value and creates a character variable "myv" set to the text you enter.

3. Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

select first_name from employees where last_name = '&&myuser';

If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

column last_name new_value mynv
select last_name from employees where employee_id = 100;

This creates a substitution variable "mynv" set to the value in the "last_name" column.

When outputting to a file, I like to use include the date in the file name:

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &
-- my report goes here
select last_name from employees;
spool off

Be sure to use the double period after the variable name in your file name:

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

define mycity = Melbourne
spool &mycity..log

is the same as:

spool Melbourne.log

Use the variable that you set like this:

define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;

Or pass in the variable on the command line like this:

You can pass parameters on the command line to a SQL*Plus script:

sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

set verify off
select employee_id from &1 where last_name = '&2';

That handled my main concern and gives me a nice text output file that uses my generic script with a variable.

The next point I sought to resolve was how to have my text output be in a delimited format as I plan to read this from an Excel file.

Here is what I found to do this:

--sets sqlplus so output will be tab-delimited
set colsep ' ' /* that's a tab */
set trimspool on
set newpage none
set underline off

Here is someone else's point of view on how this should be done.
Digital Voice

Instead of either of the above a simple handling would be to hard code into the generic script "|" between the columns. This would give me a standard pipe-delimited output.

That sounds like the easiest method and is what I commonly use.