How to resolve ORA-00900



ORA-00900 is one of the common error

Here what oracle documentation says about it

ORA-00900

Reference: Oracle documentation

Checklist to solve the ORA-00900 error

  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 which 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 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 time developer do mistakes in the plsql block and write statment 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 valid statement

The fix is to use begin and end as given below

 

v_dynsql:=

‘BEGIN

dbms_utility.analyze_schema(‘OKX’,’ESTIMATE’,30);

END;’;

execute immediate v_dynsql;

Hope you like the various ways to fix the  ORA errors

Related articles
ORA-00911: invalid character Common Issues and Resolution

How to resolve ORA-29913 with external tables

ORA-27154: post/wait create failed during startup

ORA-01111 in MRP in Standby

How to resolve the ORA-00257 error in Oracle database


Leave a Reply