What is substitution variable in PL/SQL?

Question

I'm using this code to enter the name of the department using substitute variable.

DECLARE
MAX_DEP_VAR NUMBER;
BEGIN
SELECT MAX(DEPTNO) INTO MAX_DEP_VAR
FROM DEPT;
INSERT INTO DEPT
VALUES(MAX_DEP_VAR+10, &DEPTNAME, NULL);
END;

But it gives this error:

ORA-06550: line 0, column 0:
PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
ORA-06550: line 6, column 13:
PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

You are running this script in sqlplus, and are referencing a variable, &DEPTNAME, which is not declared anywhere. Go read this - docs.oracle.com/cd/B19306_01/server.102/b14357/ch5.htm and this cs.csub.edu/~hwang/CS342/SqlPlus.htm — OldProgrammer

Luke Woodward Luke Woodward · Accepted Answer · 2013-11-18T20:02:44

Here's one way to generate the error you're seeing (Oracle XE 11.2.0.2.0, Windows 8.1 x64):

SQL> DEFINE deptname=": abc"
SQL> CREATE OR REPLACE TRIGGER test_trigger
  2    AFTER UPDATE ON DEPT
  3    FOR EACH ROW
  4  BEGIN
  5    DECLARE
  6      MAX_DEP_VAR NUMBER;
  7    BEGIN
  8      SELECT MAX(DEPTNO) INTO MAX_DEP_VAR
  9        FROM DEPT;
 10      INSERT INTO DEPT
 11        VALUES(MAX_DEP_VAR+10, &DEPTNAME, NULL);
 12    END;
 13  END;
 14  /
old  11:       VALUES(MAX_DEP_VAR+10, &DEPTNAME, NULL);
new  11:       VALUES(MAX_DEP_VAR+10, : abc, NULL);

Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS TRIGGER test_trigger;
Errors for TRIGGER TEST_TRIGGER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
7/5      PL/SQL: SQL Statement ignored
7/17     PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
         [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

I happen to know that using a colon followed by a space followed by a name in a trigger causes the error message you're seeing. However, there may well be other ways to generate this error and you might have hit one of these instead.

What value exactly does your substitution variable deptname contain? If, for example, it contains Accounts, then your SQL will end up being the following:

DECLARE
MAX_DEP_VAR NUMBER;
BEGIN
SELECT MAX(DEPTNO) INTO MAX_DEP_VAR
FROM DEPT;
INSERT INTO DEPT
VALUES(MAX_DEP_VAR+10, Accounts, NULL);
END;

Of course, this is not valid, because Accounts needs to be in single-quotes for Oracle to recognise it as a string. Remember that SQL*Plus will replace substitution variables with their values without any regard for whether this gives you valid SQL.

If you have declared deptname as something like Accounts, then I would recommend replacing the line

VALUES(MAX_DEP_VAR+10, &DEPTNAME, NULL);

with

VALUES(MAX_DEP_VAR+10, '&DEPTNAME.', NULL);

That way, when SQL*Plus replaces deptname with its value, you end up with

VALUES(MAX_DEP_VAR+10, 'Accounts', NULL);

which is valid.

What is substitution variable in PL/SQL?

1 Answers