[20170916]sqlplus Set array min 2 supplemental. txt

[20170916]sqlplus Set array min 2 supplemental. txt

--//previously written posts, links http://blog.itpub.net/267265/viewspace-1453652/
--//above mentions setting Array=1 or array=2. The logical reading of the output of the Execute SQL statement is the same, so we get a conclusion.
--//set array minimum is 2.
--//I also mentioned in http://blog.itpub.net/267265/viewspace-2138596/that the relationship between fetch and arraysize.
--//is the first fetch 1,arraysize,..... The output is arraysize, leaving 1 in the output cache each time.
--//so see the output arraysize,arraysize record, the rest of the record.

--//But today I test found that if arraysize=1, the output is a little different by the example description:

1. Environment:
[Email protected]> @ ver1
Port_string VERSION BANNER con_id
------------------------------ -------------- ------------------------------------------------------------------- ------------- ----------
ibmpc/win_nt64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production 0

GRANT EXECUTE on SYS. Dbms_lock to SCOTT;

CREATE OR REPLACE FUNCTION scott.sleep (seconds in number)
RETURN Number AS
BEGIN
Sys.dbms_lock.sleep (seconds);
RETURN seconds;
END;
/

--//if written, leave it to everyone to test.
CREATE OR REPLACE FUNCTION scott.sleep1 (seconds in number)
RETURN number
Deterministic
As
BEGIN
Sys.dbms_lock.sleep (seconds);
RETURN seconds;
END;
/


2. Test:
--//arraysize=1
[Email protected]> set ArraySize 0
Sp2-0267:arraysize option 0 out of range (1 through 5000)
[Email protected]> set arraysize 1
[Email protected]> select empno, ename, Deptno, Sleep (1) N1 from EMP;
EMPNO ename DEPTNO N1
---------- ---------- ---------- ----------
7369 SMITH 20 1
7499 ALLEN 30 1
7521 WARD 30 1
7566 JONES 20 1
7654 MARTIN 30 1
7698 BLAKE 30 1
7782 CLARK 10 1
7788 SCOTT 20 1
7839 KING 10 1
7844 TURNER 30 1
7876 ADAMS 20 1
7900 JAMES 30 1
7902 FORD 20 1
7934 MILLER 10 1
Rows selected.

--//you can find out 1 lines first, then 2,2,2.
[Email protected]> set ArraySize 2
[Email protected]> select empno, ename, Deptno, Sleep (1) N1 from EMP;
...

--//you can see that the output is 2 lines (not 1 lines), then 2,2,2. Why is there such a small difference? Follow it.

3. Tracking:
Set ArraySize 1
@10046on 12
Select Empno, ename, Deptno, Sleep (1) N1 from EMP;
@ 10046off

D:\tools\rlwrap>grep FETCH D:\APP\ORACLE\DIAG\RDBMS\TEST\TEST\TRACE\TEST_ORA_5436.TRC
grep FETCH D:\APP\ORACLE\DIAG\RDBMS\TEST\TEST\TRACE\TEST_ORA_5436.TRC
FETCH #821664400: c=0,e=1002980,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=10059543349
FETCH #821664400: c=0,e=2000348,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10061547530
FETCH #821664400: c=0,e=2000175,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10063548611
FETCH #821664400: c=0,e=1999935,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10065549649
FETCH #821664400: c=0,e=2000097,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10067550708
FETCH #821664400: c=0,e=2000102,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10069551690
FETCH #821664400: c=0,e=2000276,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=3956160932,tim=10071552866
FETCH #821664400: c=0,e=999906,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=10072553662

--//From here can also be seen arraysize minimum is 2.
--//as to why the arraysize=1 of the case output, I only guess in the implementation of the revised arraysize=2, the details of which are not good parsing.
--//test results at 11g!!

[20170916]sqlplus Set array min 2 supplemental. txt