How to resolve ORA-00900

ORA-00900 invalid SQL Statement is one of the common error
Here is what oracle documentation says about this error

Checklist to solve the ORA-00900 invalid SQL Statement

(1) This error commonly occurs when you are trying to create an Oracle procedure and the Procedural Option is not installed. To determine if the Procedural Option has been installed, open an Oracle session using SQL*Plus. If the PL/SQL banner does not display, then you know that the Procedural Option has not been installed.

(2) ORA-00900 can occurs while attempting to use a database link. Many users find that they are encountering ORA-00900 as they attempt to query fields that may have worked before 2000. To resolve ORA-00900, on the local database, try altering your init.ora parameter NLS_DATE_FORMAT, then use double quotes (instead of single) around the value


alter session set NLS_DATE_FORMAT = "DD-MON-YYYY";

(3) Using execute statement on sql developer /JDBC connection

execute dbms_utility.analyze_schema('OKX','ESTIMATE',30);
ORA-00900: invalid SQL statement

execute is sqlplus option, we should use either of the below options in application/other language programs

begin
execute dbms_utility.analyze_schema('OKX','ESTIMATE',30);
end;
or
begin
execute dbms_utility.analyze_schema('OKX','ESTIMATE',30)
end;
/

(4) Many times developers do mistakes in the plsql block and write statements like

v_dynsql:='dbms_utility.analyze_schema('OKX','ESTIMATE',30)';
execute immediate v_dynsql;

The above code gives ORA-00900 as dbms_utility.analyze_schema(‘OKX’,’ESTIMATE’,30);
is not a valid statement

The fix is to use begin and end as given below

v_dynsql:=
q'[BEGIN
dbms_utility.analyze_schema('OKX','ESTIMATE',30);
END;]';
execute immediate v_dynsql;

(5) If you want to describe a table in PLSQL

SQL> begin execute immediate 'describe FND_USER';
2 end;
3 /
begin execute immediate 'describe FND_USER';
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 1

You cannot use desc here. we may want to select it based on query

begin
execute immediate q'[select COLUMN_NAME,DATA_TYPE
from all_tab_columns
where table_name = 'FND_USER'
order by column_id]';
end;
/

(6) If you are trying to explain plan on create view statement

SQL> explain plan for create view test as select * from dual;
explain plan for create view test as select * from dual
*
ERROR at line 1:
ORA-00900: invalid SQL statement

Hope you like the various ways to fix the ORA errors. Please do provide feedback on it

Checklist to solve the ORA-00900 invalid SQL Statement

Related Posts

Leave a Comment Cancel Reply