PL/SQL fundamental – Obtain environment variable and session information

Make use of the SYS_CONTEXT built-in function to query the database for the user’s information. Once you
have obtained the information, then store it into a local variable. At that point, you can do whatever
you’d like with it, such as save it in a logging table. The following code block demonstrates this
technique:

<<obtain_user_info>>
DECLARE
username varchar2(100);
ip_address varchar2(100);
BEGIN
SELECT SYS_CONTEXT('USERENV','SESSION_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS')
INTO username, ip_address
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('The connected user is: ' || username || ', and the IP address
is ' ||
ip_address);
END;

PL/SQL fundamentals-1

Problem
You want to write a script that prompts the user for some input. You want your PL/SQL code to then use that input to generate some results.

The variable following the & character is known as a substitution variable. It is important to note that a
substitution variable is meaningful to SQLPlus. Substitution variables are not “seen” by the database engine. SQLPlus actually replaces the variable reference with the text that the user entered. As far as the
database is concerned, the solution code contains the following WHERE clause:
WHERE department_id = 40;
If you want to reference the same substitution variable at a different point in your code, you can
place two ampersands in front of the first to tell SQLPlus that you want to retain that value for use at a later time. For instance, the following code block first obtains the value from the keyboard using &&variable_name, and then it prints that value out using &variable_name:

DECLARE emp_count NUMBER; BEGIN SELECT count()
INTO emp_count
FROM employees
WHERE department_id = &&department_id;
DBMS_OUTPUT.PUT_LINE('The employee count is: ' || emp_count ||
' for the department with an ID of: ' || &department_id);
END;

When using substitution variables, it is imperative to pay attention to the type of value the user will
be entering at the keyboard. If a value will be a variable character (VARCHAR2) type, then the substitution
variable must be surrounded by single quotes, or you will receive an error when the input is processed.
Similarly, if a value should be a numeric (NUMBER) type, then there should not be single quotes placed
around the substitution variable. Here’s an example:

<<outer_block>>
DECLARE
mgr_id NUMBER(6) := '&current_manager_id';
dept_count number := 0;
BEGIN
SELECT count(*)
INTO dept_count
FROM departments
WHERE manager_id = outer_block.mgr_id;
IF dept_count > 0 THEN
<<inner_block>>
DECLARE
dept_name VARCHAR2(30);
mgr_id NUMBER(6):= '&new_manager_id';
BEGIN
SELECT department_name
INTO dept_name
FROM departments
WHERE manager_id = outer_block.mgr_id;
UPDATE departments
SET manager_id = inner_block.mgr_id
WHERE manager_id = outer_block.mgr_id;
DBMS_OUTPUT.PUT_LINE
('Department manager ID has been changed for ' || dept_name);
END inner_block;
ELSE
DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There are no departments listed for the manager');
END outer_block;

You want to execute a script in SQLPlus that contains elements that appear to be substitution variables, but you do not intend them to be substitution variables. You want the interpreter to ignore them instead of prompting the user for input.

Solution #1

One solution is to precede the & character with an escape character. The escape character tells SQLPlus
that what follows is not intended to be a variable reference.
In the following code, an escape character is used to tell SQLPlus to ignore the & character when it is encountered and to treat “& Receiving” as simple text within a string: SQL> SET ESCAPE '\' SQL> INSERT INTO DEPARTMENTS VALUES( 2 departments_seq.nextval, 3 'Shipping \& Receiving', 4 null, 5 null); 1 row created.

Solution #2

Another solution is to completely disable the substitution variable feature. The next example uses the SET DEFINE OFF command to tell SQLPlus that it should ignore all substitution variables:
SQL> SET DEFINE OFF
INSERT INTO DEPARTMENTS VALUES(
departments_seq.nextval,
'Importing & Exporting',
null,
null);
1 row created.

Problem
You have a variable and a column sharing the same name. You want to refer to both in the same SQL
statement.
For example, you decide that you’d like to search for records where LAST_NAME is not equal to a last
name that is provided by a user via an argument to a procedure call. Suppose you have declared a
variable LAST_NAME, and you want to alter the query to read as follows:
SELECT first_name, last_name, email
INTO first, last, email
FROM employees
WHERE last_name = last_name;
How does PL/SQL know which LAST_NAME you are referring to since both the table column name and
the variable name are the same? You need a way to differentiate your references.

You can use the dot notation to fully qualify the local variable name with the procedure name so that
PL/SQL can differentiate between the two. The altered query, including the fully qualified
procedure_name.variable solution, would read as follows:
CREATE OR REPLACE PROCEDURE retrieve_emp_info(last_name IN VARCHAR2) AS
first VARCHAR2(20);
last VARCHAR2(25);
email VARCHAR2(25);
BEGIN
SELECT first_name, last_name, email
INTO first, last, email
FROM employees
WHERE last_name = retrieve_emp_info.last_name;
DBMS_OUTPUT.PUT_LINE(
'Employee Information for ID: ' || first || ' ' || last_name || ' - ' || email);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee matches the last name ' || last_name);
END;