SQL & PL/SQL

2611cea0-d9b3-4eb3-b4ee-46edc8b59bec wrote:
Hi Experts,
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.

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.

Regards

Jonathan Lewis

Hello,

-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 

Best regards,

Bruno Vroman.

Hi

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

Hi

thanx for your helpful reply

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

2611cea0-d9b3-4eb3-b4ee-46edc8b59bec wrote:
Hi
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:

begin

update my_table set data=2 where pk=1;

update another_table set data = 4 where pk=1;

end;

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

I recommend that you use BLOB instead of LONG RAW.

MK