Using Ref Cursors To Return Recordsets

Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types. The example below uses a ref cursor to return a subset of the records in the EMP table.

The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

The resulting cursor can be referenced from PL/SQL as follows.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);
            
  LOOP 
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

The cursor can be used as an ADO Recordset.

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=TSH1;UID=scott;PWD=tiger"

Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "get_emp_rs"
cmd.CommandType = 4 'adCmdStoredProc

Dim param1
Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 30

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF
  -- Do something
  rs.MoveNext
Loop

rs.Close
conn.Close
Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing

The cursor can also be referenced as a Java ResultSet.

import java.sql.*;
import oracle.jdbc.*;

public class TestResultSet  {
  public TestResultSet() {
    try {
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
      Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");
      CallableStatement stmt = conn.prepareCall("BEGIN get_emp_rs(?, ?); END;");
      stmt.setInt(1, 30); // DEPTNO
      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR
      stmt.execute();
      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
      while (rs.next()) {
        System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno")); 
      }
      rs.close();
      rs = null;
      stmt.close();
      stmt = null;
      conn.close();
      conn = null;
    }
    catch (SQLException e) {
      System.out.println(e.getLocalizedMessage());
    }
  }

  public static void main (String[] args) {
    new TestResultSet();
  }
}

CREATE OR REPLACE PACKAGE types AS 
  TYPE cursor_type IS REF CURSOR;
END Types; 
/

형태

오라클 계정 -> 5분 단위로 해서 -> 아래의 테이블 생성후 -> Crontab 으로 조회하여 입력하는 형태

자세한 것은 Shell, Crontab 정보를 찾으면서 하면됨

-- 임의로 실행하는 쿼리목록 조회하기
select      to_char(sysdate,'yyyymmdd'),
            to_char(sysdate, 'HH24'),
            to_char(sysdate, 'MI'),
            A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외
   
   
CREATE TABLE MStorage.USER_SQL_LOG
(
      VIEW_YMD   VARCHAR2(8) DEFAULT to_char(sysdate,'yyyymmdd'),
      VIEW_HH    VARCHAR2(2) DEFAULT to_char(sysdate, 'HH24'),
      VIEW_MI    VARCHAR2(2) DEFAULT to_char(sysdate, 'MI'),
      INST_ID    NUMBER,
      SID      NUMBER,
      SERIAL#    NUMBER,
      USERNAME   VARCHAR2(30),
      TYPE     VARCHAR2(10),
      STATUS    VARCHAR2(8),
      STATE     VARCHAR2(19),
      EVENT     VARCHAR2(64),
      OSUSER    VARCHAR2(30),
      PROGRAM    VARCHAR2(48),
      MACHINE    VARCHAR2(64),
      SQL_ADDRESS RAW(8),
      SQL_ID   VARCHAR2(13),
      SHARABLE_MEM NUMBER,
      PERSISTENT_MEM NUMBER,
      RUNTIME_MEM NUMBER,
      FIRST_LOAD_TIME VARCHAR2(38),
      OPTIMIZER_MODE VARCHAR2(10),
      OPTIMIZER_COST NUMBER,
      MODULE     VARCHAR2(64),
      SQL_FULLTEXT  CLOB
) TABLESPACE MStorage;

COMMENT ON TABLE MStorage.USER_SQL_LOG IS '로그 모니터링 테이블(GV$SESSION) 참조';

-- 인덱스 생성
CREATE INDEX MStorage.IX_USER_SQL_LOG_01 ON MStorage.USER_SQL_LOG
(
  VIEW_YMD,VIEW_HH, VIEW_MI
)
TABLESPACE MStorage;

-- 실제 입력 구문

INSERT INTO MStorage.USER_SQL_LOG
       (
       inst_id,
            SID,
            SERIAL#,
            USERNAME,
            TYPE,
            STATUS,
            STATE,
            EVENT,
            OSUSER,
            PROGRAM,
            MACHINE,
            SQL_ADDRESS,
            SQL_ID,
            sharable_mem,
            persistent_mem,
            runtime_mem,
            first_load_time,
            OPTIMIZER_MODE,
            OPTIMIZER_COST,
            MODULE,
            SQL_FULLTEXT               
            )
select      A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     1=1-- A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외

다음의 내용들은 개인적인 권장하는 사항들이다.

오라클 10g의 경우 엔진 설치를 위한 디렉토리 공간을 20GB 이상 주는 것을 권장한다.

오라클의 adump, bdump, udump, 리스너 로그, 아카이브 로그 등을 백업할 수 있으면

백업하는 것을 권장한다. 하지만 대부분의 경우 백업의 필요성이 절실하지 않기 때문에

삭제한다.

각종 로그의 자동관리를 위하여 첨부한 파일과 같이 CRONTAB에 등록하여 관리하면 편리하다.
각각의 로그관리에 대한 정책이 필요하다.

정책 예시)
1. alert 로그       : 월별로 로그를 관리. 영구 보관하는 것이 좋다.
                      compress 명령으로 압축하여 보관.
2. adump audit 파일 : 180일 정도 유지, 매일 180일이 지난 trc파일을 삭제
3. bdump trace 파일 : 90일 정도 유지, 매일 90일이 지난 trc파일을 삭제
4. udump trace 파일 : 90일 정도 유지, 매일 90일이 지난 trc파일을 삭제
5. 리스너 로그      : 리스너를 로깅하도록 설정했을 경우 월별로 로그를 관리.

                      180일이 지난 파일은 삭제. compress 명령으로 압축하여 보관.
6. 아카이브로그 파일 : 기본적으로 1주일에 1번 이상 FULL BACKUP을 받을 경우
   백업 툴에서 아카이브로그를 관리해 주지 않을 경우 등록하여 사용
   7일전 아카이브로그 파일 삭제.

쉘 예시)

쉘 스크립트 작성 시 오타에 주의할 것. 반드시 테스트 후 적용할 것
#######################################################
#### alert.log                                     ####
#### (매월 1일 실행할 수 있도록 cron job 등록 )    ####
#######################################################
nDate=`date +%Y%m%d`
cp $ORACLE_BASE/admin/TESTDB/bdump/alert_TESTDB.log $ORACLE_BASE/TESTDB/bdump/alert_TESTDB.log.$nDate
cat /dev/null > $ORACLE_BASE/admin/TESTDB/bdump/alert_TESTDB.log
compress -vf $ORACLE_BASE/TESTDB/bdump/alert_TESTDB.log.$nDate

#######################################################
#### listener.log                                  ####
#### (매월 1일 실행할 수 있도록 cron job 등록 )    ####
#######################################################
nDate=`date +%Y%m%d`
cp $ORACLE_HOME/network/admin/listener.log $ORACLE_HOME/network/admin/listener.log.$nDate
cat /dev/null > $ORACLE_HOME/network/admin/listener.log
compress -vf $ORACLE_HOME/network/admin/listener.log.$nDate

#######################################################
#### audit                                         ####
#######################################################
# 180일이 지난 *.aud를 찾아 삭제
find $ORACLE_BASE/admin/TESTDB/adump \( -ctime +180 -name '*.aud' \) -exec rm -f {} \;

#######################################################
#### .trc                                          ####
# 90일이 지난 *.trc를 찾아 삭제                    ####
#######################################################
find $ORACLE_BASE/admin/TESTDB/bdump \( -ctime +90 -name '*.trc' \) -exec rm -f {} \;
find $ORACLE_BASE/admin/TESTDB/udump \( -ctime +90 -name '*.trc' \) -exec rm -f {} \;

#######################################################
#### archive log                                   ####
#######################################################
# 7일이 지난 *.arc를 찾아 삭제
find /archive_log \( -ctime +7 -name '*.arc' \) -exec rm -f {} \;

TRANSLATE (string1, search_set, replace_set)

    TRANSLATE ('abcd', 'ab', '12') --> '12cd'

    TRANSLATE ('abcdefg', 'abcd', 'zyx') --> 'zyxefg'

Translates character data to either the database character set (CHAR_CS) or the national character set (NCHAR_CS). The output datatype will be either VARCHAR2 or NVARCHAR2, depending on whether you are converting to the database or the national character set, respectively.

TRANSLATE...USING is an ISO standard SQL function. Starting with Oracle9i Database Release 1, you can simply assign a VARCHAR2 to an NVARCHAR2, (and vice versa), and Oracle will handle the conversion implicitly. If you want to make such a conversion explicit, you can use TO_CHAR and TO_NCHAR to convert text to database and national character sets, respectively. Oracle recommends the use of TO_CHAR and TO_NCHAR over TRANSLATE...USING, because those functions support a greater range of input datatypes.