| ORA-00922: missing or invalid option,creating tables dynamic pl/sql [message #37436] |
Thu, 07 February 2002 09:14  |
bhasker
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
I am writing a stored procedure which creates a table at runtime using dynamic pl/sql. I am getting this
error
ORA-00922: missing or invalid option
the code i am using is
v_CreateString := 'CREATE TABLE T4_EXCEPTION_LOG' ||
'(error_id NUMBER(10) NOT NULL,'||
'user_id VARCHAR2(8) NOT NULL,' ||
'error_date DATE NOT NULL,' ||
'error_string VARCHAR2(255),' ||
'error_number NUMBER(10),' ||
'error_type VARCHAR2(3),' ||
'reference_id1 VARCHAR2(50),' ||
'reference_id2 VARCHAR2(50),' ||
'reference_id3 VARCHAR2(50),' ||
'reference_id4 VARCHAR2(50),' ||
'reference_id5 VARCHAR2(50),' ||
'reference_id6 VARCHAR2(50))' ||
'PCTFREE 10 '||
'PCTUSED 40 ' ||
'INITRANS 1 ' ||
'MAXTRANS 255 '||
'TABLESPACE CBS_DATA' ||
'STORAGE ( '||
'INITIAL 8192 '||
'NEXT 4096 '||
'PCTINCREASE 1 ' ||
'MINEXTENTS 1 ' ||
'MAXEXTENTS 249 )';
DBMS_SQL.PARSE(v_cursor, v_CreateString, DBMS_SQL.NATIVE);
v_return := DBMS_SQL.EXECUTE(v_cursor);
COMMIT;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
this code gives the ora-00922 missing or invalid option.
whereas if i exclude the storage part it is working
fine
*******************************************
this code works fine
it creates a table
v_CreateString := 'CREATE TABLE T4_EXCEPTION_LOG' ||
'(error_id NUMBER(10) NOT NULL,'||
'user_id VARCHAR2(8) NOT NULL,' ||
'error_date DATE NOT NULL,' ||
'error_string VARCHAR2(255),' ||
'error_number NUMBER(10),' ||
'error_type VARCHAR2(3),' ||
'reference_id1 VARCHAR2(50),' ||
'reference_id2 VARCHAR2(50),' ||
'reference_id3 VARCHAR2(50),' ||
'reference_id4 VARCHAR2(50),' ||
'reference_id5 VARCHAR2(50),' ||
'reference_id6 VARCHAR2(50))' ||
'PCTFREE 10 '||
'PCTUSED 40 ' ||
'INITRANS 1 ' ||
'MAXTRANS 255 '||
'TABLESPACE CBS_DATA';
DBMS_SQL.PARSE(v_cursor, v_CreateString, DBMS_SQL.NATIVE);
v_return := DBMS_SQL.EXECUTE(v_cursor);
COMMIT;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
*******************************************
can some one help me here.
i will really appreciate it.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
