Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

11.1 Bind Variables

Back
Chapter 8,
you learned about substitution variables. SQL*Plus supports another
type of variable called a bind variable . Unlike
substitution variables, bind variables are real variables, having a
datatype and a size.

Bind variables were created to support the use of PL/SQL in a
SQL*Plus script. They provide a mechanism for returning data from a
PL/SQL block back to SQL*Plus, where that data can be used in
subsequent queries or by other PL/SQL blocks. Example 11-1 provides a simple script showing how a bind
variable can be used.

Example 11-1. Bind variables can be used to transfer data among PL/SQL blocks and SQL queries

--Bind variables can be declared in your SQL*Plus script.
VARIABLE s_table_name varchar2(30)
--Preface a bind variable with a colon to reference it
--in a PL/SQL block.
BEGIN
:s_table_name := 'EMPLOYEE';
END;
/
--Bind variables can even be referenced by SQL queries.
SELECT index_name
FROM user_indexes
WHERE table_name = :s_table_name;
--Bind variables persist until you exit SQL*Plus, so
--they can be referenced by more than one PL/SQL block.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(:s_table_name);
END;
/ The scope of a bind variable is the SQL*Plus session in which it is
defined. Variables defined within a PL/SQL block, on the other hand,
cease to exist once that block has finished executing. Bind variables
are defined one level higher (at the SQL*Plus level), so they can be
referenced by many PL/SQL blocks and queries.

11.1.1 Declaring Bind Variables

You
use the SQL*Plus
VARIABLE
command to declare bind variables. The syntax looks like this:

VAR[IABLE] var_name data_type Bind variable datatypes correspond to database datatypes. Not all
datatypes are supported, but the most commonly used ones are. Example 11-1 has shown one way to declare character
variables:

VARIABLE s_table_name VARCHAR2(30) You can declare numeric variables using the NUMBER datatype:

VAR billing_rate NUMBER When declaring NUMBER bind variables, you can't
specify precision and scale. However, a variable of type NUMBER can
accommodate values of any precision and scale, so the inability to
specify a specific precision and scale presents no problem in
practice.

Other datatypes are supported. REFCURSOR is a useful bind variable
type that you'll read more about later in this
chapter. The new, numeric types (BINARY_FLOAT and BINARY_DOUBLE) are
supported if you are running a version of SQL*Plus corresponding to a
release of the database software that supports those types.

For a complete list of supported, bind variable datatypes, see the section on the VARIABLE command in Appendix A.

In addition to declaring variables, you can use the VARIABLE command
to list all of the variables you have defined. To do that, issue the
command VARIABLE (which may be abbreviated VAR), with no arguments,
as shown in the following example:

SQL> VAR
variable s_table_name
datatype VARCHAR2(30)
variable billing_rate
datatype NUMBER If you are interested in one specific variable, you can specify that
variable's name as an argument to the VARIABLE
command:

SQL> VAR billing_rate
variable billing_rate
datatype NUMBER There is no way to get rid of a variable once you have defined it.

11.1.2 Using Bind Variables and Substitution Variables Together

Bind
variables and substitution variables
don't mesh together well in SQL*Plus. Each was
created for a different purpose, and the two types
can't be used interchangeably. For example, bind
variables can't be used with the ACCEPT command, but
substitution variables can. Substitution variables can be used with
the TTITLE and BTITLE commands that set up page headers and footers,
but bind variables cannot. Bind variables are true variables and can
be passed as arguments to PL/SQL functions and procedures, but
substitution variables cannot. Table 11-1
summarizes the best uses and capabilities of each type of variable.

Table 11-1. Bind variables versus substitution variables

Task	Bind variable	Substitution variable	Comments
Display information to the userthe PROMPT command.
Accept input from the userthe ACCEPT command.
Place information from a query into page headers and footersthe TTITLE and BTITLE commands.
Run a query with user-specified criteria in the WHERE clause.			User input must come through a substitution variable, but you can store the resulting value in a bind variable.
Pass values to a PL/SQL function or procedure.			Substitution variables may be used to pass input arguments as literals.
Return information back from a PL/SQL function or procedure.			Bind variables must be used for OUT and IN OUT arguments.

Each variable type, bind and substitution, exists in its own world,
separate from the other. In fact, you can't even
directly assign values from a bind variable to a substitution
variable, or vice versa. The lines of script shown in Example 11-2, although appearing perfectly reasonable on
the surface, will not work.

