Missing or invalid option oracle ошибка - Исправление ошибок и поиск оптимальных решений проблем

I entered the following SQL commands in Oracle but it complained «ORA-00922: missing or invalid option»

CREATE TABLE Student (
    StuID     NUMBER(15),
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    PRIMARY KEY (StuID))

CREATE TABLE Program (
    ProCode       VARCHAR2(12),
    ProTitle      VARCHAR2(50),
    PRIMARY KEY (ProCode))

WHY???

asked Mar 25, 2012 at 7:51

If you are using the dreaded HTML GUI (inside the browser) of OracleXE then that does not support running more than one statement.

Use SQL Developer, SQL*Plus or any other GUI tool instead.

answered Mar 25, 2012 at 9:33

CREATE TABLE Student (
    StuID     NUMBER(15),
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    CONSTRAINT PK_STUID PRIMARY KEY (StuID))

Found the answer here.

Edit:

Also, try using / as a statement separator, instead of a ;

Machavity♦

30.5k27 gold badges90 silver badges100 bronze badges

answered Mar 25, 2012 at 8:06

egruninegrunin

24.5k8 gold badges49 silver badges92 bronze badges

Try no to define the size of StuID.
also add constraint key work
and just to make sure use DROP before CREATE
like this:

DROP TABLE Student;

CREATE TABLE Student (
    StuID     NUMBER,
    StuName   VARCHAR2(50),
    Phone     VARCHAR2(20),
    constraint pk_Student PRIMARY KEY (StuID));

DROP TABLE Program;

CREATE TABLE Program (
    ProCode       VARCHAR2(12),
    ProTitle      VARCHAR2(50),
    constraint pk_Program PRIMARY KEY (ProCode));

answered Mar 25, 2012 at 7:58

Dor CohenDor Cohen

16.6k23 gold badges91 silver badges157 bronze badges

Источник

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.

Источник

Содержание

How to fix this error ORA-00922: missing or invalid option
Answers
ORA-00922: missing or invalid option while creating table
Answers
ORA-00922: missing or invalid option
Comments
ORA-00922: missing or invalid option
Answers

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??

Источник

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

Источник

ORA-00922: missing or invalid option

I keep getting the ORA-009223 error when i try to run any of these commands thru the afiedt.buf:

set echo off
set termout off
/

ie, when i am in the afiedt.buf, i will save this and go back to the sqlplus command line and then enter /, getting the following:
SQL> /
set echo off
*
ERROR at line 1:
ORA-00922: missing or invalid option

Any pointers would be appreciated, thanks!

these both lines are for sql*plus and not for sql. You cannot use them in efiedit.buf. Just write these commands on sql prompt and enter.

Ok thanks all who posted, appreciate the super fast response!

Have a great week!

contents of sample file a.sql
——————————————
SET TIMING ON
SET TIME ON
SET ECHO OFF
select sysdate from dual;

Elapsed: 00:00:00.01
10:06:42

SQL> get a
1 SET TIMING ON
2 SET TIME ON
3 SET ECHO OFF
4* select sysdate from dual;
10:06:45 SQL> /
SET TIMING ON
*
ERROR at line 1:
ORA-00922: missing or invalid option

Observation
——————
You get the following error , when you try to get the contents and run the query
as set is not an sql statement as it is an interactive sql .

Источник

ORA-00922: missing or invalid option

I am trying to create a menu of choices, but when I run the script, it shows:

1: CREAR USUARIO

2: ELIMINAR USUARIO

3: DESBLOQUEAR USUARIO

Ingresar opcion del 1-4: 1

Ingresar nombre de usuario: prueba

ERROR at line 1:

ORA-00922: missing or invalid option

ORA-06512: at line 11

set serveroutput on

set verify off

prompt MENU:

prompt 1: CREAR USUARIO

prompt 2: ELIMINAR USUARIO

prompt 3: DESBLOQUEAR USUARIO

prompt 4: SALIR

accept opcion prompt «Ingresar opcion del 1-4: «

accept username prompt «Ingresar nombre de usuario: «

declare

v_opcion number;

v_username varchar2(30);

v_statement varchar2(1000);

begin

v_opcion:=&opcion;

v_username:=upper(‘&username’);

case

when(v_opcion=1) then

v_statement:=’create user ‘ || v_username || ‘identified by 123456’;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario creado con exito, su contraseña por defecto es 123456. Cámbielo. ‘);

when(v_opcion=2) then

v_statement:=’drop user v_username’;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario eliminado con exito’);

