Missing or invalid option oracle ошибка

I’m trying to create a  script that generates sequences for each table in my data base.

But it keeps givving me this error:

ORA-00922: missing or invalid option

The code:

DECLAREV_OWNER_NAME   VARCHAR2(100) := 'HAZARDV2';V_TABLE_NAME VARCHAR2(500) ;V_PK_COLUMN  VARCHAR2(500) ;V_MAX_ID     NUMBER ;  CURSOR ALL_TABLES IS    SELECT table_name            FROM all_tables             WHERE OWNER = V_OWNER_NAME            ORDER BY TABLE_NAME ;BEGIN  FOR I IN ALL_TABLES LOOP    --STORING THE TABLE NAME RETURNED FROM CURSOR  V_TABLE_NAME := I.TABLE_NAME;     IF V_TABLE_NAME LIKE 'PS_TXN' THEN NULL;ELSE-- GETTING THE PK COLUMN NAME   SELECT 'SEQ_'||NVL(column_name,999) INTO V_PK_COLUMNFROM all_cons_columns WHEREOWNER = V_OWNER_NAME ANDconstraint_name = (  SELECT constraint_name FROM user_constraints   WHERE TABLE_NAME = V_TABLE_NAME AND  CONSTRAINT_TYPE = 'P');  ----- THIS IS THE LINE THAT THE ERROR POINTS TO -------     execute immediate    'CREATE OR REPLACE SEQUENCE ' || V_PK_COLUMN || ' START WITH (SELECT NVL(MAX('||V_PK_COLUMN||'),0)+1 FROM '||V_TABLE_NAME||') INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE ORDER';     END IF;  END LOOP;    END;

Thank you for your time.

Gado

Hi Friends,

This is a very tricky and suspenseful error called ORA-00922 with strange detail ORA-00922: missing or invalid option.

This ORA-00922 itself shows only message code missing or invalid option. So many times people are confusing after got this typical Oracle error.

If we check error code from

We can see the message for correction of the same error is simple that check SYNTAX of executed command and correct it.

Sometimes we can get this error of missing comma (,) or missing bracket () or missing length of long datatypes. Sometimes also get the same error due to invalid datatype define in PL/SQL code. The main and important thing is only that wrong SYNTAX using in coding or SQL execution.

All the best,

Thanks and regards,

Dbametrix Solutions

Expert Remote DBA team with low-cost remote plans.

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.

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.

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.

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.

Источник

How to fix this error ORA-00922: missing or invalid option

I’m trying to create a script that generates sequences for each table in my data base.

But it keeps givving me this error:

ORA-00922: missing or invalid option

Thank you for your time.

Answers

EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.

What version of oracle are you using?

I don’t see «OR REPLACE» clause in sequence creation.

Whenever you do dynamic SQL, put the entire SQL statement into a string variable, so that, during debugging, it’s easy to display the dynamic text before you execute it. For example:

That way, if you get an error, you can see exactly what the command was, and test that command separately to make sure that it works.

In this case, you’ll find that the command does not work. Review the syntax of the CREATE SEQUENCE command to find out why. (CREATE SEQUENCE has no OR REPLACE option, and the START WITH value must be a literal).

Actually, this is just the reverse of what you should do. You should test the statement outside of PL/SQL first, to make sure you know exactly what the dynamic test should be, and only then should you start writing the PL/SQL code.

Thanks guys for your quick response

I fixed my code as you suggested :

I have anew error now :

PL/SQL: ORA-00942: table or view does not exist

returned by this statement :

SELECT NVL(MAX(V_PK_COLUMN),0)+1

INTO V_START_WITH

FROM V_TABLE_NAME;

I tried this statement outside it runs if i typed the table name literally but it gives this error if the table name is stored in a variable . why??

Источник

Importing generated DDL for VIEWs and FUNCTIONs gives ORA-00922: missing or invalid option

Given:

— Schema A (source) : Apex 5

— Schema B (destination) : Apex 5

Task:

— Migrate only functions and views from Schema A to Schema B

Problem:

— When exporting the DDL script for functions and views, they are not logically ordered. In other words, for example, it executes scripts to create a view that uses a function that it is not created yet. It is coming below.

ORA-00922: missing or invalid option

00922. 00000 — «missing or invalid option»

Question:

How to export DDL script that executes without such errors

