-- 有待修改DROP TYPE FieldInfo_TYPE_TABLE/
DROP TYPE FieldInfo_TYPE/
CREATE OR REPLACE TYPE FieldInfo_TYPE AS OBJECT( table_name VARCHAR2(150), -- table owner & table name column_name VARCHAR2(100), data_type VARCHAR2(10), data_length NUMBER(9), data_precision NUMBER(2), data_scale NUMBER(2))/
CREATE OR REPLACE TYPE FieldInfo_TYPE_TABLE AS TABLE OF FieldInfo_TYPE/CREATE OR REPLACE FUNCTION getFieldInfo_Func( inp_tableSynonymName IN VARCHAR2, inp_fieldName IN VARCHAR2 )RETURN FieldInfo_TYPE_TABLE
PIPELINED AS
CURSOR getFieldInfo_Cur( lv_tableSynonymName IN VARCHAR2, lv_fieldName IN VARCHAR2) IS SELECT atc.owner||'.'||atc.table_name tableName, atc.column_name, atc.data_type, atc.data_length, atc.data_precision, atc.data_scale FROM ALL_TAB_COLS atc WHERE EXISTS ( SELECT 1 FROM all_synonyms allsyno WHERE allsyno.table_name = atc.table_name AND allsyno.table_owner = atc.owner AND synonym_name = nvl(lv_tableSynonymName,synonym_name) AND atc.COLUMN_NAME = nvl(lv_fieldName,atc.COLUMN_NAME) ); v_tableSynonymName VARCHAR2(100); v_fieldName VARCHAR2(100);
BEGIN v_tableSynonymName := TRIM(inp_tableSynonymName); v_fieldName := TRIM(inp_fieldName); FOR c IN getFieldInfo_Cur(v_tableSynonymName,v_fieldName) LOOP PIPE ROW( FieldInfo_TYPE( c.tableName, c.column_name, c.data_type, c.data_length, c.data_precision, c.data_scale ) );
END LOOP;
END getFieldInfo_Func;/
