Here is a test case to reproduce the issue that is present in 12.1.0.2.170117 and 11.2.0.4.161018:
SQL> grant connect, create table, create view, create trigger to tc identified by tc; Grant succeeded. SQL> SQL> alter user tc enable editions for view,trigger; User altered. SQL> SQL> conn tc/tc Connected. SQL> SQL> create table t ( 2 x int) 3 / Table created. SQL> SQL> create or replace editioning view ev 2 as 3 select * 4 from t 5 / View created. SQL> SQL> create or replace trigger trg 2 before update of x on ev 3 declare 4 begin 5 null; 6 end; 7 / Trigger created.Let us look at the DDL of the trigger returning by DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_SXML_DDL functions.
SQL> select dbms_metadata.get_ddl( 'TRIGGER', 'TRG') from dual; DBMS_METADATA.GET_DDL('TRIGGER','TRG') -------------------------------------------------------------------------------- CREATE OR REPLACE EDITIONABLE TRIGGER "TC"."TRG" before update of x on ev declare begin null; end; ALTER TRIGGER "TC"."TRG" ENABLE SQL> select dbms_metadata.get_sxml_ddl( 'TRIGGER', 'TRG') from dual; DBMS_METADATA.GET_SXML_DDL('TRIGGER','TRG') -------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER "TC"."TRG" BEFORE UPDATE OF X ON "TC"."EV" declare begin null; end;They are pretty much the same. Now I change the status of my trigger. Please note the highlighted lines showing the differences:
SQL> alter trigger trg enable; Trigger altered. SQL> SQL> select dbms_metadata.get_ddl( 'TRIGGER', 'TRG') from dual; DBMS_METADATA.GET_DDL('TRIGGER','TRG') -------------------------------------------------------------------------------- CREATE OR REPLACE EDITIONABLE TRIGGER "TC"."TRG" before update of x on ev declare begin null; end; ALTER TRIGGER "TC"."TRG" ENABLE SQL> select dbms_metadata.get_sxml_ddl( 'TRIGGER', 'TRG') from dual; DBMS_METADATA.GET_SXML_DDL('TRIGGER','TRG') -------------------------------------------------------------------------------- CREATE OR REPLACE TRIGGER "TC"."TRG" BEFORE UPDATE OF X, X ON "TC"."EV" declare begin null; end;I have ended up with two X columns in the output of DBMS_METADATA.GET_SXML_DDL, which is, in fact, a non-working DDL statement.
I played more with this issue and I have eventually obtained four X columns and it is not a limit:
The DBMS_METADATA.GET_SXML_DDL procedure uses a SELECT statement similar to the above to obtain the DDL:
SELECT /*+all_rows*/ SYS_XMLGEN( VALUE(KU$), XMLFORMAT.createFormat2('TRIGGER_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_TRIGGER_VIEW KU$ WHERE NOT (KU$.BASE_OBJ IS NOT NULL AND BITAND(KU$.BASE_OBJ.FLAGS,128)!=0) AND KU$.SCHEMA_OBJ.NAME = 'TRG' AND KU$.SCHEMA_OBJ.OWNER_NAME = 'TC';The column list for the trigger comes from this part of the SYS.KU$_TRIGGER_VIEW view:
cast(multiset(select * from ku$_triggercol_view tv where tv.obj_num=t.obj# ) as ku$_triggercol_list_t ),The code of the KU$_TRIGGERCOL_VIEW view is:
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."KU$_TRIGGERCOL_VIEW" OF "SYS"."KU$_TRIGGERCOL_T" WITH OBJECT IDENTIFIER (obj_num,intcol_num,type_num) AS select '1','0', tc.obj#, tc.col#, tc.type#, tc.position#, tc.intcol#, c.name, (select a.name from attrcol$ a where a.obj#=tc.obj# and a.intcol#=tc.intcol#) from col$ c, triggercol$ tc, trigger$ t where tc.obj#=t.obj# and c.obj#=t.baseobject and c.intcol#=tc.intcol#And the final part of the puzzle is the TRIGGERCOL$ table:
SQL> select * from sys.triggercol$ where obj#=(select object_id from dba_objects where owner='TC' and object_name='TRG'); OBJ# COL# TYPE# POSITION# INTCOL# ---------- ---------- ---------- ---------- ---------- 214352 1 0 0 1 214352 1 0 0 1 214352 1 0 0 1 214352 1 0 0 1 214352 1 1024 0 1It gets one row each time I execute the "ALTER TRIGGER ENABLE/DISABLE" statement. I think Oracle Developers should filter the rows because there is only one row with TYPE#=1024.
DBMS_METADATA.GET_DDL returns correct DDL and it seems to be come from the DDL passed by a user.
Conversely, DBMS_METADATA.GET_SXML_DDL tries to reconstruct the user's DDL and it messes things up.
Комментариев нет:
Отправить комментарий
Примечание. Отправлять комментарии могут только участники этого блога.