Dynamic SQL

A quick post before taking my daughter to her first concert BIGBANG ALIVE GALAXY TOUR 2012 (Korean Pop)

Configuration:
For every <schema>, there exists the following tablespaces: <schema>_DATA (DEFAULT tablespace) and <schema>_INDEX.

DON’T:
declare
v_ts varchar2(30);
begin
select user||’_index’ into v_ts from dual;
execute immediate ‘create index ix on t(id) tablespace ‘||v_ts;
end;
/

If you are paid by the keystroke, then it’s great.

DO:
EXEC EXECUTE IMMEDIATE ‘CREATE INDEX ix ON T(id) TABLESPACE ‘||USER||’_index’;

DON’T:
ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) USING INDEX;

Index is created in DEFAULT <schema>_DATA tablespace.

DO:
EXEC EXECUTE IMMEDIATE ‘ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) USING INDEX TABLESPACE ‘||USER||’_index’;

PK VARIATIONS:
EXEC EXECUTE IMMEDIATE ‘ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) RELY USING INDEX tablespace ‘||USER||’_index’;
EXEC EXECUTE IMMEDIATE ‘ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) USING INDEX tablespace ‘||USER||’_index ENABLE NOVALIDATE’;

Constraint and index name are the same.

DEV01:(SCOTT@orcl):PRIMARY> select constraint_name from user_constraints where table_name='T';
CONSTRAINT_NAME
------------------------------
 PK_T
Elapsed: 00:00:00.11
DEV01:(SCOTT@orcl):PRIMARY> select index_name, tablespace_name from user_indexes where table_name='T';
INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
PK_T                           SCOTT_INDEX
Elapsed: 00:00:00.01
DEV01:(SCOTT@orcl):PRIMARY>

Don’t like seeing false errors?
EXEC EXECUTE IMMEDIATE ‘ALTER TABLE t DROP PRIMARY KEY CASCADE’; EXCEPTION WHEN OTHERS THEN NULL;
EXEC EXECUTE IMMEDIATE ‘DROP TABLE t1′; EXCEPTION WHEN OTHERS THEN NULL;

Some times, it might not be a good idea to ignore errors. In this case, I wanted to drop PK on table t but incorrectly (purposely) coded table t1.

DEV01:(SCOTT@orcl):PRIMARY> EXEC EXECUTE IMMEDIATE 'alter table t1 DROP PRIMARY KEY CASCADE'; EXCEPTION WHEN OTHERS THEN NULL;
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
DEV01:(SCOTT@orcl):PRIMARY> EXEC EXECUTE IMMEDIATE 'ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) USING INDEX tablespace '||USER||'_index ENABLE NOVALIDATE';
BEGIN EXECUTE IMMEDIATE 'ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY(id) USING INDEX tablespace '||USER||'_index ENABLE NOVALIDATE'; END;

*
ERROR at line 1:
ORA-02260: table can have only one primary key
ORA-06512: at line 1

Elapsed: 00:00:00.05
DEV01:(SCOTT@orcl):PRIMARY> 

About these ads