Ошибка ora 00900 - Решение и исправление самых разных ошибок на TopOshibok.ru

С точки зрения синтаксиса, все написано вроде верно, но мне выдает ошибку.

Сам анонимный блок синтактически верен и он компилируется. Но ошибка не в нём, а в том, что он запускает динамически — ORA-06512: at line 6, т.е в строчке:

execute immediate actjob.scr;

Для локализации ошибки надо как минимум вывести команду, которую блок будет выполнять.

Например, изменённый для воиспроизводимости блок из вопроса:

declare
    cursor activejobs is
        select 'begin dbms_scheduler.drop_job('''||JOB_name||''',force=>true); end;' scr 
        from (select 'XXX' JOB_name from dual);
begin
    for actjob in activejobs loop
        dbms_output.put_line ('>>'||actjob.scr||'<<');
        execute immediate actjob.scr;
    end loop;
end;
/

тоже выглядит синтактически правильно, но при выполнении ошибка:

declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 8

В выводе видно, что в команде присутствует non-ascii символ:

>>begin???dbms_scheduler.drop_job('XXX',force=>true); end;<<

Поэтому, прежде всего стоит проверить скрипт на наличие non-ascii символoв:

$ grep -P "[^\x00-\x7F]" test.sql|xd
000000 20 20 20 20 20 20 20 20 73 65 6c 65 63 74 20 27  >        select '<
000010 62 65 67 69 6e e2 80 80 64 62 6d 73 5f 73 63 68  >begin...dbms_sch<
                      ^^^^^^^^

Источник

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

Reference: Oracle documentation

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

Related articles
ORA-00911: invalid character
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-27154: post/wait create failed during startup
ORA-01111
ORA-00257 : archiver error, connect internal only until freed
ora-29283: invalid file operation

Источник

Learn the cause and how to resolve the ORA-00900 error message in Oracle.

Description

When you encounter an ORA-00900 error, the following error message will appear:

ORA-00900: invalid SQL statement

Cause

The statement that you’ve tried to execute is not a valid SQL statement.

Resolution

The option(s) to resolve this Oracle error are:

Option #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.

Below is what a sample PL/SQL banner looks like:

Источник

ORA-00900 Error Message “Invalid SQL Statement”

Error ORA-00900 occurs when the user tries to execute a statement that is not a valid SQL statement. This error occurs if the Procedural Option is not installed and a SQL statement is issued that requires this option.

Users have several options in resolving error ORA-00900. You may choose to install the Procedural Option or correct the actual syntax.

To determine if you have installed the Procedural Option, open a session in Oracle by typing SQL*Plus. If the PL/SQL banner does not display, this means that the Procedural Option has not been installed. Otherwise, the PL/SQL banner should appear in a display window and contain a message with the following information:

SQL*Plus: [Version of release] – [Date of production: Day of week, month, day]

[Further information on the connection]

Users may see error ORA-00900 when attempting to use a database link or when querying fields that may have worked previously. If faced with this situation, users should take the following steps.

On the local database, change the init.ora parameter NLS _ DATE _ FORMAT and use double quotation marks for the value. Using single quotation marks will cause Oracle to throw error ORA-00900 as this is not properly read and will become invalid. Always use double quotation marks. The database should then be restarted. For example, the statement should read:

NLS _ DATE _ FORMAT = “Day – Month – Year”

Another method is to alter the parameter within the session. This is done by an “alter session set” statement. The following is an example of such a statement:

SQL> alter session set NLS _ DATE _ FORMAT = “Day – Month – Year”;

To avoid seeing error ORA-00900 in the future, double check the syntax and spelling of your PL/SQL statements. Look over your error statement to get more clues on how to solve your error. Minimize the possibility of errors by using indentations and a color-coded IDE. If you continue to see error ORA-00900, you may try posting your code to an Oracle help forum. Likely, another Oracle user has faced the same or similar problems and has already posted the question to a forum. You may consider consulting your database administrator for help or even an outside expert. With any outside Oracle expert, make sure to check their certifications and experience to ensure they are professionals. For reference, see the Online Oracle documentation.

Источник

Calling an Oracle stored procedure in SSIS is a little different than calling a SQL Server version. Today I will show you the error you get if trying the SQL Server syntax and how to fix it.

I am assuming you already have your Oracle connections set up and have rights to call the Oracle stored procedure.

The first thing I tried was using the syntax I was used to in the Execute SQL Task editor.

Below I am trying to execute the following command:

exec cisadm.CM_DIRS_MEETING_PRC_1()

osp1

After I click OK and then try to run the package, I get an error:

[Execute SQL Task] Error: Executing the query "exec cisadm.CM_DIRS_MEETING_PRC_1();
" failed with the following error: "ORA-00900: invalid SQL statement". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, 
parameters not set correctly, or connection not established correctly.

osp2

After doing some research through Oracle’s documentation, you have to call the procedure as follows:

DECLARE
BEGIN
cisadm.CM_DIRS_MEETING_PRC_1();
end;

osp3

After these changes are made, I tried to run the package and it executed successfully.

osp4

You now know how to get Oracle Stored Procedures to run in SSIS and to solve this scary looking error message.

[Execute SQL Task] Error: Executing the query “exec cisadm.CM_DIRS_MEETING_PRC_();
” failed with the following error: “ORA-00900: invalid SQL statement”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly,
parameters not set correctly, or connection not established correctly.

Источник