Example 11-2. You cannot directly place a bind variable value into a substitution variable

DEFINE my_sub_var = ' '
VARIABLE my_bind_var VARCHAR2(30)
EXECUTE :my_bind_var := 'Donna Gennick'
my_sub_var = my_bind_var This lack of interoperability between variable types can be a source
of frustration when writing scripts. As Table 11-1
shows, you can only use a bind variable for some tasks; for others,
you can only use a substitution variable. Yet SQL*Plus
doesn't let you move values between the two types.
Fortunately, some relatively straightforward incantations let you
work around this problem.

11.1.2.1 From substitution to bind

Putting the
value of a substitution variable
into a bind variable is the easier of the two tasks. Remember that as
SQL*Plus executes your script, any substitution variables are simply
replaced by their contents as each line of code is executed. You can
take advantage of this to place a value into a bind variable. Take a
look at the short script in Example 11-3.

Example 11-3. Assigning a substitution variable value to a bind variable

DEFINE my_sub_var = 'Mykola Leontovych'
VARIABLE my_bind_var VARCHAR2(30)
EXECUTE :my_bind_var := '&my_sub_var';

EXECUTE is
a command that executes one line of PL/SQL code.
When SQL*Plus encounters the EXECUTE command in Example 11-3, it replaces the reference to the substitution
variable with the value of that variable. The command after
substitution, the one that is executed, looks like this:

EXECUTE :my_bind_var := 'Mykola Leontovych';

The EXECUTE command is a SQL*Plus command. What gets sent to the
database is a PL/SQL block:

BEGIN
:my_bind_var := 'Mykola Leontovych';
END;

Because the assignment involves a character string, the substitution
variable must be contained in quotes; otherwise, you would not have a
valid string. If you are working with numeric values, you
shouldn't quote them. Example 11-4
declares a variable of type NUMBER and assigns a value to it.

Example 11-4. Assigning a "numeric" value from a substitution variable to a bind variable

DEFINE my_sub_num = 9
VARIABLE my_bind_num NUMBER
EXECUTE :my_bind_num := &my_sub_num;

The EXECUTE command that SQL*Plus executes from Example 11-4 looks like this:

EXECUTE :my_bind_num := 9;

So, quote your strings, don't quote your numbers,
and remember that substitution is occurring.

The value in the substitution my_sub_num in Example 11-4 is a character string. Substitution variables that you create using the DEFINE command are always character strings. That character string is converted to a true NUMBER when it is assigned to :my_bind_num.

11.1.2.2 From bind to substitution

Taking a value from a bind variable and placing it into a
substitution variable is a more difficult task. What you need to do
is take advantage of SQL*Plus's ability to store the
result of a SELECT statement into a substitution variable.
Let's say you have the following in your script:

DEFINE my_sub_var = ' '
VARIABLE my_bind_var VARCHAR2(35)
EXECUTE :my_bind_var := 'Brighten the corner where you are';

To get the value of the bind variable into the substitution variable,
you need to follow these steps:

Think up a column name.

Execute a COLUMN command for the column name you thought up. Use the
NEW_VALUE clause and specify the substitution variable as the target.

Turn off terminal output by executing a SET TERMOUT OFF command. This
is optional.

Issue a SELECT statement that selects the bind variable from
Oracle's dual table. Use the
column name you thought up in step 1 as the column alias.

Turn terminal output back on.

The SELECT statement will return only one value, but that value will
be a new value for the column in question. The COLUMN command, with
its NEW_VALUE clause, causes this value to be stored in the specified
substitution variable. It's a roundabout solution to
the problem, but when it's all over, the
substitution variable will contain the value from the bind variable.
The important thing is to be sure that the column alias matches the
column name used in the COLUMN command. Example 11-5
demonstrates the technique.

Example 11-5. Assigning a bind variable value to a substitution variable

--Declare one bind variable and one substitution variable.
--Initialize the bind variable to a value.
DEFINE my_sub_var = ' '
VARIABLE my_bind_var VARCHAR2(35)
EXECUTE :my_bind_var := 'Brighten the corner where you are';
--Store the new value of the my_alias column in my_sub_var.
COLUMN my_alias NEW_VALUE my_sub_var
--SELECT the value of the bind variable. SQL*Plus
--will store that value in my_sub_var because of the
--previous COLUMN command.
SET TERMOUT OFF
SELECT :my_bind_var my_alias
FROM dual;
SET TERMOUT ON
--Display the new value of the substitution variable
DEFINE my_sub_var Notice in Example 11-5 that a column alias is used in
the SELECT statement to give the column a name. This same name must
be used in the COLUMN command issued prior to the SELECT. If these
two don't match, then the assignment
won't be made and my_sub_var will
remain blank.

