Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201: Compilation Error! (oracle 9i)
PLS-00201: Compilation Error! [message #321768] Wed, 21 May 2008 05:10 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I am finding compilation error when I am using MQ_PROCESS table inside a procedure
 CREATE OR REPLACE PROCEDURE TEST1
    (  p_loc_id IN location.location_id%type,
       p_cnt_1 OUT number,
      )
  IS
  BEGIN
    SELECT
            COUNT (*) INTO P_CNT_1
    FROM  LOCATION A where A.LOCATION_ID=p_loc_id;
 END TEST1;
 
 
 Procedure Created successfully!
 
 
 
  CREATE OR REPLACE PROCEDURE TEST2
    (  p_loc_id IN mq_process.location_id%type,
       p_cnt_1 OUT number
      )
  IS
  BEGIN
    SELECT
            COUNT (*) INTO P_CNT_1
    FROM  MQ_PROCESS A where A.LOCATION_ID=p_loc_id;
 END TEST2;
/

Warning: Procedure created with compilation errors.
 
SQL> SHOW ERRORS
Errors for PROCEDURE TEST2:
 
LINE/COL ERROR
-------- ----------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'MQ_PROCESS' must be declare

I am getting the data from table mq_process when trying to fetch
with single select statement (Select * from mq_process) but when getting the above compilation error when
I am trying to execute inside procedure.

Whats the syntax for granting priviledge to execute table through procedure?

[Updated on: Wed, 21 May 2008 05:15]

Report message to a moderator

Re: What is the syntax to execute table through procedure? [message #321773 is a reply to message #321768] Wed, 21 May 2008 05:14 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Privileges granted via roles are not valid within PL/SQL procedures; you'll have to GRANT SELECT on "mq_process" table directly to this user.
Re: What is the syntax to execute table through procedure? [message #321779 is a reply to message #321773] Wed, 21 May 2008 05:22 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks for your instant reply.

Please make a note that I am getting the data from table mq_process when trying to fetch with single select statement.

But when I am using the same table I am getting the compilation error
Re: What is the syntax to execute table through procedure? [message #321785 is a reply to message #321779] Wed, 21 May 2008 05:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes. That is what Littlefoot said.

When you issue a SELECT statement, all you need it the SELECT privilige on that table, regardless of how you get it.

To compile a procedure that selects from the table, you must have the SELECT privilege granted to you explicitly.

Re: What is the syntax to execute table through procedure? [message #321787 is a reply to message #321785] Wed, 21 May 2008 05:43 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks for the reply.
How to do that? Whats the syntax for it?
Can you please show me with an example?
Re: What is the syntax to execute table through procedure? [message #321797 is a reply to message #321787] Wed, 21 May 2008 06:24 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
GRANT SELECT ON mq_process TO user_who_creates_this_procedure;
Previous Topic: Group by with nested sql
Next Topic: Execute immediate + Tuning this piece of code (merged)
Goto Forum:
  


Current Time: Fri Dec 02 20:49:51 CST 2016

Total time taken to generate the page: 0.39764 seconds