what is the reason? [message #445764] |
Thu, 04 March 2010 00:19  |
rajesh4851
Messages: 89 Registered: January 2007
|
Member |
|
|
Hi,
When i executed the below block , Surprisingly i didn't get any error. Moreover, it is printed SMITH, ALLEN.
Could you please let me know what is the reason behind this?
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> declare
2 l1 VARCHAR2(100);
3 l2 VARCHAR2(100);
4 cursor c1 is select ename from emp;
5 begin
6 open c1;
7 fetch c1 into l1;
8 fetch c1 into l2;
9 close c1;
10 dbms_output.put_line (' Values of l1: '||l1||' l2: '||l2);
11 end;
12 /
Values of l1: SMITH l2: ALLEN
PL/SQL procedure successfully completed.
Output:
Values of l1: SMITH l2: ALLEN
|
|
|
|
|
|
Re: what is the reason? [message #446016 is a reply to message #445784] |
Fri, 05 March 2010 02:58   |
rajesh4851
Messages: 89 Registered: January 2007
|
Member |
|
|
I agree.
But, could you please explain how it works ? this cursor c1 is fetching more than one record, but l1 is a variable of varchar2, it is neither a cursor variable nor a table type variable. As per my assumption, it should throw error.
7 fetch c1 into l1;
8 fetch c1 into l2;
Please explain the back ground process ( how the oracle allocates the memory and how the data will be fetched to variables etc) for this kind of scenario?
|
|
|
Re: what is the reason? [message #446020 is a reply to message #446016] |
Fri, 05 March 2010 03:05   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you look at your cursor, you'll see that it only selects a single column.
Thus a single varchar2 variable is fine to select a row from the cursor into.
If the cursor returned multiple columns, then your FETCH statement would need to have multiple variables.
|
|
|
Re: what is the reason? [message #446030 is a reply to message #445764] |
Fri, 05 March 2010 03:45  |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Explicit cursors only fetch one record at a time unless you use BULK COLLECT. Each Fetch fetchs the next record in order. You can't get too_many_rows errors from explicit cursors.
|
|
|