Fail To Refer A Plsql Variable Inside Dynamic SQL (Doc ID 1385588.1)

Last updated on AUGUST 06, 2012

Applies to:

PL/SQL - Version 10.2.0.5 and later
Information in this document applies to any platform.

Symptoms

Using a plsql variable in a dynamic sql in the plsql procedure, it failed with following error:

ORA-00904 invalid identifier.

Testcase:

 

connect scott/******

set serveroutput on
declare
Y number(2) := 1.5;
V number(2);
p_a number(8);
p_aa number(8);

begin
V := Y + 5;

-- The static sql is working

select nvl(max(case when a.comm <= V*250 and a.comm >= Y*500 then a.sal else null end),-333) into p_a from emp a;
DBMS_OUTPUT.PUT_LINE(' results = '||p_a);

-- The dynamic sql failed with error ORA-00904: "V": invalid identifier.

execute immediate 'select nvl(max(case when a.comm <= V*250 and a.comm >= Y*500 then a.sal else null end),-333) from emp a' into p_aa;
DBMS_OUTPUT.PUT_LINE(' results = '||p_aa);

end;
/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms