Solving PLS-00487: Invalid Reference to Variable in PL/SQL

The PL/SQL error PLS-00487: Invalid reference to variable <name> occurs when you attempt to reference a variable in a context where it is not allowed, such as:

• In a SQL context that doesn’t support procedural variables
• Inside function-based indexes or stored generated columns
• As a default expression for another variable
• Or in uninitialized or incorrectly scoped usage

This article explores what causes this error and how to resolve it with clear, working examples.

1. Understanding the Error: PLS-00487

Error Message Format:

vbnetCopyEditPLS-00487: Invalid reference to variable '<name>'

This means the variable <name> is being accessed in a context where it is invalid or disallowed.

PL/SQL allows you to declare and use variables within procedural blocks, but when you try to use them in SQL or schema-level declarations where they are not recognized, Oracle throws this error.

2. Common Causes and Fixes

Cause 1: Using PL/SQL variable in a SQL DDL or schema-level definition

Incorrect Example:

DECLARE
   v_default_value NUMBER := 100;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE my_table (
       id NUMBER,
       value NUMBER DEFAULT ' || v_default_value || '
   )';
END;
/

Problem:
You cannot use v_default_value directly in dynamic DDL unless you concatenate its value into the SQL string.

Corrected Example:

DECLARE
   v_default_value NUMBER := 100;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE my_table (
       id NUMBER,
       value NUMBER DEFAULT ' || TO_CHAR(v_default_value) || '
   )';
END;
/

Cause 2: Using variables in function-based indexes

Oracle disallows PL/SQL variables inside function-based indexes.

Incorrect:

DECLARE
   v_col_value VARCHAR2(10) := 'Y';
BEGIN
   EXECUTE IMMEDIATE 'CREATE INDEX idx_demo ON my_table (CASE WHEN active = ''' || v_col_value || ''' THEN 1 ELSE 0 END)';
END;
/

Fix:
You must hard-code the value or use application logic to avoid using PL/SQL variables in index definitions.

Cause 3: Variable used before declaration

Incorrect:

DECLARE
   v_total NUMBER := v_subtotal + 100;  -- v_subtotal not declared yet
   v_subtotal NUMBER := 50;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_total);
END;
/

Error:

PLS-00487: Invalid reference to variable 'V_SUBTOTAL'

Fix:
Declare v_subtotal before v_total.

DECLARE
   v_subtotal NUMBER := 50;
   v_total NUMBER := v_subtotal + 100;
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_total);
END;
/

Cause 4: Variable used in a default value of another declaration improperly

Incorrect:

DECLARE
   a NUMBER := 5;
   b NUMBER DEFAULT a + 10;  -- Invalid reference
BEGIN
   DBMS_OUTPUT.PUT_LINE(b);
END;
/

Fix:
Use assignment in the BEGIN section, not the declaration section:

DECLARE
   a NUMBER := 5;
   b NUMBER;
BEGIN
   b := a + 10;
   DBMS_OUTPUT.PUT_LINE(b);
END;
/

3. Less Common Scenarios

Accessing a variable from outside its scope

Incorrect:

DECLARE
   outer_var NUMBER := 100;
BEGIN
   DECLARE
      inner_var NUMBER := outer_var + 10;  -- OK: accessing outer_var inside inner block
   BEGIN
      DBMS_OUTPUT.PUT_LINE(inner_var);
   END;

   DBMS_OUTPUT.PUT_LINE(inner_var);  -- ❌ invalid reference
END;
/

Fix:
Only use inner_var inside its declaration block.

4. Summary Table

5. Conclusion

The PLS-00487: Invalid reference to variable <name> error often appears when procedural logic is misused in non-procedural contexts. Fixing it requires:

• Understanding PL/SQL block and scope rules
• Avoiding use of variables in schema-level SQL
• Declaring variables in correct order
• Moving expressions into the executable section when needed

By respecting these structural boundaries in PL/SQL, you can write cleaner and more reliable code.