Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5955
  • Last Modified:

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
0
nullmind
Asked:
nullmind
  • 2
1 Solution
 
paquicubaCommented:
All you have to do is assign a value to the substitution variable, so you won't get prompted at runtime, see below:

HR@PROD > DEFINE p_dept_name = 'Collections'
HR@PROD > DEFINE p_manager_id = 200
HR@PROD > DEFINE p_dept_loc = 1900
HR@PROD >
HR@PROD > DECLARE
  2       v_max_deptno    departments.department_id%TYPE;
  3
  4  BEGIN
  5       SELECT MAX(department_id) + 10
  6       INTO v_max_deptno
  7       FROM departments;
  8
  9       INSERT INTO departments (department_id, department_name, manager_id, location_id)
 10       VALUES (v_max_deptno, '&p_dept_name', '&p_manager_id', '&p_dept_loc');
 11       COMMIT;
 12
 13  END;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
HR@PROD > SELECT * FROM DEPARTMENTS;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          280 Collections                           200        1900
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700

28 rows selected.

Elapsed: 00:00:00.01
0
 
paquicubaCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now