[20170916]sqlplus Set array min 2 supplemental. txt

Source: Internet
Author: User
Tags sqlplus

[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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.