Strictly speaking, it's not necessary to turn the
terminal output off for the SELECT statement. The variable assignment
will still be made, even with the output on. However, if you are
writing a script, you probably won't want the
results of this SELECT to clutter up the display.

11.1.3 Displaying the Contents of a Bind Variable

You can display the contents of a bind variable to a user by using
the PRINT command, or by listing the variable in a SELECT statement.

11.1.3.1 Using the PRINT command

The PRINT command takes a bind variable name as a parameter and
displays the value of that variable. The results look much like the
results you get from a SELECT. Example 11-6
demonstrates.

Example 11-6. PRINTing the value of a bind variable

SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are';
PL/SQL procedure successfully completed.
SQL> PRINT my_bind_var
MY_BIND_VAR
-----------------------------------------------------------------
Brighten the corner where you are The bind variable is treated like a database column, with the
variable name being the default column heading. If you have page
titles defined, they will print as well. You can even use
the

COLUMN command to format the output.
Example 11-7 shows how this works.

Example 11-7. Using the COLUMN command to format bind variable output

SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are';
PL/SQL procedure successfully completed.
SQL> TTITLE LEFT '**********************' SKIP 1 -
> 'A Song by Ina D. Ogdon' SKIP 1 -
> '**********************' SKIP 2
SQL> COLUMN my_bind_var FORMAT A35 HEADING 'My Motto'
SQL> PRINT my_bind_var
**********************
A Song by Ina D. Ogdon
**********************
My Motto
-----------------------------------
Brighten the corner where you are All other formatting options, such as PAGESIZE and LINESIZE, apply
when printing bind variables. You can use the COLUMN
command's NEW_VALUE clause to store the value of a
bind variable into a substitution variable, as Example 11-8 shows.

Example 11-8. Using PRINT and NEW_VALUE to store a bind variable value into a substitution variable

SQL> TTITLE OFF
SQL> DEFINE my_sub_var = ' '
SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are';
PL/SQL procedure successfully completed.
SQL> COLUMN my_bind_var NEW_VALUE my_sub_var
SQL> PRINT my_bind_var
MY_BIND_VAR
----------------------------------------------------------------------
Brighten the corner where you are
SQL> PROMPT &my_sub_var
Brighten the corner where you are Issuing the PRINT command by itself causes the contents of all bind
variables to be displayed. Here's an example:

SQL> PRINT
S_TABLE_NAME
----------------------------------------------------------------------
EMPLOYEE
My Motto
-----------------------------------
Brighten the corner where you are
MY_BIND_NUM
-----------
9 Some special considerations apply when printing bind variables of
type CLOB and of type REFCURSOR. These are described in the following
sections.

11.1.3.2 PRINTing CLOB variables

CLOB stands for character large object, and variables of this type
can hold up to two gigabytes of tex
t
data (even more in Oracle Database 10 g ). When
printing variables of type CLOB or NCLOB, you can use three SQL*Plus
settings to control what you see and how the retrieval of the CLOB
data is done, as in Table 11-2.

By default, SQL*Plus displays only the first 80 characters of a CLOB
value. This is rarely enough characters. After all, if you needed
only 80 characters you wouldn't have used a CLOB
datatype in the first place. On the other hand, you may not want to
risk printing two gigabytes of data either.

Example 11-9 shows the result of displaying a CLOB
value using the default settings for the values in Table 11-2.

Example 11-9. PRINTing a CLOB bind variable using default settings

SQL> VARIABLE clob_bind CLOB
SQL>
SQL> BEGIN
2 SELECT clob_value INTO :clob_bind
3 FROM clob_example;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 60
SQL> PRINT clob_bind
CLOB_BIND
------------------------------------------------------------
By default, SQL*Plus will only display the first 80 characte
rs of a CLOB value.

As you can see, only 80 characters of the value were displayed.
Annoyingly, although you can use the COLUMN command to set the
heading over a bind variable's value, any attempt to
use WORD_WRAPPED to enable word wrapping is ignored. You can change
the LONG setting to see more of the value, as Example 11-10 shows.

