asked on

Substitution Variables in PL/SQL

I'm trying to add a new department in to my DEPARTMENTS table. I want to have information about the new department (name, manager_id, location) come from substitution variables. But, I'm getting errors for variables not being defined...etc.

How do I use substitution variables correctly to make this code work? What exactly ARE substitution variables?

Here's the code that I'm using (I'm using iSQL* Plus):

SET ECHO OFF
SET VERIFY OFF

DEFINE
      p_dept_name      departments.department_name%TYPE;
      p_manager_id departments.manager_id%TYPE;
      p_dept_loc        departments.location_id%TYPE;

DECLARE
      v_max_deptno departments.department_id%TYPE;

BEGIN
      SELECT MAX(department_id) + 10
      INTO v_max_deptno
      FROM departments;

      INSERT INTO departments (department_id, department_name, manager_id, location_id)
      VALUES (v_max_deptno, '&p_dept_name', '&p_manager_id', '&p_dept_loc');
      COMMIT;

END;
/

SET ECHO ON
SET VERIFY ON

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

paquicuba

You seem to be confused with BIND variables, see how they work:

HR@PROD > VARIABLE p_dept_name VARCHAR2(20)
HR@PROD > BEGIN
2 SELECT DEPARTMENT_NAME INTO :p_dept_name FROM DEPARTMENTS WHERE ROWNUM = 1;
3 END;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
HR@PROD > PRINT p_dept_name

P_DEPT_NAME
--------------------------------
Collections