Home » SQL & PL/SQL » SQL & PL/SQL » ORA 00922 MISSING OR INVALID OPTION (ORACLE 12C , ORACLE FORMS AND REPORTS 12C)
ORA 00922 MISSING OR INVALID OPTION [message #685494] Mon, 24 January 2022 21:23 Go to next message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

Hi,

I am trying to replecate a script of an existing table and renaming the table name to create another table with a different table name.

The script is mentioned below

CREATE TABLE T_APPLN_IMAGE
(
  TIM_PK             NUMBER(10),
  TIM_APPLN_PK       NUMBER(10),
  TIM_PHOTO          CLOB,
  TIM_SIGN           CLOB,
  TIM_PHOTO_ENCODED  BLOB,
  TIM_SIGN_ENCODED   BLOB
)
LOB (TIM_PHOTO) STORE AS SECUREFILE (
  TABLESPACE TS_SAI
  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)
LOB (TIM_SIGN) STORE AS SECUREFILE (
  TABLESPACE TS_SAI
  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)
LOB (TIM_PHOTO_ENCODED) STORE AS SECUREFILE (
  TABLESPACE TS_SAI
  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)
LOB (TIM_SIGN_ENCODED) STORE AS SECUREFILE (
  TABLESPACE TS_SAI
  ENABLE       STORAGE IN ROW
  CHUNK       8192
  RETENTION
  NOCACHE
  LOGGING)
TABLESPACE TS_SAI
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
-- There is no statement for index SAIBANG.SYS_C00112047.
-- The object is created automatically by Oracle when the parent object is created.
ALTER TABLE T_APPLN_IMAGE ADD (
  PRIMARY KEY
  (TIM_PK)
  USING INDEX
    TABLESPACE TS_SAI
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE T_APPLN_IMAGE ADD (
  CONSTRAINT FK_TIM_APPLN_PK 
  FOREIGN KEY (TIM_APPLN_PK) 
  REFERENCES T_APPLICATION_HDR (APPLN_PK));
When i execute this script, it gives an error
[Error] Execution (55: 11): ORA-00922: missing or invalid option[b][/b][color=darkred]
The screenshot of the error message is attached for kind perusal.

I would like to have guidance in solving this issue.

/foru/forum/fa/14585/0/


[EDITED by LF: fixed [code] tags]

[Updated on: Tue, 25 January 2022 13:36] by Moderator

Report message to a moderator

Re: ORA 00922 MISSING OR INVALID OPTION [message #685510 is a reply to message #685494] Tue, 25 January 2022 13:34 Go to previous messageGo to next message
Littlefoot
Messages: 21713
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you keep what you do need (and remove what you don't), table is created with no errors.

First, table that's referenced by table whose CREATE TABLE statement you posted (otherwise, you can't create a foreign key constraint):

SQL> CREATE TABLE t_application_hdr (
  2      appln_pk NUMBER(10) PRIMARY KEY
  3  );

Table created.

SQL>
Now, shortened code you posted:

SQL> CREATE TABLE t_appln_image (
  2      tim_pk            NUMBER(10)
  3          CONSTRAINT pk_tapplnimg PRIMARY KEY,
  4      tim_appln_pk      NUMBER(10)
  5          CONSTRAINT fk_tim_appln_pk
  6              REFERENCES t_application_hdr ( appln_pk ),
  7      tim_photo         CLOB,
  8      tim_sign          CLOB,
  9      tim_photo_encoded BLOB,
 10      tim_sign_encoded  BLOB
 11  );

Table created.

SQL>
Re: ORA 00922 MISSING OR INVALID OPTION [message #685512 is a reply to message #685510] Tue, 25 January 2022 21:50 Go to previous message
lacchhii
Messages: 151
Registered: May 2009
Location: bangalore
Senior Member

Thanks a lot sir. will try as suggested .I used to copy the entire script as i had posted and table used to get created when i was using oracle 10g. i faced this problem only after it was upgraded to oracle 12c.

Any how i will try to execute it as suggested by you sir.
Previous Topic: Hex Value to Base64 Value Convertion
Next Topic: transpose logic
Goto Forum:
  


Current Time: Mon May 16 16:26:34 CDT 2022