Example 11-10. Example 11-9 rerun with a longer LONG setting

SQL> SET LONG 500
SQL> @ex11-9
SQL> SET ECHO ON
SQL>
SQL> VARIABLE clob_bind CLOB
SQL>
SQL> BEGIN
2 SELECT clob_value INTO :clob_bind
3 FROM clob_example;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 60
SQL> PRINT clob_bind
CLOB_BIND
------------------------------------------------------------
By default, SQL*Plus will only display the first 80 characte
rs of a CLOB value. This is rarely enough. After all, if you
only needed 80 characters, you wouldn't have used a CLOB da
tatype in the first place. On the other hand, you may not wa
nt to risk printing 2 gigabytes of data either.

By combining the LOBOFFSET and LONG settings, you can print any
arbitrary substring of a CLOB variable. Example 11-11
displays characters 81 through 102, which make up the second sentence
of the CLOB value shown in the previous two examples.

Example 11-11. Using SET LONG and SET LOBOFFSET to display a substring of a CLOB value

SQL> SET LONG 22
SQL> SET LOBOFFSET 81
SQL> @ex11-9
SQL> SET ECHO ON
SQL>
SQL> VARIABLE clob_bind CLOB
SQL>
SQL> BEGIN
2 SELECT clob_value INTO :clob_bind
3 FROM clob_example;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> SET LINESIZE 60
SQL> PRINT clob_bind
CLOB_BIND
----------------------
This is rarely enough.

Finally, the LONGCHUNKSIZE setting controls the amount of the CLOB
fetched from the database at one time. If you have the memory
available, you may want to set this to match the LONG setting. That
way, SQL*Plus retrieves all that you wish to display with one fetch
from the database, possibly improving performance.

11.1.3.3 PRINTing REFCURSOR variables

SQL*Plus allows you to create bind
variables of the type REFCURSOR. A REFCURSOR variable is a pointer to
a cursor that returns a result set. Using PL/SQL, you can assign any
SELECT query to a variable of this type and then use the SQL*Plus
PRINT command to format and display the results of that query. The
script shown in Example 11-12 makes use of this
capability by using a REFCURSOR to display a list of tables owned by
the current user.

Example 11-12. PRINTing a REFCURSOR variable

SET ECHO OFF
VARIABLE l_table_list REFCURSOR
-- Set the REFCURSOR variable to the results of
-- a SELECT statement returning a list of tables
-- owned by the user.
BEGIN
OPEN :l_table_list FOR
SELECT table_name
FROM user_tables;
END;
/
--Print the list of tables the user wants to see.
PRINT l_table_list The script in Example 11-12 defines a SQL*Plus
REFCURSOR variable. The cursor is opened and a query is assigned by
code within a PL/SQL block. Then the SQL*Plus PRINT command is used
to display the results of that query. Following is an example run of
the script:

SQL> @ex11-12
PL/SQL procedure successfully completed.
TABLE_NAME
------------------------------
CLOB_EXAMPLE
SUBTEST
NBR_CC
BILL_OF_MATERIALS
PART
...

The output you get when PRINTing a REFCURSOR variable is identical to
the output you would get if you executed the same query directly from
SQL*Plus.

11.1.3.4 SELECTing a bind variable

The SQL*Plus manual, at
least the one for Versions 8.0.3 and before, tells you that bind
variables can't be used in SQL statements.
Don't believe it! Bind variables
can be used in SELECT statements, in the column
list and in the WHERE clause. You will see this done in scripts where
getting the contents of a bind variable into a substitution variable
is important. (See the Section 11.1.2.) Example 11-13 shows a SELECT statement being used to display
the contents of a bind variable.

Example 11-13. SELECTing the contents of a bind variable

SQL> VARIABLE employee_name VARCHAR2(30)
SQL> EXECUTE :employee_name := 'Mykola Leontovych';
PL/SQL procedure successfully completed.
SQL> SELECT :employee_name FROM dual;
:EMPLOYEE_NAME
------------------------------------------------------------
Mykola Leontovych Using SELECT in this way offers no real advantage over the use of the
PRINT command. If you need to display one variable, you might as well
PRINT it. Being able to use bind variables in a SELECT statement
becomes more of an advantage when you need to display information
from more than one column, when you want to use the bind variable in
an expression for a computed column, or when you want to use it in
the WHERE clause. Example 11-14 combines all three of
these situations.

