CODEWITHSUNDEEP
oracle-databaseplsql
SaintLike
SaintLike

Reputation: 9589

Oracle errors ORA-00922 and ORA-06512

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 !

Upvotes: 0

Views: 1038

Answers (2)

aljassi
aljassi

Reputation: 246

Create or replace table

Create or replace view --> yes but not create or replace table. This Option does not exist.

Upvotes: -1

fabribara
fabribara

Reputation: 140

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||'" (

Upvotes: 2

Related Questions