DECLARE vs CREATE PROC Issue Oracle

Question

I'm writing a stored procedure in Oracle and am having great success with my PLSQL when I run it via DECLARE.

However when I try to switch it over and create a stored proc (I have the permissions) I get this error: PL/SQL: ORA-00942: table or view does not exist

I reference the table exactly the same, through synonym, in my DECLARE way of doing things and it runs without issue.

Is there another permission I need for stored procs or something silly like that?

Code snippet for reference:

select count(nbr) into v_count from cla.numericPlayer where length(nbr) != 15;

For something like this, you should probably post a complete example that generates the error .. if you provide us only partial code, we can only give partial answer .. :) (my suspicion: your grant to the object is via a role, so no, you really don't have PROPER permissions ...) post a full example, it'll show up much clearer. — Ditto, Mar 16 '16 at 13:46

score 3 · Accepted Answer · answered Mar 16 '16 at 14:14

Here when you use a stored procedure make sure you have given the direct object level GRANT and not by Roles.

If you are trying to compile the Procedure in CLA schema then it should not be any problem however if you are trying to do it on other schema then you would probably need a OBJECT level GRANT. Hope this helps.

TRY

GRANT SELECT ON CLA.numericPlayer TO <SCHEMA>;

DECLARE vs CREATE PROC Issue Oracle

1 Answers1