Ora-00942 Error In Procedure
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the
Pl/sql Ora-00942 Table Or View Does Not Exist In Procedure
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions ora 00942 table or view does not exist in stored procedure Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million ora-00942 table or view does not exist in oracle 10g programmers, just like you, helping each other. Join them; it only takes a minute: Sign up oracle “table or view does not exist” from inside stored procedure up vote 6 down vote favorite the scenarion is
Oracle V$session Table Or View Does Not Exist
like this... I HAVE a namespace XXX where I created some tables and some stored procedures... THEY HAVE a namespace YYY where they created some tables... THEY GRANTED XXX access to their tables, so when I conect to SQL Developer using XXX connection, I can do: SELECT * FROM YYY.TableA But if I try to run that very same statement from inside a stored procedure (either simple stored procedure or a package), the stored
Ora 00942 Table Or View Does Not Exist In Oracle 11g
procedure does not compile. It happens to a lot of sp's. Is there any other permission that I have to ask for??? I'm running sp's like this: CREATE OR REPLACE PROCEDURE PRC_SOMESP( ) AS BEGIN END PRC_SOMESP; Procedures that does not access YYY tables do compile well. Thanks in advance. After Justin Cave response, I'm trying to add "AUTHID CURRENT_USER" sentence to the sp's but getting the same "table or view does not exist" result: CREATE OR REPLACE PROCEDURE PRC_PROC1( PARAMETERS... ) AUTHID CURRENT_USER AS MYVAR NUMBER; BEGIN STATEMENTS... END PRC_PROC1; CREATE OR REPLACE PACKAGE PKG_PROC2 AUTHID CURRENT_USER AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE PRC_PROC2( PARAMETERS... ) END PKG_PROC2 Should I check anything else??? oracle stored-procedures share|improve this question edited Nov 16 '10 at 20:38 Alex Poole 86.9k55993 asked Nov 16 '10 at 19:18 user509925 31113 First, you don't run SPs like you say you do, you create them. Second, make sure the PRC_SOMESP() has the right priviledges over YYY.TableA to select from it. –Will Marcouiller Nov 16 '10 at 19:28 add a comment| 2 Answers 2 active oldest votes up vote 11 down vote Most likely, the problem is that the grant was done via a role. Privileges granted to a user are not available in a definer's rights stored procedure (the default). In
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn table or view does not exist oracle sql developer more about Stack Overflow the company Business Learn more about hiring developers or
Oracle Authid Current_user
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community ora-06512 Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up 'ORA-00942: table or view does not exist' only when http://stackoverflow.com/questions/4198052/oracle-table-or-view-does-not-exist-from-inside-stored-procedure running within a Stored procedure up vote 0 down vote favorite This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that has been resolved. I When I log http://stackoverflow.com/questions/30850479/ora-00942-table-or-view-does-not-exist-only-when-running-within-a-stored-proc onto my schema, I execute the following PL-SQL code: DECLARE v_rpt_per_key NUMBER := 0; BEGIN SELECT MAX(rpt_per_key) INTO v_rpt_per_key FROM rxfinods_sta.hd_invc_ln_item_dtl_stat; dbms_output.PUT_LINE('v_RPT_PER_KEY=' || v_rpt_per_key); END; / The query executes successfully and the max value of RPT_PER_KEY is written to the Output Window in Toad. However, when I execute essentially the same code in a procedure. CREATE OR REPLACE PROCEDURE hd_purge_test IS v_rpt_per_key NUMBER := 0; BEGIN SELECT MAX(stat.rpt_per_key) INTO v_rpt_per_key FROM rxfinods_sta.hd_invc_ln_item_dtl_stat stat; --HD_INVC_LN_ITEM_DTL_STAT dbms_output.PUT_LINE('v_RPT_PER_KEY=' || v_rpt_per_key); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END hd_purge_test; I get an error that the table does not exist. [Warning] ORA-24344: success with compilation error 14/21 PL/SQL: ORA-00942: table or view does not exist 9/4 PL/SQL: SQL Statement ignored (1: 0): Warning: compiled but with compilation errors Since I was able to query the table when using the same credentials, this proves that my ID has access to select from the table. Shouldn't I have rights to also query the table from a stored procedure that I created underr the same logged on schema? Do some additional grants need to be executed? Note: The procedure compiles successfully if I select from any table in
the error ORA-00942: table or view does not exist, although this table (or view) definitely exists. The reminder here tries to explain as to why this is. The setup First, http://www.adp-gmbh.ch/ora/err/ora_00942.html two users are created: table_owner and table_user. As their names indicate, the first owns a table on which the second will then try to perform a select statement. As both users need to be able to connect to the database, create session is granted to them. Also, the table owner obviously needs the right to create tables, so he gets the create table right as well. create table or user table_owner identified by table_owner default tablespace ts_data temporary tablespace ts_temp quota unlimited on ts_data quota unlimited on ts_temp; create user table_user identified by table_user default tablespace ts_data temporary tablespace ts_temp; grant create session to table_owner; grant create table to table_owner; grant create role to table_owner; grant create session to table_user; grant create procedure to table_user; Now, we log on as the (future) table owner... connect table or view table_owner/table_owner; ... and create a table: create table just_a_table ( field_1 number, field_2 varchar2(20) ); insert into just_a_table values (4,'four'); insert into just_a_table values (6,'six'); insert into just_a_table values (8,'eight'); commit; Selecting as table_user Now, we log on as table_user and try to do a select connect table_user/table_user; select * from table_owner.just_a_table; We receive a ORA-00942: table or view does not exist, but that was to be expected, as the owner didn't grant the user with the right to select on the table. The owner will now do that. As the owner of the table expects to have many users that will want to access his table, and he also expects to give them more than just this select right, he creates a role and grants the role to the user. First the role: create role table_accessers; Then granting the select right to that role grant select on just_a_table to table_accessers; Finally, granting the role to table_user grant table_accessers to table_user; Selecting again connect table_user/table_user; select * from table_owner.just_a_table; This times, it works perfectly, as the user has the right to access the table (through the role) Creating a procedure For most people, it comes as a surpri