Best Answer

Which is entirely expected on remotely hosted environments as you would not expect to have DBA privileges.

Off the top of my head I don’t know of any other way to check this. Suggest you ask the hosting provider whether the service supports edition-based redefinition. If it does, then they need to enable it for your schema. If not, you’ll have to edit the scripts to remove the editionable keyword.

Answers

I don’t think that is the reason for the error.

Firstly, the Export DDL utility (at least in 5.1) outputs CREATE OR REPLACE FORCE VIEW. which will create the view regardless of any object dependency failures, showing an ORA-24344: success with compilation error message should any occur. Secondly, following an attempt to create the view without the FORCE keyword I would expect a missing dependency to result in ORA-00904: «FUNCTION_NAME»: invalid identifier, not ORA-00922.

Suggest you post the script.

I forgot to mention that I ran the script from the Oracle SQL Developer 4.2 on Mac.

You are right. The error is not related to logical order of object creation. That’s why I changed the title of the thread to make sense.

Here are the first FUNCTION and VIEW

Note that the error appears in SQL Developer

In addition, running the generated DDL script from Apex gives the following

If I remove all EDITIONABLE words from the generated DDL script file, it works!!

I would appreciate any help

Is it a bug in Generate DDL ?

Think the source and target environments and schema configurations are more likely to be of significance.

1. CREATEORREPLACEEDITIONABLEFUNCTION»OEHR_GET_PHONE_NUMBER_F»
2. (p_inINTEGER,p_phonelistoehr_phone_list_typ)
3. RETURNVARCHAR2AS
4. TYPEphone_listISVARRAY(5)OFVARCHAR2(25);
5. phone_outvarchar2(25):=null;
6. v_sizeINTEGER;
7. BEGIN
8. IFp_phonelist.FIRSTISNULLOR
9. p_in>(p_phonelist.LAST+1)-p_phonelist.FIRSTTHEN
10. RETURNphone_out;
11. ELSE
12. phone_out:=p_phonelist(p_in);
13. RETURNphone_out;
14. ENDIF;
15. END;
16. /
17. CREATEORREPLACEFORCEEDITIONABLEVIEW»OEHR_ACCOUNT_MANAGERS»(«ACCT_MGR»,»REGION»,»COUNTRY»,»PROVINCE»,»NUM_CUSTOMERS»)AS
18. SELECTc.account_mgr_idACCT_MGR,
19. cr.region_idREGION,
20. c.cust_address.country_idCOUNTRY,
21. c.cust_address.state_provincePROVINCE,
22. count(*)NUM_CUSTOMERS
23. FROMoehr_customersc,oehr_countriescr
24. WHEREc.cust_address.country_id=cr.country_id
25. GROUPBYROLLUP(c.account_mgr_id,
26. cr.region_id,
27. c.cust_address.country_id,
28. c.cust_address.state_province)
29. /

If I remove all EDITIONABLE words from the generated DDL script file, it works!!

I suspect that schema A has editions enabled while schema B does not. Check this using:

Thanks again. I appreciate your help.

Schema A is on apex.oracle.com

Schema B is on Apex Hosted Plan

The result of your query in both

ORA-00942: table or view does not exist

Источник

ORA-00922: missing or invalid option while creating table

We are getting error ORA-00922: missing or invalid option while trying to run fololwing create table statement.

