Oracle PL/SQL block accepts user input information with the help of substitution variable. Substitute variable can not be used to output values, because memory is not allocated to substitute variable. Substitute variable is prefixed with ampersand(&) or double ampersand (&&) character. Below example is using Oracle provided HR schema to demonstrate the use of substitute variable
SET SERVEROUTPUT ON
DECLARE
v_employee_id NUMBER := &s_employee_id;
v_first_name VARCHAR2(20);
v_last_name VARCHAR2(25);
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM hr.employees
WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE ( ' Employee Name is '|| v_first_name ||' '|| v_last_name );
DECLARE
v_employee_id NUMBER := &s_employee_id;
v_first_name VARCHAR2(20);
v_last_name VARCHAR2(25);
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM hr.employees
WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE ( ' Employee Name is '|| v_first_name ||' '|| v_last_name );
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Supplied employee id does not exists');
END;
/
Enter value for s_employee_id: 123
old 2: v_employee_id NUMBER := &s_employee_id;
new 2: v_employee_id NUMBER := 123;
Employee Name is Shanta Vollman
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Supplied employee id does not exists');
END;
/
Enter value for s_employee_id: 123
old 2: v_employee_id NUMBER := &s_employee_id;
new 2: v_employee_id NUMBER := 123;
Employee Name is Shanta Vollman
PL/SQL procedure successfully completed
IT DOESN'T WORK WITH ORACLE 10G
ReplyDeleteIT DOESN'T WORK WITH ORACLE 10G ..WHAT CHANGES NEEDS TO BE DONE WITH ORACLE 10G
ReplyDelete