Ora-00900: Invalid SQL Statement On EXECUTE IMMEDIATE Call with Dynamic Object Names (Doc ID 578464.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 10.2.0.3 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 22-Jul-2013***


Symptoms

Code raises ORA-00900: invalid SQL statement from a package call building and running Dynamic SQL with EXECUTE IMMEDIATE when the initialization parameter plsql_optimize_level=2.

--if not already set:
alter session set plsql_optimize_level=2;

drop table sample;

create or replace procedure do_null
(dat in varchar2)
IS
BEGIN
  null;
  dbms_output.put_line('did nothing ' || dat);
end;
/

create table sample
(name varchar2(15), num number);


SET serveroutput on

DECLARE
  Type mytab_type is table of sample%rowtype
  INDEX by BINARY_INTEGER;
  mytab mytab_type;
BEGIN
  mytab(1).name := 'do_null';
  mytab(1).num :=  1;
  EXECUTE IMMEDIATE 'BEGIN ' || mytab(1).name|| '(:val_in); END;'
    USING IN to_char(mytab(1).num);
END;
/


Sample Output :


BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 10

 

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