dbms_output.put_line(v_statement);

when (v_opcion=3) then

v_statement:=’alter user ‘ || v_username || ‘account lock’;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario desbloqueado con exito’);

when (v_opcion=4) then

dbms_output.put_line(‘Bye Bye’);

else

dbms_output.put_line(‘La opción no se encuentra’);

end case;

end;

exit

Answers

Watch your spaces:

v_statement:=’create user ‘ || v_username || ‘identified by 123456’;

I think you want

Instead of CASE ..

Also, I suggest you put your ANONYMOUS PL/SQL block as a PROCEDURE (in a package).

The code becomes easier to control access, easier to debug, easier to maintain, and (most importantly) allow you to use modern programming environments such as Oracle Application Express (APEX).

Once it is in a package, you can give an IT person’s account so that they have the ability to ‘reset passwords’ without them having DBA or SYSADMIN privileges.

Finally. make sure you add some checks to your code so that they can’t drop the SYS or XDB schema.

Whenever you wrote dynamic SQL, test it by displaying the dynamic text instead of executing it. For example:

SET SERVEROUTPUT ON

v_username varchar2 (30);

v_statement varchar2 (1000);

when (v_opcion=1) then

v_statement:=’create user ‘ || v_username || ‘identified by 123456’;

dbms_output.put_line (v_statement || ‘

Thank very much. How do I repeat the menu until to choice 4. I can´t use prompt within plsql.

Sorry, it’s unclear what you want.

If you’re asking about how to run the same SQL*Plus script over and over, depending on what input you get from the user, then see this thread: https://forums.oracle.com/message/9599683

It sounds like you are trying to develop an application.

As such, you should use some modern day tools to do so.

Put the code in a package and access it via Application Express [APEX] (which comes free with the Oracle Database).

This application would be very easy to build with APEX.

I´m trying to make a very simple menu of choice.

I have created 3 files:

set serveroutput on

set verify off

prompt MENU:

prompt 1: CREAR USUARIO

prompt 2: ELIMINAR USUARIO

prompt 3: DESBLOQUEAR USUARIO

prompt 4: SALIR

accept opcion prompt «Ingresar opcion del 1-4: «

accept username prompt «Ingresar nombre de usuario: «

declare

v_opcion number;

v_username varchar2(30);

v_statement varchar2(1000);

begin

when(v_opcion=1) then

@c:sql1createuser;

@c:sql1showmenu;

when(v_opcion=2) then

v_statement:=’drop user ‘ || v_username;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario eliminado con exito’);

dbms_output.put_line(v_statement);

when (v_opcion=3) then

v_statement:=’alter user ‘ || v_username || ‘ account lock’;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario desbloqueado con exito’);

when (v_opcion=4) then

dbms_output.put_line(‘Bye Bye’);

dbms_output.put_line(‘La opción no se encuentra’);

end case;

v_statement:=’create user ‘ || v_username || ‘ identified by 123456’;

execute immediate(v_statement);

dbms_output.put_line(‘Usuario creado con exito, su contraseña por defecto es 123456. Cámbielo. ‘);

set serveroutput on

set verify off

prompt MENU:

prompt 1: CREAR USUARIO

prompt 2: DESBLOQUEAR USUARIO

prompt 3: ELIMINAR USUARIO

prompt 4: SALIR

accept selection prompt «Ingresar opcion del 1-4: «

accept username prompt «Ingresar nombre de usuario: «

When I run SQL> @c:sql1choice.sql

I have an error:

1: CREAR USUARIO

2: ELIMINAR USUARIO

3: DESBLOQUEAR USUARIO

Ingresar opcion del 1-4: 1

Ingresar nombre de usuario: test06

SP2-0552: Bind variable «SALIR» not declared.

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 — Production

Ask Mike said, SQL*Plus is the wrong tool for this job,

If you really, really must use SQL*Plus, expect it to be tedious.

You’re calling scripts in the middle of a PL/SQL block. That’s allowed, but I don’t think it’s doing what you want. When you call a script inside a PL/SQL block, for example

the scripts are called before the PL/SQL is compiled, and tthe effect is the same as inserting the contents of the script at that point. It’s exactly the same as if you had written out the contents of those files in those places:

v_statement:=’create user ‘ || v_username || ‘ identified by 123456’;

dbms_output.put_line(‘Usuario creado con exito, su contraseña por defecto es 123456. Cámbielo. ‘);

set serveroutput on
set verify off

