OraFAQ Forum: SQL & PL/SQL » PROCEDURE WITH CURSOR ERROR!

Home » SQL & PL/SQL » SQL & PL/SQL » PROCEDURE WITH CURSOR ERROR!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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....

Attachment: assess_coll.txt
(Size: 19.56KB, Downloaded 1376 times)

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #114870 is a reply to message #114866]

Wed, 06 April 2005 00:19

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Are regist and system databases or schemas? If they are databases, which I doubt, then you will need database links. If they are schemas, then your syntax looks O.K. What happens when you copy and paste the select statement from the cursor into SQL*Plus and run it? If it does not run from SQL*Plus, then see what error that produces. You may have mistyped a column or some such thing. If you are getting a privileges error, it is probably because you need privileges to all of the objects within the procedure granted directly, not through roles. Roles are not used in PL/SQL.

Report message to a moderator

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.

Report message to a moderator

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)

Report message to a moderator

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.

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #114931 is a reply to message #114866]

Wed, 06 April 2005 08:03

phyxsly
Messages: 8
Registered: April 2005

Junior Member

I had enough with this problem. I really can't fix it. I tried everything but I always fail. Help everyone! Help!

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #114935 is a reply to message #114931]

Wed, 06 April 2005 08:27

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Don't panic.

If the user has the table privileges through a role, I think you might find this interesting:
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Either use invokers' rights or grant access to the table on user level.

HTH,
MHE

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #115039 is a reply to message #114935]

Wed, 06 April 2005 21:59

phyxsly
Messages: 8
Registered: April 2005

Junior Member

It is interesting to note that I am using SYSTEM to create the PROCEDURE and the VIEW as well. Does the user SYSTEM has ALL the PRIVILEGES?

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #115046 is a reply to message #115039]

Wed, 06 April 2005 23:31

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

No, system does not automatically have direct privileges to everything. Also, usually applications are created in a separate schema, not system.

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #115078 is a reply to message #115046]

Thu, 07 April 2005 05:01

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I don't recommend using SYSTEM anyway. Create an application user instead.

MHE

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #115104 is a reply to message #115078]

Thu, 07 April 2005 08:48

phyxsly
Messages: 8
Registered: April 2005

Junior Member

I will follow your suggestion. I will create an application user.

What privileges will I give the application user so i could let my procedure select data from another schema? A good case will be the code I posted at my first message. I really can';t get it to work.

In creating view, what specific privilege(s) will I grant the user so that I could create a view from another schema? For example: I am currently in X schema and I want to create a view from the different tables of Y schema. Is that possible? If yes, please enlighten me.

Thanks for all the inputs. I am learning that fast. Thanks to all of you. I really do appreciate all your inputs. Smile

Report message to a moderator

Re: PROCEDURE WITH CURSOR ERROR! [message #115136 is a reply to message #115104]

Thu, 07 April 2005 13:46

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

User y will need to:

SQL> GRANT SELECT ON table_name TO application_user;

for each table that will be used in the view.

Report message to a moderator

Previous Topic:	Performance issues with paging a stored procedure
Next Topic:	get lower / upper bound of input

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 28 03:13:51 CDT 2025