develog

DECLARE TYPE T_LIST IS TABLE OF VARCHAR2(20); V_LIST T_LIST; TYPE T_CURSOR IS REF CURSOR; V_CURSOR T_CURSOR; V_SQL VARCHAR2(100); BEGIN V_SQL := ' SELECT TITLE FROM ATEST WHERE SEQ IN (1,2,3) '; OPEN V_CURSOR FOR V_SQL; FETCH V_CURSOR BULK COLLECT INTO V_LIST; CLOSE V_CURSOR; FOR I IN 1..V_LIST.COUNT LOOP DBMS_OUTPUT.PUT_LINE(V_LIST(I)); END LOOP; END;

▶ DDL (Data Definition Language)- CREATE- ALTER- DROP- TRUNCATE- RENAME ▶ DML (Data Manipulation Language)- SELECT- INSERT- UPDATE- DELETE- MERGE ▶ DCL (Data Control Language)- GRANT- REVOKE ▶ TCL (Transaction Control Language)- COMMIT- ROLLBACK- SAVEPOINT

SELECT OWNER , ( SELECT DISTINCT REPLACE(OBJECT_TYPE, ' BODY', '') FROM USER_OBJECTS WHERE OBJECT_NAME = A.TABLE_NAME ) OBJECT_TYPE , TABLE_NAME OBJECT_NAME , GRANTOR, GRANTEE, PRIVILEGE, GRANTABLE, HIERARCHY FROM USER_TAB_PRIVS A WHERE OWNER = 'SCOTT' ORDER BY OWNER, OBJECT_TYPE, TABLE_NAME, GRANTOR, GRANTEE, PRIVILEGE ;

권한 테이블 USER_TAB_PRIVS 부여된 권한 조회 (펑션, 프로시저, 패키지) SELECT * FROM USER_TAB_PRIVS WHERE OWNER = 'USER01' AND GRANTEE = 'USER02' AND PRIVILEGE = 'EXECUTE' ORDER BY TABLE_NAME ; 부여된 권한 조회 (시퀀스) SELECT * FROM USER_TAB_PRIVS WHERE OWNER = 'USER01' AND GRANTEE = 'USER02' AND INSTR(TABLE_NAME, 'SEQ') > 0 ORDER BY TABLE_NAME ; PRIVILEGE 리스트 SELECT INSERT UPDATE DELETE EXECUTE DEBUG ALTER INDEX DEQUEUE FLASH..

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'AATEST' ;

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions208.htm SELECT 'ACTION' KEY, SYS_CONTEXT('USERENV', 'ACTION') VALUE FROM DUAL UNION ALLSELECT 'AUDITED_CURSORID' KEY, SYS_CONTEXT('USERENV', 'AUDITED_CURSORID') VALUE FROM DUAL UNION ALLSELECT 'AUTHENTICATED_IDENTITY' KEY, SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') VALUE FROM DUAL UNION ALLSELECT 'AUTHENTICATION_DATA' KEY, SYS..