[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