[20180109]sqlplus refcursor.txt

[20180109]sqlplus refcursor.txt

--//別人問的問題，如何在sqlplus使用refcursor定義的變數，我查詢一下,做一個記錄：

SCOTT@book> @ &r/ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> var x refcursor;
SCOTT@book> exec open :x for select * from dept;

PL/SQL procedure successfully completed.

SCOTT@book> print :x
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

--//以前經常使用這樣方式測試undo的ora-01555錯誤。

$ oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments