develog

1. 생성 -- 1. OBJECT 타입 생성CREATE OR REPLACE TYPE T_OBJ AS OBJECT ( COL1 VARCHAR2(10) , COL2 VARCHAR2(10) ); -- 2. TABLE 타입 생성CREATE OR REPLACE TYPE T_TABLE AS TABLE OF T_OBJ; -- 3. 함수 생성 (테이블 리턴) CREATE OR REPLACE FUNCTION TFN_LIST(P_COL1 VARCHAR2) RETURN T_TABLE IS V_RTN T_TABLE; BEGIN SELECT T_OBJ(COL1, COL2) BULK COLLECT INTO V_RTN FROM ( SELECT COL1, COL2 FROM MY_TABLE WHERE COL1 = P_COL1 ); R..

SELECT * FROM ALL_DEPENDENCIES WHERE REFERENCED_OWNER = '' AND REFERENCED_NAME = ''

--시간차가 5분 미만인 건AND REG_TS - TO_TIMESTAMP('2016/11/04 15:05:19', 'YYYY/MM/DD HH24:MI:SS') < INTERVAL '05:00' MINUTE TO SECOND

SELECT TYPE, NAME --, LINE --, TEXT , REGEXP_REPLACE(TEXT, 'FUNCTION|PROCEDURE|;|\s|\(.*', '') TEXT2FROM( SELECT TYPE, NAME, LINE, UPPER(TEXT) TEXT FROM USER_SOURCE WHERE TYPE = 'PACKAGE' AND (1=2 OR TEXT LIKE '%FUNCTION%' OR TEXT LIKE '%PROCEDURE%' ))ORDER BY TYPE, NAME, LINE

SELECT *FROM USER_DEPENDENCIES

CHR(10) : LF, Line FeedCHR(13) : CR, Carriage Return TRIM(REPLACE(REPLACE(COL_NM, CHR(10), ''), CHR(13), ''))