Home » SQL & PL/SQL » SQL & PL/SQL » what is the reason? (oracle 8i)
what is the reason? [message #445764] Thu, 04 March 2010 00:19 Go to next message
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 #445766 is a reply to message #445764] Thu, 04 March 2010 00:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why did you expect an error? (And what error did you expect?)
I don't see anything wrong at first glance.
Re: what is the reason? [message #445778 is a reply to message #445764] Thu, 04 March 2010 01:53 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
rajesh4851
your cursor c1 fetch 1st ename into l1 and 2nd ename into l2.
then you print it by dbms_output.put_line
that's ok
Re: what is the reason? [message #445784 is a reply to message #445778] Thu, 04 March 2010 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to confirm what Frank said.

Regards
Michel
Re: what is the reason? [message #446016 is a reply to message #445784] Fri, 05 March 2010 02:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 12320
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.
Previous Topic: Migrate Oracle DB to Ms/Sql server
Next Topic: Query to Eliminate multiple spaces
Goto Forum:
  


Current Time: Thu Sep 29 03:57:29 CDT 2016

Total time taken to generate the page: 0.13686 seconds