Home » SQL & PL/SQL » SQL & PL/SQL » varry procedure
varry procedure [message #210563] Thu, 21 December 2006 05:53 Go to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
plz help me.its a simple procedure i want to display the varray.varray gets displayed but giving error at the end .elements in varray will be added as per results of sql query
provding the code and errors:-

1 CREATE OR REPLACE PROCEDURE STUDENT1(CID COURSE.COURSEID%TYPE)
2 IS
3 TYPE S IS VARRAY(20) OF STUDENT.STUDID%TYPE;
4 STUD S;
5 BEGIN
6 STUD:=S();
7 SELECT STUDENT.STUDID BULK COLLECT INTO STUD
8 from student,course,registration
9 where student.studid=registration.studid
10 and course.courseid=registration.courseid
11 and course.courseid = cid;
12 FOR I IN 1..STUD.LIMIT
13 LOOP
14 DBMS_OUTPUT.PUT_LINE(STUD(I));
15 END LOOP;
16* END STUDENT1;
SQL> /

Procedure created.

SQL> EXEC STUDENT1(1003);
3001
3004
3006
BEGIN STUDENT1(1003); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "APPS.STUDENT1", line 14
ORA-06512: at line 1
Re: varry procedure [message #210567 is a reply to message #210563] Thu, 21 December 2006 06:11 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Answer to your problem
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200630de3a44301efcd24ead8346938b5c1df469.e34Tb34Lb34PbO0Lb3eTahiPc3eRe0?me ssageID=477523&#477523

[Updated on: Thu, 21 December 2006 06:12]

Report message to a moderator

Re: varry procedure [message #210571 is a reply to message #210563] Thu, 21 December 2006 06:24 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

check this if this works for you.i think the limit clause may be cause for the error.
CREATE OR REPLACE PROCEDURE STUDENT1(CID COURSE.COURSEID%TYPE)
IS
TYPE S IS VARRAY(20) OF STUDENT.STUDID%TYPE;
STUD S :=S();
BEGIN
SELECT STUDENT.STUDID BULK COLLECT INTO STUD
from student,course,registration
where student.studid=registration.studid
and course.courseid=registration.courseid
and course.courseid = cid;
FOR I IN 1..STUD.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(STUD(I));
END LOOP;
END STUDENT1;


regards,
Re: varry procedure [message #210577 is a reply to message #210563] Thu, 21 December 2006 06:36 Go to previous messageGo to next message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
thank you so much it does work!!!!!!!!!!!!thanks a lot!!!!!!!!
Re: varry procedure [message #210578 is a reply to message #210571] Thu, 21 December 2006 06:38 Go to previous message
CHAISH
Messages: 12
Registered: December 2006
Junior Member
thank u so much dhananjay it works!!!u solved my problem!!!thanks a lot!!1
Previous Topic: Listener.ora
Next Topic: About Forms & Reports 6i
Goto Forum:
  


Current Time: Sat Dec 03 08:04:39 CST 2016

Total time taken to generate the page: 0.12369 seconds