Defining and Using Variables in .sql scripts

Question

I'm trying to define variables in a .sql file so that I can use them in my sql statments. But I'm so confused about how these variables are defined, and I can't find even one good online resource that has explained all this in clear manner. I follow the recommendations in different sources and I keep getting compile errors in 'Oracle SQL Developer'. Here are my 2 problems:

Problem 1: It seems that you can define variables in the following 2 ways. What is the difference between these 2 ways, and can I use both?

define first_name = Joe;
select * from customer where name = '&firstname';

or

variable first_name CHAR;
exec :first_name:= 'Joe';
select * from customer where name = :firstname;

Problem 2: In the first method (meaning using define command), can I define a variable of type number and if so, how can I do it?

Answer 1

You can define NUMBER variables no problem:

SQL> VARIABLE myNum NUMBER;
SQL> EXEC :myNum := 123.456;

PL/SQL procedure successfully completed.

SQL> print myNum;

     MYNUM                                        
----------                                        
   123.456                                        

Lots of other types are supported as well. Here's the USAGE help text from the VARIABLE command:

Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

If you type VARIABLE (or just VAR) without anything else, SQL*Plus will list all your variables with their values.

Addendum: contrast the two variable assignment styles in the original question.

When you do this...

define first_name = Joe
select * from customer where name = '&first_name';

... It's more like a #define in C/C++. You can't treat it like a variable; it's just text that gets pasted every time SQL*Plus sees &first_name.

When you do this...

variable first_name CHAR;
exec :first_name:= 'Joe';
select * from customer where name = :first_name;

You're creating a real variable that you can manipulate.

Note that if you use CHAR in the definition (without a size), the first assignment will determine its size and you can't make it longer after that. If you define it as CHAR(50) it'll always be 50 characters long and padded with spaces. That can get confusing so I'd recommend VARCHAR2 in most cases for strings.

Answer 2

- Answer to problem 1: The first type of variables is called Substitution Variables which only works in SQL*Plus and SQL Developer. So when you supply a substituation vaiable in any SQL statement, SQL*Plus replaces the variable with it's value. It has nothing to do with Oracle server or performance. The example you gave will be translated to the follwing BEFORE sending it to the Oracle database server:

select * from customer where name = 'Joe';

The second part is called bind variables which is not exclusive to SQL*Plus or SQL Developer as you can use it, for example, in a Java application (or other languages) connecting to Oracle. Bind variables provide better performance when you run the same statement many times as you always submit the statement as it is (without rewriting). Then the variables get evaluated at the database level. For example, let's say you've changed the value of "first_name" to "Mark":

exec :first_name:= 'Mark';

The same statement with the bind variable is submitted to Oracle database server. The database uses cached area to find that the same statement was run perviously and uses it again. Then the database uses the variables values. This means the database will not need to re-parse and re-calculate the best execution plan for the same statement. But this is not the case with the first type(Substitution Variables).

- Answer to problem 2: No you can't, because as I said above, all what SQL*Plus or SQL Developer do is just rewriting the statement replacing the variable name with it's value. It doesn't know anything about it's type. Only text replacement is done here.

You can find more details here: http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php

Answer 3

In Oracle, you can define variables as follows:

CREATE OR REPLACE FUNCTION MyFunction()
AS

    my_number     NUMBER (20, 0);
    first_name    VARCHAR2(256);

BEGIN
    first_name := 'Joe';
    select * from customer where name = first_name;

END

Is that what you're looking for?