Example 11-14. A more extensive use of bind variables in a SQL statement

UNDEFINE user_name
VARIABLE l_user VARCHAR2(30)
EXECUTE :l_user := '&user_name';
SELECT 'User ' || :l_user || ' has '
|| TO_CHAR(COUNT(*)) || ' tables.'
FROM all_tables
WHERE owner = UPPER(:l_user);

You run Example 11-14 as follows:

SQL> @ex11-14
Enter value for user_name: gennick
PL/SQL procedure successfully completed.
'USER'||:L_USER||'HAS'||TO_CHAR(COUNT(*))||'TABLES.'
------------------------------------------------------------
User gennick has 60 tables.

Two types of bind variables can't be used in a SQL
statement: REFCURSOR and CLOB types. You must use the PRINT command
with these bind variables.

11.1.4 When and How to Use Bind Variables

You have three primary reasons for using bind variables in SQL*Plus:

You need to call PL/SQL procedures or functions that return a value
or that use IN OUT parameters.

You need to execute one of several possible SELECT statements
conditionally depending on user input or other circumstances.

You want to test a query for use in an application, and that query
uses colons to mark parameters.

The next few sections briefly describe each of these uses.

11.1.4.1 Calling PL/SQL procedures and functions from SQL*Plus

Oracle provides a number of useful, built-in
PL/SQL packages. Some of the procedures
and functions in these packages return values that you may wish to
capture and return to SQL*Plus. Capturing the return value from a
function is usually easy because you can SELECT that function from
the dual table. Example 11-15
generates a random string of alphanumerics (the
"X" option) by making a call to
DBMS_RANDOM.STRING from within a SELECT statement. The string is
returned to SQL*Plus via the COLUMN command's
NEW_VALUE mechanism.

Example 11-15. Capturing the return value of a PL/SQL function

COLUMN mixed_up NEW_VALUE mixed_up_sub_var
SELECT dbms_random.string('X',30) mixed_up
FROM dual;
DEFINE mixed_up_sub_var Life becomes more difficult, though, if you wish to invoke a
procedure or function with an OUT or an IN OUT parameter.
DBMS_UTILITY.CANONICALIZE is one such procedure:

DBMS_UTILITY.CANONICALIZE(
name IN VARCHAR2,
canon_name OUT VARCHAR2,
canon_len IN BINARY_INTEGER);

This procedure takes an identifier, such as a table reference, and
returns that identifier in canonical form. For example, pass the
table name gennick."Mixed_Case_Table" and
you'll get back
GENNICK."mixed_case_table".
What's interesting about this procedure, and it is a
procedure, is that it returns the canonical name via an OUT variable.
To execute this procedure, you must send that OUT variable somewhere,
and a bind variable provides a convenient destination. Example 11-16 presents a brief SQL*Plus script to invoke
DBMS_UTILITY.CANONICALIZE on a string that you supply and then
display the result.

Example 11-16. Capturing an OUT value into a bind variable

ACCEPT not_can CHAR PROMPT 'Enter a table reference >'
VARIABLE can VARCHAR2(60)
EXECUTE DBMS_UTILITY.CANONICALIZE('&not_can',:can, 60);
COLUMN can HEADING 'Canonicalized Reference IS:'
PRINT can The following is a run of Example 11-16:

SQL> @ex11-16
Enter a table reference >gennick."Mixed-Case-Table"
PL/SQL procedure successfully completed.
Canonicalized Reference IS:
------------------------------------------------------------
"GENNICK"."Mixed-Case-Table" Were it not for the SQL*Plus bind variable (named
:can in Example 11-16), you
wouldn't be able to capture the canonicalized string
for later use in your SQL*Plus script.

11.1.4.2 Using REFCURSOR variables

As mentioned earlier, the REFCURSOR datatype holds a pointer to a
cursor. Using REFCURSOR variables, you can open a cursor for a SELECT
statement in PL/SQL and print the results from SQL*Plus. One
practical use for this is to write PL/SQL code that selects one query
from many possibilities, based on user input or some other factor.

Earlier, in the section on "Printing REFCURSOR
variables," Example 11-12 showed the
use of a REFCURSOR variable to display a list of tables owned by the
current user. Example 11-17 is an enhanced version of
that script that allows you to enter a pattern match string to narrow
the list of table names to be displayed. The script executes one of
two possible queries depending on whether or not a string was
supplied.

