Wednesday, December 1, 2010

Accept User Input (substitute variable) in Anonymous PL/SQL Block


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 );
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

PL/SQL procedure successfully completed

2 comments:

  1. IT DOESN'T WORK WITH ORACLE 10G

    ReplyDelete
  2. IT DOESN'T WORK WITH ORACLE 10G ..WHAT CHANGES NEEDS TO BE DONE WITH ORACLE 10G

    ReplyDelete

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...