prompt MENU:
prompt 1: CREAR USUARIO
prompt 2: DESBLOQUEAR USUARIO
prompt 3: ELIMINAR USUARIO
prompt 4: SALIR

accept selection prompt «Ingresar opcion del 1-4: «
accept username prompt «Ingresar nombre de usuario: «

SET, PROMPT and ACCEPT are not valid PS/SQL commands, so you get an error when the code is compiled.

(By the way, I had to change the sub-script calls to run on my system. That’s not significant for this problem; I got the same error with my shorter calls as you did.)

Once again, SQL*Plus is the wrong tool for this job. APEX would be a much better choice. I’m not sure which would take more time, learning APEX or learning hiow to do this in SQL*Plus, but even if learning APEX takes twice as long, it’s a much better investment of your time and effort.

Источник

Содержание

Ora 00922 что за ошибка
How to fix this error ORA-00922: missing or invalid option
Answers
Importing generated DDL for VIEWs and FUNCTIONs gives ORA-00922: missing or invalid option
Best Answer
Answers
ORA-00922: missing or invalid option while creating table
Answers

Ora 00922 что за ошибка

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:

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))

CREATE table employee
(employee_id(5) NOT NULL, employee_name varchar2(30), employment_length varchar2 (40))

Источник

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

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.

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.

CREATEORREPLACEEDITIONABLEFUNCTION»OEHR_GET_PHONE_NUMBER_F»
(p_inINTEGER,p_phonelistoehr_phone_list_typ)
RETURNVARCHAR2AS
TYPEphone_listISVARRAY(5)OFVARCHAR2(25);
phone_outvarchar2(25):=null;
v_sizeINTEGER;
BEGIN
IFp_phonelist.FIRSTISNULLOR
p_in>(p_phonelist.LAST+1)-p_phonelist.FIRSTTHEN
RETURNphone_out;
ELSE
phone_out:=p_phonelist(p_in);
RETURNphone_out;
ENDIF;
END;
/
CREATEORREPLACEFORCEEDITIONABLEVIEW»OEHR_ACCOUNT_MANAGERS»(«ACCT_MGR»,»REGION»,»COUNTRY»,»PROVINCE»,»NUM_CUSTOMERS»)AS
SELECTc.account_mgr_idACCT_MGR,
cr.region_idREGION,
c.cust_address.country_idCOUNTRY,
c.cust_address.state_provincePROVINCE,
count(*)NUM_CUSTOMERS
FROMoehr_customersc,oehr_countriescr
WHEREc.cust_address.country_id=cr.country_id
GROUPBYROLLUP(c.account_mgr_id,
cr.region_id,
c.cust_address.country_id,
c.cust_address.state_province)
/

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.

-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,

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.)

update my_table set data=2 where pk=1;

update another_table set data = 4 where pk=1;

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

Источник

I’ve just run the installer for Oracle 11gR2 on a Windows Server 2008 (64-bit) OS.

I encountered the «ORA-00922: missing or invalid option» error at the last stage of installation when it was doing something like «cloning the database». I ignored the error and then immediately got a «ORA-28000: the account is locked» error. The installer then completed successfully.

I looked in the logs for the installation («C:appadministratorcfgtoollogsdbcaorcl») and found one file named «postScripts.log» which contains these two error messages. Here is the contents of that file:

     1
     1
alter user CTXSYS account unlock identified by ******
*
ERROR at line 1:
ORA-00922: missing or invalid option


ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
dropping default lexer preference...
SP2-0640: Not connected
dropping default wordlist preference...
SP2-0640: Not connected
dropping default stoplist preference...
SP2-0640: Not connected
dropping default policy...
SP2-0640: Not connected
SP2-0640: Not connected
Enter value for lang_abbr: SP2-0310: unable to open file "C:appAdministratorproduct11.2.0dbhome_2ctxadmindefaultsdrdefset.sql"

Has anyone else encountered this error before, or know how to resolve it? Will the database function correctly?

Thanks in advance!

asked May 29, 2014 at 9:56

I found a solution on my own!

It seems that there is a bug in the Oracle installer — it doesn’t like it if you start your system password with a number.

I completely uninstalled Oracle using the deinstall utility and then reinstalled Oracle, this time specifying a system password beginning with a letter. This solved the problem!

I hope this helps anyone else encountering this annoying issue.

Additionally, user HanFromBs found that the installer doesn’t like special characters (- or +) at the start or end of the password, so watch out for this too.

answered May 29, 2014 at 14:41

user1578653user1578653

4332 gold badges8 silver badges21 bronze badges

Источник