Home » SQL & PL/SQL » SQL & PL/SQL » RE:PL/SQL Table
RE:PL/SQL Table [message #197673] Thu, 12 October 2006 04:50 Go to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product


DECLARE
TYPE EMP_TY IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(30);
EMP_TAB EMP_TY;
BEGIN
FOR X IN (SELECT ENAME FROM EMP)
LOOP
EMP_TAB(EMP_TAB.COUNT+1) :=X.ENAME;
END LOOP;
FOR I IN EMP_TAB.FIRST..EMP_TAB.last /* if replaced last with
count then output differs */
LOOP
DBMS_OUTPUT.PUT_LINE(I||','||EMP_TAB(I));
END LOOP;
END;
[U]output :
1,KING
2,BLAKE
3,CLARK
4,JONES
5,SCOTT
6,FORD
7,SMITH
8,ALLEN
9,WARD
[/U]


can anyone explain why ?

regards,
Re: RE:PL/SQL Table [message #197682 is a reply to message #197673] Thu, 12 October 2006 05:08 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
yep, same behavior in 9i, LAST returns 9 records, COUNT returns 13, ill also search =)
Re: RE:PL/SQL Table [message #197683 is a reply to message #197682] Thu, 12 October 2006 05:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
because you index by varchar2. '9' > '13', so '9' is last.
Re: RE:PL/SQL Table [message #197685 is a reply to message #197683] Thu, 12 October 2006 05:18 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!

also have read this

http://www.oracle.com/technology/oramag/oracle/03-jan/o13plsql.html

and modified some of your code


SQL> DECLARE
  2  TYPE EMP_TY IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(30);
  3  EMP_TAB EMP_TY;
  4  BEGIN
  5  FOR X IN (SELECT ENAME FROM EMP)
  6  LOOP
  7  EMP_TAB(x.ename) := X.ENAME;
  8  END LOOP;
  9  
 10  dbms_output.put_line(emp_tab('Smith'));
 11  end;
 12  /

Smith

PL/SQL procedure successfully completed

SQL> 

Re: RE:PL/SQL Table [message #197687 is a reply to message #197673] Thu, 12 October 2006 05:34 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Here's an example of the correct way to iterate through a table which is indexed on varchar2 rather than binary_integer:

DECLARE
TYPE OBJ_TY IS TABLE OF USER_OBJECTS.OBJECT_TYPE%TYPE INDEX BY USER_OBJECTS.OBJECT_NAME%TYPE;
OBJ_TAB OBJ_TY;
ELE_NO  USER_OBJECTS.OBJECT_NAME%TYPE;
BEGIN
FOR X IN (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS) LOOP
   OBJ_TAB(X.OBJECT_NAME) := X.OBJECT_TYPE;
END LOOP;
--
ELE_NO := OBJ_TAB.FIRST;
WHILE OBJ_TAB.EXISTS(ELE_NO) LOOP
  DBMS_OUTPUT.PUT_LINE(ELE_NO||' HAS TYPE '||OBJ_TAB(ELE_NO));
  ELE_NO := OBJ_TAB.NEXT(ELE_NO);
END LOOP;
END;

[Updated on: Thu, 12 October 2006 05:39]

Report message to a moderator

Previous Topic: group function in cursors
Next Topic: Procedure: insert else delete (merged)
Goto Forum:
  


Current Time: Fri Dec 09 04:23:34 CST 2016

Total time taken to generate the page: 0.12597 seconds