CREATE TABLE «odb_test_header» (

«fiscalyear» NUMBER(4, 0) NOT NULL,

«companyid» NUMBER(4, 0) NOT NULL,

«documentnumber» NUMBER(15, 0) NOT NULL,

«postingdate» NUMBER(8, 0) NOT NULL,

«enteredby» VARCHAR2(100) NOT NULL,

«entereddate» NUMBER(8, 0) NOT NULL,

«entrycomment» VARCHAR2(100) NOT NULL,

«amount» NUMBER(15, 2) NOT NULL,

«currency» VARCHAR2(3) NOT NULL,

«docguid» VARCHAR2(15) NOT NULL,

«binary» LONG RAW NOT NULL,

«bool» NUMBER(1) NOT NULL,

«i16» NUMBER(5, 0) NOT NULL,

«i16u» NUMBER(5, 0) NOT NULL,

«i32» NUMBER(10, 0) NOT NULL,

«i32u» NUMBER(10, 0) NOT NULL,

«i64» NUMBER(19, 0) NOT NULL,

«i64u» NUMBER(19, 0) NOT NULL,

«sng» NUMBER(7, 6) NOT NULL,

«dbl» NUMBER(17, 6) NOT NULL,

«dec» NUMBER(28, 6) NOT NULL,

«str» VARCHAR2(255) NOT NULL,

CONSTRAINT odb_test_header_PK PRIMARY KEY («fiscalyear», «companyid», «documentnumber»)

CREATE UNIQUE INDEX odb_test_header_UIX1 ON «odb_test_header» («docguid»);

CREATE INDEX odb_test_header_IX2 ON «odb_test_header» («enteredby», «entereddate»);

Can any one suggest what could be the issue?

We are using .Net 4.7 / DbProvider = «Oracle.ManagedDataAccess.Client» or DbProvider = «System.Data.OracleClient»

PS : this statement can be executed on SqlDevelopper.

Answers

IMO it is a terrible idea to make any table or column names case sensitive like you are doing.

If it works in sql developer then theres nothing wrong with it, it must be how you are executing it elsewhere.

So how are you executing it elsewhere to get the error?

Why exactly do you want to run DDL like this and not through a script using a properly managed process? Applications shouldnt generally do explicit DDL like this.

The statements are also valid from SQL*Plus. The problem may relate to the use of the double-quote marks.

You will (probably) find that when the statement succeeds the table name and its column names are stored in the database in lower case while the index name and any constraint names are in upper case. It’s possible that the Net provider service is doing something like stripping out some of the double quote marks but leaving others in place. If you can enable SQL_TRACE before executing the statement (or if you can capture the text transmitted by .Net you can check what text actually arrives at the database.

-1- remove the double quotes so that names will be treated in uppercase:

CREATE TABLE odb_test_header (

fiscalyear NUMBER(4, 0) NOT NULL,

companyid NUMBER(4, 0) NOT NULL,

((this is equivalent to, for example,

CREATE TABLE ODB_TEST_HEADER (

FISCALYEAR NUMBER(4, 0) NOT NULL,

COMPANYID NUMBER(4, 0) NOT NULL,

Your life will be easier (the name of table and columns are not case sensitive if you don’t use double quotes, so you can successfully do things like SELECT fiscalyear, FiscalYear, FISCALYEAR, FiScALyEAr FROM odb_test_header) and the problem might disappear, I guess that the tool that you use is not 100% OK with the lowercase names or double quotes.

-2- welcome to the forums; I see that you could already pick a non-default avatar, may I suggest you to modify also your display name to something more «human-friendly»? You might find a nice unique name without having to use something like 2611cea0-d9. 😉 Please have a look at

I’ve tried executions show below, but the results are same again

1 — CREATE TABLE odb_test_header (fld1 NUMBER(4, 0), fld2 NUMBER(4, 0));

2 — CREATE TABLE ODB_TEST_HEADER (FLD1 NUMBER(4, 0), FLD2 NUMBER(4, 0));

Are you just creating the table in the ExecuteNonQuery(), or creating the indexes as well? If the latter, I wouldn’t be surprised if you can only execute one statement at a time.

Edit: n/m, looks like only the create table.

‘;’ doesn’t belong in the commandText, it is only used as the syntax of a client tool to submit the statement, it is not part of the SQL

thanx for your helpful reply

But I dont know how to execute multiple commands such as (insert, insert, insert, delete, update etc.)

You can do multiple DML statements in one call by packaging them up into PL/SQL, ideally with a stored procedure but an anonymous block works okay for one offs. In PL/SQL ‘;’ is used to separate statements fine:

update my_table set data=2 where pk=1;

update another_table set data = 4 where pk=1;

But you can’t do DDL (create table, create index, alter. etc) directly in PL/SQL, nor is it a good idea. So you would have to run that as a separate command — but really DDL should be well known in advance and not part of general application running, you should use something like sql*plus to run in DDL in a controlled way.

And if you do select statements in PL/SQL, you’re only selecting the data for your PL/SQL code, it will need to go to into a variable to be used in the PL/SQL and will not make its way to the calling program unless it is explicitly returned.

Hopefully that’s enough to get you started, don’t forget to locate the documentation and google around, there’s plenty of real life example code out there

Источник