ORA-00922
ORA-00922: неправильная опция
Причина:
Была указана неправильная опция в определении колонки или параметра памяти. Правильная опция при определении колонки как NOT NULL для указания, что колонка не содержит нулевых значений. Только ограничения могут следовать за типом данных. Указывая максимальную длину данных типа DATE или LONG вы будете часто вызывать эту ошибку.
Действие:
Проверьте ваш синтаксис и спеллинг. Удалите неправильную опцию или спецификацию длины из области определения колонки.
ORA-00922 means that SQL parser found an invalid option where should be empty or one of other valid options it expects.
Since it’s a broadly used error, the root cause could be very different from case to case.
ALTER USER
Case 1
Tried to grant some quota on tablespace to an user, but it failed with ORA-00922.
SQL> alter user hr quota 100g on tablespace users;
alter user hr quota 100g on tablespace users
*
ERROR at line 1:
ORA-00922: missing or invalid option
In this case, the keyword TABLESPACE shall not be used in this clause, just use a normal tablespace name after ON.
SQL> alter user hr quota 100g on users;
User altered.
Case 2
Tried to lock an user, but it failed with ORA-00922.
SQL> alter user hr lock;
alter user hr lock
*
ERROR at line 1:
ORA-00922: missing or invalid option
In the above, we missed the keyword ACCOUNT before LOCK.
SQL> alter user hr account lock;
User altered.
CREATE TABLE
Case 1
Tried to use CTAS to create a table to another tablespace, but it failed with ORA-00922.
SQL> create table t2 tablespace users select * from t1;
create table t2 tablespace users select * from t1
*
ERROR at line 1:
ORA-00922: missing or invalid option
In the above, we missed the keyword AS before SELECT.
SQL> create table t2 tablespace users as select * from t1;
Table created.
Case 2
Tried to use CTAS to create a table to another tablespace, but it failed with ORA-00922.
SQL> create table t2 users as select * from t1;
create table t2 users as select * from t1
*
ERROR at line 1:
ORA-00922: missing or invalid option
In the above, we missed the keyword TABLESPACE before the tablespace name USERS.
SQL> create table t2 tablespace users as select * from t1;
Table created.
Пытаюсь выполнить PL/SQL скрипт:
script = '''
Set serveroutput on;
DECLARE
req pls_integer;
BEGIN
req := infomediary_nse.request(
p_inApp_id => 100,
p_inPayload => XMLTYPE('<tag>hello</tag>'));
END;
/'''
connection = cx_Oracle.connect(ConnectionString)
str, err = connection.cursor().execute(script)
Но получаю:
cx_Oracle.DatabaseError: ORA-00922: missing or invalid option
Похоже на проблему с форматированием скрипта, но пока не знаю, как ее решить.
Свободный перевод вопроса cx_Oracle.DatabaseError: ORA-00922: missing or invalid option when trying to execute pl/sql от участника @Mohd Faheem
задан 11 мая 2021 в 23:05
0xdb0xdb
51.5k198 золотых знаков59 серебряных знаков237 бронзовых знаков
1
Ошибка заключается в том, что cx_Oracle не знает понятия — скрипт. Каждый запрос или вызов анонимного блока, процедуры или функци, это отдельная единица выполнения. Кроме того, не следует слепо копировать фрагменты кода из IDE. Они могут содержать команды или символы самого IDE, но которые не являются частью синтаксиса SQL или PL/SQL.
В данном случае это: set serveroutput on и завершающие символы: ;, /.
Простой рабочий пример вкл. считывание буфера вывода dbms_output:
sql = """
declare
payload XMLType := XMLType ('<tag>hello</tag>');
begin
dbms_output.put_line('payload='||payload.getStringVal());
end;
"""
cursor.callproc ("dbms_output.enable")
cursor.execute (sql)
chunksize = 100
lines = cursor.arrayvar (str, chunksize)
numlines = cursor.var (int)
numlines.setvalue (0, chunksize)
while True:
cursor.callproc ("dbms_output.get_lines", (lines, numlines))
nl = numlines.getvalue()
linearr = lines.getvalue()[:nl]
for line in linearr:
print(line or "")
if nl < chunksize:
break
# payload=<tag>hello</tag>
ответ дан 11 мая 2021 в 23:05
0xdb0xdb
51.5k198 золотых знаков59 серебряных знаков237 бронзовых знаков
ORA-00922
ORA-00922: missing or invalid option is one of many Oracle errors, which does not point to a concrete and easily identifiable problem. The key to resolving this issue is to find the specific location of the error (which can be tedious) and correct its syntax. A small error such as a misplaced comma or missing quotation mark can throw this error. To make things easier, use a good integrated development environment software application to help identify syntactical errors that are hard to catch on your own.
The Problem
ORA-00922: missing or invalid option occurs if an invalid option is specified while defining a storage clause or column. Oracle provides the following information about this error:
Cause: The valid option in specifying a column is NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error.
The Solution
Resolving ORA-00922: missing or invalid option can be tricky as the error does not appear due to one, universal cause. Oracle simply states that the syntax must be corrected. The user must remove the erroneous option or length specification from the column or storage specification.
In the following example, a table named employee is created:
CREATE table employee
(employee_id(5) NOT NULL, employee_name varchar2(30) employment_length varchar2 (40))
While this code seems correct at first glance, there is a missing comma after “employee_name varchar2(30),” throwing off the entire code and warranting ORA-00922 error message. The corrected code should read:
CREATE table employee
(employee_id(5) NOT NULL, employee_name varchar2(30), employment_length varchar2 (40))
ORA-00922: missing or invalid option is commonly seen during the installation process, specifically when creating new passwords. It installer may state that the user cannot be created along with the message: “missing or invalid option.” In such cases, the error is likely occurring because you are attempting to create a password that is complex. Changing the password to a less complex, weaker password will allow you to resolve the error and complete the installation successfully. Note that you can change the password to a stronger one after the installation is complete.
Moving Forward
If you continue to face problems with ORA-00920: missing or invalid option, you may consider contacting your database administrator for assistance if you yourself are not the DBA. Another option may be to contact an Oracle professional to resolve the issue. Remember to always check the consultant’s credentials and certification to ensure your Oracle needs are properly met.
I am running an export from Oracle 11g:
$ expdp system/ELIDED JOB_NAME=exp_BTM2CATS SCHEMAS=BTM2CATS file=btm2cats-%u.dmp DIRECTORY=DP_DIR filesize=1900M CONSISTENT=Y DIRECT=Y
Export: Release 11.2.0.1.0 - Production on Wed Jul 31 22:44:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2013-07-31 22:44:29', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode Parameter: "direct=TRUE" Location: Command Line, ignored.
Legacy Mode Parameter: "file=btm2cats-110.dmp" Location: Command Line, Replaced with: "dumpfile=btm2cats-2.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
… and getting an error:
...
ORA-31693: Table data object "BTM2CATS"."APM_PACKAGE_VERSIONS" failed to load/unload and is being skipped due to error:
ORA-00922: missing or invalid option
...
All other ORA-00922 errors I see references to are when invoking «CREATE TABLE» or perhaps a related «ALTER». This error does not seem to be appropriate for occurring in the middle of a properly-invoked expdp invocation. Can anyone explain what this error means in this context and what I might do to try and fix it?