CODEWITHSUNDEEP
sqldatabaseoracle-database
Steve
Steve

Reputation: 19

Kept getting open cursors exceeded with this code

This is the error I keep receiving

SQL Error: ORA-01000: maximum open cursors exceeded 01000. 00000 - "maximum open cursors exceeded"

This is the code I have been working on 

DROP TABLE STUDENT; 

CREATE TABLE STUDENT 
(
   Student_ID NUMBER(5) NOT NULL ENABLE 
   ,F_NAME VARCHAR2(30) NOT NULL ENABLE 
   ,L_NAME VARCHAR2(30) NOT NULL ENABLE 
   ,HomePhone_Number NUMBER(10) 
   ,Home_Address     VARCHAR2(35) 
   ,City             VARCHAR2(35) 
   ,State            VARCHAR(2) 
   ,Zip_Code         NUMBER(5) 
   ,Spouse_FName     VARCHAR2(25) 
   ,Spouse_LName     VARCHAR2(25) 
   ,Enrolled_ID      NUMBER(10) 
   ,Academic_Advisor_ID NUMBER(5) 
   ,PRIMARY KEY (Student_ID) ENABLE 
); 

DROP TABLE FACULTY; 

CREATE TABLE FACULTY 
(
   Faculty_ID            NUMBER(5) NOT NULL ENABLE 
   ,F_Name               VARCHAR2(30) NOT NULL ENABLE 
   ,L_Name               VARCHAR2(30) NOT NULL ENABLE 
   ,OfficePhone_Numnber  NUMBER(10) 
   ,Office_Number        NUMBER(10) 
   ,PRIMARY KEY (Faculty_ID) ENABLE 
); 

DROP TABLE ADVISOR; 

CREATE TABLE ADVISOR 
(
   Student_ID  NUMBER(5), 
   Faculty_ID  NUMBER(5), 
   Advisor_TYPE VARCHAR2(20) 
); 

--INSERT VALUES INTO STUDENT 

INSERT INTO STUDENT VALUES 
(1,'Emily','Bronte',9542403843,'742 Evergreen Terrace','Springfield','IL',34567,'Jason','Bourne',1001); 

INSERT INTO STUDENT VALUES 
(2,'Alice','Walker',5481354795,'3584 NW 29th St','Mason','FL',17465,'Michael','Myers',1002); 

INSERT INTO STUDENT VALUES 
(3,'Kurt','Vonnegut',5481354796,'14 Blanchfield','Summer','FL',12444,'Jane','Vonnegut',1003); 

INSERT INTO STUDENT VALUES 
(4,'Isaac','Asimov',5481354743,'6754 N Riva Ridge Loop','Evans Mills','NY',13602,'April','Asimov',1004); 

INSERT INTO STUDENT VALUES 
(5,'Thomas','Elliot',5481354799,'6754 W Riva Ridge Loop','Evans Mills','NY',13602,'Sara','Elliot',1005); 

INSERT INTO STUDENT VALUES 
(6,'Louisa','Alcott',5481357117,'6754 S Riva Ridge Loop','Evans Mills','NY',13602,'Freddy','Alcott',1006); 

--INSERT VALUES INTO FACULTY 

INSERT INTO FACULTY VALUES(500,'Marth','Graham'); 
INSERT INTO FACULTY VALUES(501,'Mikhail','Baryshinokov'); 
INSERT INTO FACULTY VALUES(502,'Jerome', 'Robbins'); 
INSERT INTO FACULTY VALUES(503,'Arthur', 'Mitchell'); 
INSERT INTO FACULTY VALUES(504,'Kir','Jooss'); 

--INSERT VALUES INTO ADVISOR 

INSERT INTO ADVISOR VALUES( 1, 500, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 2, 501, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 3, 502, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 4, 503, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 5, 504, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 1, 502, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 2, 504, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 3, 503, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 4, 501, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 5, 500, 'Thesis Advisor'); 

COMMIT; 

SET AUTOTRACE ON; 

--QUERY : All students under a specific advisor 

SELECT FACULTY.Faculty_ID,FACULTY.F_Name,FACULTY.L_Name,STUDENT.Student_ID,F_Name,L_Name 
FROM STUDENT,FACULTY,ADVISOR 
WHERE (STUDENT.STUDENT_ID = ADVISOR.STUDENT_ID) AND 
(FACULTY.Faculty_ID = ADVISOR.Faculty_ID) 
ORDER BY FACULTY.Faculty_ID; 

CREATE UNIQUE INDEX index_name 
ON ADVISOR(Student_ID,Faculty_ID); 

SET AUTOTRACE ON; 

--QUERY : All students under a specific advisor 

SELECT FACULTY.Faculty_ID,FACULTY.F_Name,FACULTY.L_Name,STUDENT.Student_ID,F_Name,L_Name 
FROM STUDENT,FACULTY,ADVISOR 
WHERE (STUDENT.Student_ID = ADVISOR.Student_ID) AND 
(FACULTY.Faculty_ID = ADVISOR.Faculty_ID) 
ORDER BY FACULTY.Faculty_ID;

After running this on run script in Oracle SQL developer this is the errors i get Error report - SQL Error: ORA-01000: maximum open cursors exceeded 01000. 00000 - "maximum open cursors exceeded" *Cause:
*Action: Unable to gather statistics please unsure user has correct access. The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

Upvotes: 0

Views: 2230

Answers (1)

Mihai Lucian Rîtan
Mihai Lucian Rîtan

Reputation: 15

Oracle can hold maximum 300 open_cursors by default.

A solution is to increase the open_cursors count limit. This can be done by the following sql code

alter system set open_cursors = 2000 scope=both;

This was asked before at this link: java.sql.SQLException: - ORA-01000: maximum open cursors exceeded and there the problem is much broader described.

Upvotes: 1

Related Questions