Example 11-17. Setting a REFCURSOR to return results from one of two possible SELECT statements

SET ECHO OFF
SET VERIFY OFF
--Find out what tables the user wants to see.
--A null response results in seeing all the tables.
ACCEPT s_table_like PROMPT 'List tables LIKE > '
VARIABLE l_table_list REFCURSOR
--This PL/SQL block sets the l_table_list variable
--to the correct query, depending on whether or
--not the user specified all or part of a table_name.
BEGIN
IF '&s_table_like' IS NULL THEN
OPEN :l_table_list FOR
SELECT table_name
FROM user_tables;
ELSE
OPEN :l_table_list FOR
SELECT table_name
FROM user_tables
WHERE table_name LIKE UPPER('&s_table_like');
END IF;
END;
/
--Print the list of tables the user wants to see.
PRINT l_table_list This script first asks the user for a search string to be used with
the LIKE operator. Entering this is optional. If a pattern match
string is specified, then only table names that match that string are
displayed; otherwise, all table names are listed. This conditional
logic is implemented by the PL/SQL block, which checks the value of
the substitution variable and opens the REFCURSOR variable using the
appropriate SELECT statement. Here's how it looks to
run the script:

SQL> @ex11-17
List tables LIKE > %emp%
PL/SQL procedure successfully completed.
TABLE_NAME
------------------------------
EMPLOYEE
EMPLOYEES
EMPLOYEE_COMMENT
EMPLOYEE_COPY
EMPLOYEE_EXPENSE
EMPLOYEE_COMMA
EMPLOYEE_FIXED Using REFCURSOR variables is one way to add conditional logic to your
SQL*Plus scripts. You'll see another example of this
in Section 11.2.2.

You might be thinking about using REFCURSOR variables with the DBMS_SQL package to return the results of dynamically generated SQL queries back to SQL*Plus. Unfortunately, that can't be done. DBMS_SQL returns integer values that reference cursors held internally, but there is no way to get a REFCURSOR value pointing to one of those cursors.

11.1.4.3 Testing application queries

Bind variables can make it
more convenient to take a query from an application development
environment and debug it using SQL*Plus. Such queries often contain
parameters to be supplied at runtime, and those parameters are
preceded by colons, which is the syntax SQL*Plus uses for bind
variables. Example 11-18 shows a query containing a
bind variable in the WHERE clause.

Example 11-18. A query using a bind variable

SELECT employee.employee_id,
employee.employee_name,
employee.employee_hire_date,
employee.employee_termination_date,
employee.employee_billing_rate
FROM employee
WHERE employee.employee_id = :emp_id If you want to test the query in Example 11-18 and you
run it as it is, you will get the following results:

SQL> @ex11-18
SQL> SET ECHO ON
SQL> SELECT employee.employee_id,
2 employee.employee_name,
3 employee.employee_hire_date,
4 employee.employee_termination_date,
5 employee.employee_billing_rate
6 FROM employee
7 WHERE employee.employee_id = :emp_id;
SP2-0552: Bind variable "EMP_ID" not declared.

At this point, you have two choices. You can change the query and
replace the parameter :emp_id with an employee
number you know exists. You can test the query, and when you are
satisfied the query works, you can replace the hardcoded value with
the parameter reference. Woe be unto you, however, if there are
several parameters and you forget to change one back. A second and
safer approach is to declare bind variables to match the parameters
in the query. In this case, there is just one to declare:

SQL> VARIABLE emp_id NUMBER Once the variable has been declared, it is a simple matter to
initialize it to a known good value:

SQL> EXECUTE :emp_id := 101;
PL/SQL procedure successfully completed.

Now that you have declared and initialized the variable,
it's easy to execute the query, bind variable and
all:

SQL> @ex11-18
SQL> SET ECHO ON
SQL> SELECT employee.employee_id,
2 employee.employee_name,
3 employee.employee_hire_date,
4 employee.employee_termination_date,
5 employee.employee_billing_rate
6 FROM employee
7 WHERE employee.employee_id = :emp_id;
EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_ EMPLOYEE_ EMPLOYEE_BILLING_RATE
----------- -------------------- --------- --------- ---------------------
101 Marusia Churai 15-NOV-61 169 Once you are satisfied that everything is correct, you can paste the
query directly back into your application without the risk that you
might forget to change a hardcoded value back into a bind variable.