PHP code:
create or replace type my_type as object
(
sql_id varchar2(13),
address raw(4),
child_address raw(4),
child_number number,
mismatch_reason_name varchar2(30),
mismatch_reason varchar2(1)
);
--==============================
create or replace type my_type_table is table of my_type;
--==============================
create or replace function fun_table_column_to_row
(p_cursor sys_refcursor)
return my_type_table pipelined
is
type my_rec_type is record(
SQL_ID VARCHAR2(13) ,
ADDRESS RAW(4) ,
CHILD_ADDRESS RAW(4) ,
CHILD_NUMBER NUMBER ,
UNBOUND_CURSOR VARCHAR2(1) ,
SQL_TYPE_MISMATCH VARCHAR2(1) ,
OPTIMIZER_MISMATCH VARCHAR2(1) ,
OUTLINE_MISMATCH VARCHAR2(1) ,
STATS_ROW_MISMATCH VARCHAR2(1) ,
LITERAL_MISMATCH VARCHAR2(1) ,
SEC_DEPTH_MISMATCH VARCHAR2(1) ,
EXPLAIN_PLAN_CURSOR VARCHAR2(1) ,
BUFFERED_DML_MISMATCH VARCHAR2(1) ,
PDML_ENV_MISMATCH VARCHAR2(1) ,
INST_DRTLD_MISMATCH VARCHAR2(1) ,
SLAVE_QC_MISMATCH VARCHAR2(1) ,
TYPECHECK_MISMATCH VARCHAR2(1) ,
AUTH_CHECK_MISMATCH VARCHAR2(1) ,
BIND_MISMATCH VARCHAR2(1) ,
DESCRIBE_MISMATCH VARCHAR2(1) ,
LANGUAGE_MISMATCH VARCHAR2(1) ,
TRANSLATION_MISMATCH VARCHAR2(1) ,
ROW_LEVEL_SEC_MISMATCH VARCHAR2(1) ,
INSUFF_PRIVS VARCHAR2(1) ,
INSUFF_PRIVS_REM VARCHAR2(1) ,
REMOTE_TRANS_MISMATCH VARCHAR2(1) ,
LOGMINER_SESSION_MISMATCH VARCHAR2(1) ,
INCOMP_LTRL_MISMATCH VARCHAR2(1) ,
OVERLAP_TIME_MISMATCH VARCHAR2(1) ,
SQL_REDIRECT_MISMATCH VARCHAR2(1) ,
MV_QUERY_GEN_MISMATCH VARCHAR2(1) ,
USER_BIND_PEEK_MISMATCH VARCHAR2(1) ,
TYPCHK_DEP_MISMATCH VARCHAR2(1) ,
NO_TRIGGER_MISMATCH VARCHAR2(1) ,
FLASHBACK_CURSOR VARCHAR2(1) ,
ANYDATA_TRANSFORMATION VARCHAR2(1) ,
INCOMPLETE_CURSOR VARCHAR2(1) ,
TOP_LEVEL_RPI_CURSOR VARCHAR2(1) ,
DIFFERENT_LONG_LENGTH VARCHAR2(1) ,
LOGICAL_STANDBY_APPLY VARCHAR2(1) ,
DIFF_CALL_DURN VARCHAR2(1) ,
BIND_UACS_DIFF VARCHAR2(1) ,
PLSQL_CMP_SWITCHS_DIFF VARCHAR2(1) ,
CURSOR_PARTS_MISMATCH VARCHAR2(1) ,
STB_OBJECT_MISMATCH VARCHAR2(1) ,
ROW_SHIP_MISMATCH VARCHAR2(1) ,
PQ_SLAVE_MISMATCH VARCHAR2(1) ,
TOP_LEVEL_DDL_MISMATCH VARCHAR2(1) ,
MULTI_PX_MISMATCH VARCHAR2(1) ,
BIND_PEEKED_PQ_MISMATCH VARCHAR2(1) ,
MV_REWRITE_MISMATCH VARCHAR2(1) ,
ROLL_INVALID_MISMATCH VARCHAR2(1) ,
OPTIMIZER_MODE_MISMATCH VARCHAR2(1) ,
PX_MISMATCH VARCHAR2(1) ,
MV_STALEOBJ_MISMATCH VARCHAR2(1) ,
FLASHBACK_TABLE_MISMATCH VARCHAR2(1) ,
LITREP_COMP_MISMATCH VARCHAR2(1)
);
in_rec my_rec_type ;
out_rec my_type;
begin
loop
fetch p_cursor into in_rec;
exit when p_cursor%NOTFOUND;
-- first row
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'UNBOUND_CURSOR',
in_rec.UNBOUND_CURSOR);
pipe row(out_rec);
-- second row
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SQL_TYPE_MISMATCH',
in_rec.SQL_TYPE_MISMATCH);
pipe row(out_rec);
-- 3
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OPTIMIZER_MISMATCH ',
in_rec.OPTIMIZER_MISMATCH) ;
pipe row(out_rec);
-- 4
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OUTLINE_MISMATCH',
in_rec.OUTLINE_MISMATCH);
pipe row(out_rec);
-- 5
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'STATS_ROW_MISMATCH',
in_rec.STATS_ROW_MISMATCH);
pipe row(out_rec);
-- 6
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LITERAL_MISMATCH',
in_rec.LITERAL_MISMATCH);
pipe row(out_rec);
-- 7
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SEC_DEPTH_MISMATCH ',
in_rec.SEC_DEPTH_MISMATCH) ;
pipe row(out_rec);
-- 8
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'EXPLAIN_PLAN_CURSOR',
in_rec.EXPLAIN_PLAN_CURSOR);
pipe row(out_rec);
-- 9
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BUFFERED_DML_MISMATCH',
in_rec.BUFFERED_DML_MISMATCH);
pipe row(out_rec);
-- 10
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'PDML_ENV_MISMATCH ',
in_rec.PDML_ENV_MISMATCH );
pipe row(out_rec);
-- 11
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INST_DRTLD_MISMATCH ',
in_rec.INST_DRTLD_MISMATCH) ;
pipe row(out_rec);
-- 12
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SLAVE_QC_MISMATCH',
in_rec.SLAVE_QC_MISMATCH);
pipe row(out_rec);
-- 13
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TYPECHECK_MISMATCH',
in_rec.TYPECHECK_MISMATCH);
pipe row(out_rec);
-- 14
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'AUTH_CHECK_MISMATCH',
in_rec.AUTH_CHECK_MISMATCH);
pipe row(out_rec);
-- 15
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'BIND_MISMATCH ',
in_rec.BIND_MISMATCH) ;
pipe row(out_rec);
-- 16
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DESCRIBE_MISMATCH',
in_rec.DESCRIBE_MISMATCH);
pipe row(out_rec);
--==============================================================
-- 17
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LANGUAGE_MISMATCH',
in_rec.LANGUAGE_MISMATCH);
pipe row(out_rec);
-- 18
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TRANSLATION_MISMATCH',
in_rec.TRANSLATION_MISMATCH);
pipe row(out_rec);
-- 19
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ROW_LEVEL_SEC_MISMATCH ',
in_rec.ROW_LEVEL_SEC_MISMATCH) ;
pipe row(out_rec);
-- 20
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INSUFF_PRIVS',
in_rec.INSUFF_PRIVS);
pipe row(out_rec);
-- 21
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INSUFF_PRIVS_REM',
in_rec.INSUFF_PRIVS_REM);
pipe row(out_rec);
-- 22
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'REMOTE_TRANS_MISMATCH',
in_rec.REMOTE_TRANS_MISMATCH);
pipe row(out_rec);
-- 23
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LOGMINER_SESSION_MISMATCH ',
in_rec.LOGMINER_SESSION_MISMATCH) ;
pipe row(out_rec);
-- 24
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INCOMP_LTRL_MISMATCH',
in_rec.INCOMP_LTRL_MISMATCH);
pipe row(out_rec);
--===========================================================================
-- 25
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'OVERLAP_TIME_MISMATCH',
in_rec.OVERLAP_TIME_MISMATCH);
pipe row(out_rec);
-- 26
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'SQL_REDIRECT_MISMATCH ',
in_rec.SQL_REDIRECT_MISMATCH );
pipe row(out_rec);
-- 27
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'MV_QUERY_GEN_MISMATCH ',
in_rec.MV_QUERY_GEN_MISMATCH) ;
pipe row(out_rec);
-- 28
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'USER_BIND_PEEK_MISMATCH',
in_rec.USER_BIND_PEEK_MISMATCH);
pipe row(out_rec);
-- 29
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TYPECHECK_MISMATCH',
in_rec.TYPECHECK_MISMATCH);
pipe row(out_rec);
-- 30
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'NO_TRIGGER_MISMATCH',
in_rec.NO_TRIGGER_MISMATCH);
pipe row(out_rec);
-- 31
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'FLASHBACK_CURSOR ',
in_rec.FLASHBACK_CURSOR) ;
pipe row(out_rec);
-- 32
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'ANYDATA_TRANSFORMATION',
in_rec.ANYDATA_TRANSFORMATION);
pipe row(out_rec);
--=============================================================================
-- 33
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'INCOMPLETE_CURSOR',
in_rec.INCOMPLETE_CURSOR);
pipe row(out_rec);
-- 34
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'TOP_LEVEL_RPI_CURSOR ',
in_rec.TOP_LEVEL_RPI_CURSOR );
pipe row(out_rec);
-- 35
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DIFFERENT_LONG_LENGTH ',
in_rec.DIFFERENT_LONG_LENGTH) ;
pipe row(out_rec);
-- 36
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'LOGICAL_STANDBY_APPLY',
in_rec.LOGICAL_STANDBY_APPLY);
pipe row(out_rec);
-- 37
out_rec := my_type(in_rec.sql_id,
in_rec.address,
in_rec.child_address,
in_rec.child_number,
'DIFF_CALL_DURN',
in_rec.DIFF_CALL_DURN);
pipe row(out_rec);
-- 38
out_rec := my_type(in_rec.sql_id,
in_rec.address,
&nb
