 PROCEDURE WITH CURSOR ERROR! [message #114866] |
Wed, 06 April 2005 00:00  |
phyxsly
Messages: 8
Registered: April 2005
|
Junior Member |
|
|
I wanted to have a cursor in my procedure having results from different tables and different databases. I successfully created the query and compiled it using the procedure builder. However, when i was about to create it as stored procedure in iSQLplus, it displayed a warning. It says "Procedure created wuth compilation error." The first error is "PLS-00341: declaration of cursor 'SUBJ_CUR' is incomplete or malformed"
Here's a piece of my code:
--------------------------------
PROCEDURE assess(STUD_ID VARCHAR2, STUD_YEAR NUMBER, STUD_TYPE CHAR) IS
CURSOR subj_cur IS
SELECT e.student_id, s.subject_id, s.subj_code, s.subject_title, s.units, s.pay_units,
d.department_name, s.tuition_fee_type, s.lab_fee_type, f.year, f.amount
FROM regist.enrol e, regist.offering o, regist.subject s,
fee_details f, regist.student st, regist.department d
WHERE (e.student_id = STUD_ID) AND (e.offering_id = o.offering_id) AND
(o.subject_id = s.subject_id) AND (f.fee_no = s.tuition_fee_type) AND
(st.student_id=e.student_id) AND ((f.year = STUD_YEAR) OR (f.year=0)) AND
(s.department_id = d.department_id);
-----------------------------
regist and system are databases.
Can anyone tell me where did I go wrong?
I resorted though in creating views, however I still received an error. It says, "Insufficient privileges"
Help me please..... I really need it badly..
PS. I also uploaded the file i'm working at....
|
|
|
|
|
|
| Re: PROCEDURE WITH CURSOR ERROR! [message #114892 is a reply to message #114870] |
Wed, 06 April 2005 02:40  |
phyxsly
Messages: 8
Registered: April 2005
|
Junior Member |
|
|
Thanks for the reply. regist and system are schemas, sorry for the error. It did compile successfully using procedure builder but not in SQL*plus.
The select statement in the cursor executed successfully. I just don;t know why I cannot create the procedure successfully. The errors in SQL*plus are:
------------------
LINE/COL ERROR
2/10 PLS-00341: declaration of cursor 'SUBJ_CUR' is incomplete or malf ormed
3/5 PL/SQL: SQL Statement ignored
6/51 PL/SQL: ORA-00942: table or view does not exist
11/20 PL/SQL: Item ignored
81/5 PL/SQL: SQL Statement ignored
81/25 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
84/5 PL/SQL: Statement ignored
84/26 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
88/5 PL/SQL: Statement ignored
88/8 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
93/5 PL/SQL: Statement ignored
93/8 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
98/5 PL/SQL: Statement ignored
98/8 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
LINE/COL ERROR
103/5 PL/SQL: Statement ignored
103/8 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
166/5 PL/SQL: Statement ignored
166/15 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
170/5 PL/SQL: Statement ignored
170/15 PLS-00320: the declaration of the type of this expression is inco mplete or malformed
----------------------------
The CREATE VIEW was my second option. But to no avail, it did not work as well. Here's the code...
--------------
CREATE VIEW assess_view AS select e.student_id, s.subj_code, s.subject_title, s.tuition_fee_type, s.lab_fee_type, f.amount from regist.enrol e, regist.offering o, regist.subject s, system.fee_details f where o.offering_id = e.offering_id AND s.subject_id = o.subject_id AND f.fee_no = s.tuition_fee_type;
-------------
The errors generated are:
-----------
ERROR at line 1:
ORA-01031: insufficient privileges
------------
I really don't know why PL/SQL can't accept my code. It's really giving me a pain in the head. I've been working with it for the past two days.
Hope you can help me with this.
Thank you very much.
|
|
|
|
| Re: PROCEDURE WITH CURSOR ERROR! [message #114898 is a reply to message #114892] |
Wed, 06 April 2005 03:30 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
The key line of the error stack is
6/51 PL/SQL: ORA-00942: table or view does not exist
Can you tell which table is indicated (line 12, coloumn 51)? When it says the table does not exist, it can also mean it can't see it due to missing grants. If the query works on its own but not as part of a vew or procedure, it means you have your SELECT permissions only through a role, not granted directly. PL/SQL doesn't use roles (there are some exceptions to that, but they won't help you here).
btw you only need to bracket OR conditions. And it's a personal preference (it means the same thing) but I would change
(f.year = STUD_YEAR) OR (f.year=0)
to the shorter version
f.year IN (STUD_YEAR, 0)
|
|
|
|
| Re: PROCEDURE WITH CURSOR ERROR! [message #114928 is a reply to message #114898] |
Wed, 06 April 2005 07:59 |
phyxsly
Messages: 8
Registered: April 2005
|
Junior Member |
|
|
The error generated is in line 6 column 51 which is the "regist.department d". The table is valid and it exist. I wonder why PL/SQL can't read into it.
I used the shorter version you recommended. It works well. Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
