Oracle errors ORA-00922 and ORA-06512

Question

I was trying to run the following script:

SET serveroutput ON
DECLARE

  v_nbr_part        NUMBER := 2;
  v_nbr_subpart     NUMBER := 2;

  v_table_name      VARCHAR2(100) := 'XPTO';
  v_table_shortname     VARCHAR2(100) := 'XPTO';

  v_part_first      NUMBER;
  v_part_last       NUMBER;
  v_subpart_first   NUMBER;
  v_subpart_last    NUMBER;
  v_sql             CLOB := '';
  v_sql_part        CLOB := '';
  v_sql_subpart     CLOB := '';
  v_part_dynam      CLOB := '';
  v_part_keys       CLOB := '';

BEGIN          

  IF v_nbr_part > 0 THEN

      IF v_nbr_subpart > 0 THEN
        v_subpart_first := 0;
        v_subpart_last := v_nbr_subpart-1;    
        v_part_dynam:='';
        FOR i IN v_subpart_first .. v_subpart_last
        LOOP
           v_part_dynam := v_part_dynam || ' SUBPARTITION SP' || i ||' VALUES ('||i||')';
           IF i <> v_subpart_last THEN
              v_part_dynam := v_part_dynam || ',';
           END IF;      
         END LOOP;
         v_sql_subpart:= where_to_save('LDR_SUBTYPE',  v_part_dynam);
         v_part_keys:= ' , "LDR_SUBTYPE" NUMBER NOT NULL ENABLE ';
       END IF;

       v_part_first := 1;
       v_part_last := v_nbr_part;    

       v_sql_part:= to_clob(' PARTITION BY RANGE (PART_KEY) interval(numtodsinterval(1,''day'')) ')|| v_sql_subpart || 
            to_clob(' ( partition EMPTY values less than (to_date(''01-01-2013'',''DD-MM-YYYY'') ) ) ');
       v_part_keys:= ', "PART_KEY" TIMESTAMP (6) NOT NULL ENABLE ' || v_part_keys;
  END IF;

  v_sql:=to_clob(
'CREATE OR REPLACE TABLE "'||v_table_name||'" (
"SEQUENCENUMBER" NUMBER NOT NULL ENABLE,
"LDRID" VARCHAR2(100),
"LDRTYPE" VARCHAR2(50),
"LDR_SUBTYPE" VARCHAR2(50),
"CREATIONTIMESTAMP" TIMESTAMP (6),
"CORRELATIONID" VARCHAR2(4000),
"PARENTLDRID" VARCHAR2(100),
"NUMBEROFCHILDREN" NUMBER,
"PROTOCOLSESSIONID" VARCHAR2(100),
"ACTIVITYID" VARCHAR2(100),
"ACTIVITYINITIALTIMESTAMP" TIMESTAMP (6),
"ACTIVITYELAPSEDTIME" NUMBER,
"CANCELED" CHAR(1),
"REPLACED" CHAR(1),
"INVALIDATIONTIMESTAMP" TIMESTAMP (6),
"INVALIDATEDBYLDRID" VARCHAR2(100),
"INVALIDATESLDRID" VARCHAR2(100),
"OPERATIONID" VARCHAR2(256),
"ACCOUNTID" VARCHAR2(100),
"TOPLEVELID" VARCHAR2(100),
"RESULTCODE" VARCHAR2(100),
"VERSION" NUMBER,
"LDROBJECT" BLOB,
"OBJMETADATA" BLOB,
"OBJKEY" BLOB,
"OBJKEYHASH" VARCHAR2(200) ') ||
v_part_keys ||
to_clob('
)
LOB (OBJKEY) STORE AS SECUREFILE
LOB (LDROBJECT) STORE AS SECUREFILE 
LOB (OBJMETADATA) STORE AS SECUREFILE
') ||
v_sql_part || to_clob(',TABLESPACE OCS LOGGING  NOCOMPRESS  NOCACHE NOPARALLEL');

dbms_output.put_line(dbms_lob.substr(v_sql,4000,1)); //this is line 84!!!!!!!!!
EXECUTE IMMEDIATE v_sql;


EXCEPTION
 WHEN OTHERS THEN
    DBMS_OUTPUT.Put_Line(DBMS_UTILITY.Format_Error_Backtrace);
    DBMS_OUTPUT.Put_Line(SQLERRM);
END;
/

CREATE OR REPLACE FUNCTION where_to_save(LDR_SUBTYPE VARCHAR2, v_part_dynam OUT CLOB)
RETURN CLOB
IS
BEGIN 
  CASE
      WHEN LDR_SUBTYPE = ('IEC') THEN v_part_dynam := v_part_dynam || 'SUBPARTITION SP0 VALUES (IEC)';

      ELSE  v_part_dynam := v_part_dynam || 'SUBPARTITION SP1 VALUES (OTHER)';
  END CASE;

  RETURN v_part_dynam;   

END where_to_save;

The script look good to me, but every time I try to run it I get the following errors:

ORA-06512: line 84

ORA-00922: missing or invalid option

I've searched a lot and the only thing I've found about fixing this was "contact your DBA". What is wrong with my script? How can I fix this? P.S.: I've commented line 84 !

Get rid of your execute immediate right below the 84-th line. Do you still experience this error? — neshkeev
Wow, you're some kind of wizard lol. Thanks a lot, that solved both errors. Why was that line causing it? — SaintLike
Because the line SET serveroutput ON is not a part of your script, so the line with execute immediate is 84-th line but it doesn't solve the problem, your table hasn't been craeted — neshkeev
true, I've just noticed that... any solutions? I've created the table before... I don't know why's not working now — SaintLike
show us the table structure you are trying to create. Add it to your post — neshkeev

fabribara fabribara · Accepted Answer · 2014-08-07T05:01:26

Tables can't be created or replaced, try dropping it first or build a pl that drops table if exists.

'CREATE TABLE "'||v_table_name||'" (

Oracle errors ORA-00922 and ORA-06512

2 Answers