-- table name, owner, synony, del_flg, entity_cre_flg-- del_entity_cre_flg-- entity_infy
DROP TYPE ENTITY_INFY_TYPE_TABLE/
DROP TYPE ENTITY_INFY_TYPE/
CREATE OR REPLACE TYPE ENTITY_INFY_TYPE AS OBJECT( TABLE_NAME VARCHAR2(60), OWNER VARCHAR2(20), SYNONY VARCHAR2(60), DEL_FLG CHAR(1), ENTITY_CRE_FLG CHAR(1))/
CREATE OR REPLACE TYPE ENTITY_INFY_TYPE_TABLE AS TABLE OF ENTITY_INFY_TYPE/
CREATE OR REPLACE FUNCTION entity_infy_funcRETURN ENTITY_INFY_TYPE_TABLEPIPELINED AS
CURSOR ENTITY_INFY_CUR IS SELECT DISTINCT allsyno.table_name,allsyno.table_owner, allsyno.synonym_name
FROM ALL_TAB_COLS atc, all_synonyms allsyno
WHERE atc.table_name = allsyno.table_name AND atc.owner = allsyno.table_owner AND atc.column_name IN ('DEL_FLG','ENTITY_CRE_FLG');
v_delFlg CHAR(1); v_entityCreFlg CHAR(1);
BEGIN
FOR v_record IN entity_infy_cur LOOP dbms_output.put_line('0'); BEGIN SELECT 'Y' INTO v_delFlg FROM all_tab_cols WHERE table_name = v_record.table_name AND column_name = 'DEL_FLG' AND ROWNUM<2;
EXCEPTION WHEN no_data_Found THEN v_delFlg := 'N'; END;
dbms_output.put_line('1'); BEGIN SELECT 'Y' INTO v_entityCreFlg FROM all_tab_cols WHERE table_name = v_record.table_name AND column_name = 'ENTITY_CRE_FLG' AND ROWNUM<2; EXCEPTION WHEN no_data_Found THEN v_entityCreFlg := 'N'; END; dbms_output.put_line('2'); PIPE ROW( ENTITY_INFY_TYPE( v_record.table_name, v_record.table_owner, v_record.synonym_name, v_delFlg, v_entityCreFlg ) ); END LOOP;
END entity_infy_func;/-- select * from table(entity_infy_func) where rownum<6s;
-- 前5条结果
TABLE_NAME OWNER SYNONY DEL_FLG ENTITY_CRE_FLGFPICENTITYDETAILS FDMADMIN ENTTAB Y NCTL_PASSBOOK_DETAILS DBSCUST CTLPBDT Y YLA_ROLL_OVER_MASTER_TBL TBAADM LA_ROM Y YLA_SCHM_PARM_TABLE TBAADM TBA_LA_SCHM_PARM_TBL Y N
LA_TOPUP_DETAILS_TABLE TBAADM TBA_LA_TOPUP_DETAILS_TBL N Y
前5条结果如del_flg 和 entity_cre_flg 标识正确!
--SELECT * FROM tbaadm.TBA_LA_TOPUP_DETAILS_TBL WHERE ROWNUM<2
-- TBA_LETTER_TBL
--SELECT * FROM tbaadm.TBA_LETTER_TBL WHERE ROWNUM<2
