To create a table in the 11.2.0.2.0 database, a developer should create at least 3 schema elements:
- a table
- a sequence
- a trigger
With the Oracle database 12.1.0.2.0, the last two schema elements, the sequence and the trigger, have been eliminated from the table creation process, and the table could be created with an Identity Column.
In the 11.2.0.2.0 release, the table segment is created immediately, but with the version 12.1.0.2.0, the table segment will be created after the first data insert into that particular table.
The next table shows the difference between 12.1.0.2.0 and 11.2.0.2.0 versions of the Oracle database for the SQL statement CREATE TABLE.
CRETE TABLE Statement Differences
| RELEASE | CREATE TABLE |
|---|---|
| Oracle Database 11.2.0.2.0 |
CREATE TABLE "BILJANA"."LEARN"
( "ID" NUMBER,
"ACTION" VARCHAR2(20),
"RESULT" NUMBER(3,0),
CONSTRAINT "LEARN_PK" PRIMARY KEY ("ID")
) SEGMENT CREATION IMMEDIATE ;
CREATE SEQUENCE "LEARN_SEQ" START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE;
CREATE TRIGGER LEARN_TR_INSERT
BEFORE
INSERT ON LEARN
FOR EACH ROW
DECLARE
l_id number;
BEGIN
SELECT LEARN_SEQ.NEXTVAL INTO l_id FROM DUAL;
:NEW.ID := l_id;
END;
|
|
Oracle Database 12.1.0.2.0 |
CREATE TABLE "BILJANA"."LEARN"
( "ID" NUMBER GENERATED AS IDENTITY
"ACTION" VARCHAR2(20),
"RESULT" NUMBER(3,0)
) SEGMENT CREATION DEFERRED;
|
In the schema “BILJANA” there are already some schema objects that we created in the previous post on 19 July 2015. We created the table HowWeLearn with the columns Id, How, and Value, then, we created the sequence Learn_seq, the trigger Learn_Tr_Insert, and the standalone function Funct_Get_Table_DDL.
We are going to rename the table name and the names of its columns and see what problem that action will cause.
biljana@XE> rename howwelearn to learn; Table renamed. biljana@XE> alter table learn rename column how to action; Table altered. biljana@XE> alter table learn rename column value to result; Table altered.
The names of the table and its columns are changed successfully. There is a recommendation that comments about the table and its columns should be always added to the database using the SQL statement COMMENT ON.
biljana@XE> COMMENT ON TABLE learn IS 'The table LEARN contains the pairs key/value, where the key is the way how we learn, and the value is the % of how much we remember.'; Comment created. biljana@XE> COMMENT ON COLUMN learn.action IS 'How we usually learn something: we listen to the teacher, we listen to the audio tape, we watch a video, etc.'; Comment created. biljana@XE> COMMENT ON COLUMN learn.result IS 'How much we remember, 100% means we remember everything.'; Comment created. biljana@XE> select TABLE_NAME, COMMENTS 2 from user_tab_comments 3 where comments is not null; TABLE_NAME COMMENTS --------------------------------------------------------------------- LEARN The table LEARN contains the pairs key/value, where the key is the way how we learn, and the value is the % of how much we remember. biljana@XE> select * 2 from user_col_comments 3 where comments is not null; TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ COMMENTS --------------------------------------------------------------------- LEARN ACTION How we usually learn something: we listen to the teacher, we listen to the audio tape, we watch a video, etc. LEARN RESULT How much we remember, 100% means we remember everything.
So far, we created the following schema objects:
biljana@XE> select 2 rpad(substr(object_name,1,18),20) 3 || rpad(substr(object_type,1,10), 15) 4 || status OBJECT_STATUS 5 from user_objects 6 where created > sysdate - 3 7 order by 1; OBJECT_STATUS ---------------------------------------------------------------------------------------------------- FUNCT_GET_TABLE_DDL FUNCTION VALID LEARN TABLE VALID LEARN_PK INDEX VALID LEARN_SEQ SEQUENCE VALID LEARN_TR_INSERT TRIGGER INVALID
Some of the schema objects are VALID and the trigger Learn_Tr_Insert is INVALID.
Let check the definition of the sequence LEARN_SEQ and the trigger LEARN_TR_INSERT.
biljana@XE> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER ------------- -------- --------- ------------ - - ---------- LEARN_SEQ 1 1.0000E+28 1 N N 20 61
The definition of the sequence LEARN_SEQ is unchanged.
biljana@XE> select text from all_source where name like'%LEARN%'; TEXT ----------------------------------------------------------------------- TRIGGER LEARN_TR_INSERT before insert on LEARN for each row declare var_id number; begin select learn_seq.nextval into var_id from dual; :new.ID := var_id; end;
As we can see, the change of the table name was done not only in the table definition but also in the trigger definition.
Unfortunately, the trigger has been invalidated as of this change.
What about our function FUNCT_GET_TABLE_DDL? Is it valid or invalid? We expect the function to be INVALID, but it is not. WHY?
Let list the function code that is saved in the static data dictionary view ALL_SOURCE.
biljana@XE> select text from all_source where name like'%FUNCT%';
TEXT
----------------------------------------------------------------------------------------------------
FUNCTION funct_get_table_ddl
RETURN CLOB
AUTHID CURRENT_USER
IS
-- Define local variables.
l_h NUMBER; --handle returned by OPEN
l_th NUMBER; -- handle returned by ADD_TRANSFORM
l_doc CLOB; --returned sql ddl statement
BEGIN
-- Specify the object type.
l_h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(l_h,'SCHEMA','BILJANA');
DBMS_METADATA.SET_FILTER(l_h,'NAME','HOWWELEARN');
-- Request that the metadata be transformed into creation DDL.
l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL');
-- Fetch the object.
l_doc := DBMS_METADATA.FETCH_CLOB(l_h);
-- Release resources.
DBMS_METADATA.CLOSE(l_h);
-- Return the result
RETURN l_doc;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
42 rows selected.
The function body has NOT been changed accordingly. WHY? Because we put the literal strings (highlighted) instead of formal function parameters inside the body of the function.
If we execute the function, the error will be generated:
biljana@XE> select funct_get_table_ddl from dual; ERROR: ORA-06503: PL/SQL: Function returned without value ORA-06512: at "BILJANA.FUNCT_GET_TABLE_DDL", line 42 no rows selected ORA-31603: object "HOWWELEARN" of type TABLE not found in schema "BILJANA"
Again, we will check the validity of the function:
biljana@XE> select 2 rpad(substr(object_name,1,20),25) 3 || rpad(substr(object_type,1,10), 15) 4 || status OBJECT_STATUS 5 from user_objects 6 where created > sysdate - 3 7 order by 1; OBJECT_STATUS ---------------------------------------------------------------------------------------------------- FUNCT_GET_TABLE_DDL FUNCTION VALID LEARN TABLE VALID LEARN_PK INDEX VALID LEARN_SEQ SEQUENCE VALID LEARN_TR_INSERT TRIGGER INVALID
The function is still valid and the trigger is still invalid.
As of the trigger, it should only be recompiled:
biljana@XE> alter trigger learn_tr_insert compile; Trigger altered. biljana@XE> select 2 rpad(substr(object_name,1,20),25) 3 || rpad(substr(object_type,1,10), 15) 4 || status OBJECT_STATUS 5 from user_objects 6 where created > sysdate - 3 7 order by 1; OBJECT_STATUS ---------------------------------------------------------------------------------------------------- FUNCT_GET_TABLE_DDL FUNCTION VALID LEARN TABLE VALID LEARN_PK INDEX VALID LEARN_SEQ SEQUENCE VALID LEARN_TR_INSERT TRIGGER VALID
The function needs to be rewritten with formal input parameters for the schema object type, the schema name, and schema object name:
biljana@XE> drop function funct_get_table_ddl; biljana@XE> CREATE OR REPLACE FUNCTION funct_get_ddl 2 ( p_in_type VARCHAR2 3 , p_in_schema VARCHAR2 4 , p_in_name VARCHAR2 ) 5 RETURN CLOB 6 AUTHID CURRENT_USER 7 IS 8 9 -- Define local variables. 10 11 l_h NUMBER; --handle returned by OPEN 12 l_th NUMBER; -- handle returned by ADD_TRANSFORM 13 l_doc CLOB; --returned sql ddl statement 14 15 BEGIN 16 17 -- Specify the object type. 18 19 l_h := DBMS_METADATA.OPEN(p_in_type); 20 21 -- Use filters to specify the particular object desired. 22 23 DBMS_METADATA.SET_FILTER(l_h,'SCHEMA',p_in_schema); 24 DBMS_METADATA.SET_FILTER(l_h,'NAME',p_in_name); 25 26 -- Request that the metadata be transformed into creation DDL. 27 28 l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL'); 29 30 -- Fetch the object. 31 32 l_doc := DBMS_METADATA.FETCH_CLOB(l_h); 33 34 -- Release resources. 35 36 DBMS_METADATA.CLOSE(l_h); 37 38 -- Return the result 39 40 RETURN l_doc; 41 42 EXCEPTION 43 WHEN OTHERS THEN 44 DBMS_OUTPUT.PUT_LINE(SQLERRM); 45 END; 46 / Function created. Elapsed: 00:00:00.18
Now, we can get DDL statements for all schema objects that we created:
biljana@XE> SET PAGESIZE 0
biljana@XE> SET LONG 1000000
biljana@XE> set timing on
biljana@XE> SELECT funct_get_ddl('TABLE','BILJANA','LEARN') from dual;
CREATE TABLE "BILJANA"."LEARN"
( "ID" NUMBER,
"ACTION" VARCHAR2(20),
"RESULT" NUMBER(3,0),
CONSTRAINT "LEARN_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
Elapsed: 00:00:07.21
biljana@XE> SELECT funct_get_table_ddl('TRIGGER','BILJANA','LEARN_TR_INSERT') from dual;
CREATE OR REPLACE TRIGGER "BILJANA"."LEARN_TR_INSERT"
before
insert on LEARN
for each row
declare
var_id number;
begin
select learn_seq.nextval into var_id from dual;
:new.ID := var_id;
end;
ALTER TRIGGER "BILJANA"."LEARN_TR_INSERT" ENABLE
Elapsed: 00:00:00.54
biljana@XE> SELECT funct_get_ddl( 'SEQUENCE' ,'BILJANA','LEARN_SEQ') from dual;
CREATE SEQUENCE "BILJANA"."LEARN_SEQ" MINVALUE 1 MAXVALUE 99999999999999999
99999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE
Elapsed: 00:00:00.34
biljana@XE> SELECT funct_get_ddl( 'FUNCTION' ,'BILJANA','FUNCT_GET_DDL') FROM DUAL;
CREATE OR REPLACE FUNCTION "BILJANA"."FUNCT_GET_DDL"
( p_in_type VARCHAR2
, p_in_schema VARCHAR2
, p_in_name VARCHAR2 )
RETURN CLOB
AUTHID CURRENT_USER
IS
-- Define local variables.
l_h NUMBER; --handle returned by OPEN
l_th NUMBER; -- handle returned by ADD_TRANSFORM
l_doc CLOB; --returned sql ddl statement
BEGIN
-- Specify the object type.
l_h := DBMS_METADATA.OPEN(p_in_type);
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(l_h,'SCHEMA',p_in_schema);
DBMS_METADATA.SET_FILTER(l_h,'NAME',p_in_name);
-- Request that the metadata be transformed into creation DDL.
l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL');
-- Fetch the object.
l_doc := DBMS_METADATA.FETCH_CLOB(l_h);
-- Release resources.
DBMS_METADATA.CLOSE(l_h);
-- Return the result
RETURN l_doc;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Elapsed: 00:00:00.50
biljana@XE> select
2 rpad(substr(object_name,1,20),25)
3 || rpad(substr(object_type,1,10), 15)
4 || status OBJECT_STATUS
5 from user_objects
6 where created > sysdate - 3
7 order by 1;
FUNCT_GET_DDL FUNCTION VALID
LEARN TABLE VALID
LEARN_PK INDEX VALID
LEARN_SEQ SEQUENCE VALID
LEARN_TR_INSERT TRIGGER VALID
Elapsed: 00:00:00.04
Now, all schema objects are VALID and we can continue with other programming.
Summary
In this post I showed what changes the release 12.1.0.2.0 brought to us regarding the SQL CREATE TABLE statement.
Then, we learned what happened if the body of the PL/SQL function contains the literal strings of the schema object names.
The function will produce the error any time we call it if the exact names of our schema objects were changed in the meantime.
The checking with the queries on the USER_OBJECTS table and its column STATUS will mislead us.
We should always avoid using the literal strings of the schema object names in the body of any PL/SQL subprogram.
I hope this will be helpful for anyone who would like to learn more about Oracle Database.
Thank you for visiting my blog!
Have a nice day!
P.S. Keep climbing Mount Olympus ( I mean keep learning and make things happen ) !
Climbing Olympus 