[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

ITPub博客

[20180109]sqlplus refcursor.txt

最新文章