Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

ORA-00900: invalid SQL statement tips

Oracle Error Tips by Burleson Consulting

 

Oracle says this about the ORA-00900:

 

ORA-00900 invalid SQL statement
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option.

Oracle offers information regarding ORA-00900, when it 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.  For example:

NLS_DATE_FORMAT = "DD-MON-YYYY"

The next step in resolving ORA-00900 is to restart the database.

If you want to instead alter the parameter in the session, you can try:

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

The reason ORA-00900 is thrown is that the single quotes become incorrectly read and so the results lose validity. 


A user on this forum also encountered ORA-00900:

Question:

I am trying to execute a procedure using parameter, if there are two parameters, I do not receive ORA-00900, but for other parameter, this message is thrown:

ORA-00900: invalid SQL statement

When I try this, my procedure attempts to update.  This update is successful with the backend and three rows, however, it does not update the entire table.  Her is the procedure:

select ,insert,update grant permission are given to the table.
update statement within procedure:

update table_customer set x_customer_value =
(select to_number(FLD2) from cfg_asciifiles
where to_number(FLD1)='100000195'
and process_flag is null
and inputfilename='c100'
and cno='test1.txt')
where x_customer_id in (select to_number(FLD1)
from cfg_asciifiles
where process_flag is null
and inputfilename='test1.txt'
and cno='c100');

Answer:

The ORA-00900 issues have to do with the parameters passing through the procedure because there are text strings being used.  You should use a dynamic SQL in your stored procedure:

cmmd:='ALTER SESSION SET CURSOR_SHARING=SIMILAR';
username:=SYS_CONTEXT('USERENV','SESSION_USER');
IF username LIKE 'APPL%' then
EXECUTE IMMEDIATE cmmd;
EXECUTE IMMEDIATE cmmd2;
END IF;

For more information on this try:

Oracle Dynamic PL/SQL Stored Procedures

Late Binding and Runtime Binding in PL/SQL


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.


Hit Counter