Ora-00942 Error In Oracle 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 pl/sql ora-00942 table or view does not exist in procedure About Us Learn more about Stack Overflow the company Business Learn more about
Ora-00942 Table Or View Does Not Exist In Oracle 10g
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Table Or View Does Not Exist Oracle Sql Developer
Join the Stack Overflow Community 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
Java.sql.sqlexception: Ora-00942: Table Or View Does Not Exist
does not exist (works when a separate sql, but does not work inside a oracle function) up vote 13 down vote favorite When I have a sql statement like select * from table1, it works great, but as soon as I put it into a function, I get: ORA-00942: table or view does not exist How to solve this? sql oracle oracle9i ora-00942 share|improve this ora 00942 table or view does not exist in stored procedure question edited Jun 18 '15 at 17:22 djule5 1,22011216 asked Jul 12 '11 at 19:00 Victor 5,28231121254 Maybe the function belongs to a different schema than the table? –Daniel Hilgarth Jul 12 '11 at 19:03 Strongly related: oracle “table or view does not exist” from inside stored procedure –Mooz Sep 26 at 4:56 add a comment| 4 Answers 4 active oldest votes up vote 7 down vote accepted There are a couple of things you could look at. Based on your question, it looks like the function owner is different from the table owner. 1) Grants via a role : In order to create stored procedures and functions on another user's objects, you need direct access to the objects (instead of access through a role). 2) By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. If you created a table in Schema A and the function in Schema B, you should take a look at Oracle's Invoker/Definer Rights concepts to understand what might be causing the issue. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809 share|improve this answer edited Aug 1 at 13:34 answered Jul 12 '11
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 ora-00942 table or view does not exist hibernate About Us Learn more about Stack Overflow the company Business Learn more about oracle v$session table or view does not exist hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss ora 00942 in informatica Join the Stack Overflow Community 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 http://stackoverflow.com/questions/6669575/ora-00942-table-or-view-does-not-exist-works-when-a-separate-sql-but-does-not view does not exist' only when 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 http://stackoverflow.com/questions/30850479/ora-00942-table-or-view-does-not-exist-only-when-running-within-a-stored-proc question that has been resolved. I When I log 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 additiona
is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to http://www.dbforums.com/showthread.php?1670540-Stored-Procedure-ora-00942-table-or-view-does-not-exist visit from the selection below. Results 1 to 5 of 5 Thread: Stored Procedure: ora-00942 table or view does not exist Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode https://techjourney.net/ora-00942-table-or-view-does-not-exist-oracle-error/ Switch to Hybrid Mode Switch to Threaded Mode 10-04-11,03:59 #1 GraceCheong View Profile View Forum Posts Registered User Join Date Oct 2011 Posts 3 Unanswered: Stored Procedure: ora-00942 table or view does not exist Hi all, I'm new to table or oracle SQL Developer, currently using Oracle 11g R2. I need to write Stored Procedure to retrieve data and work with Crystal Report. I just started to leaned this a week ago. I having a problem when I compile the Stored Procedure, it appears the errors: Error(8,14): PL/SQL: SQL Statement ignored Error(8,58): PL/SQL: ORA-00942: table or view does not exist My Stored Procedure coding: create or replace PROCEDURE TEST3_SP AS PO_Num VARCHAR2(9); CURSOR PO_Crs IS SELECT erpln.ttdpur400223.t$orno FROM erpln.ttdpur400223; BEGIN table or view OPEN po_crs; LOOP FETCH PO_Crs INTO PO_Num; EXIT WHEN PO_Crs%NOTFOUND; DBMS_OUTPUT.PUT_LINE(PO_Num); END LOOP; CLOSE PO_Crs; END TEST3_SP; It works fine at SQL Worksheet to the SQL Command (SELECT erpln.ttdpur400223.t$orno FROM erpln.ttdpur400223) and returned values. Does anyone encounterd this error before? How to solve it? URGENT!!! Best Regards, Grace Reply With Quote 10-04-11,06:56 #2 Littlefoot View Profile View Forum Posts Lost Boy Join Date Jan 2004 Location Croatia, Europe Posts 4,090 Provided Answers: 4 If you don't own the table, then its owner has to grant you privileges to access it. For example, he granted SELECT to you. However: if he granted it via a role, bad news: privileges acquired via roles won't work in PL/SQL. Ask the owner to grant these privileges directly to you. Reply With Quote 10-05-11,05:13 #3 GraceCheong View Profile View Forum Posts Registered User Join Date Oct 2011 Posts 3 Hi, Thanks, I got to solve it. Actually I dont know where and how to access to Grant SELECT & Privileges. Finally I do it at SQL Developer, it works. How to Grant Privileges at SQL Developer (version 1.5.5 / Oracle 11g R2): 1.) Open the Connection 2.) Click on 'Other Users' 3.) Select user name 4.) Right click, edit user 5.) Go to [Roles], select [Grant All] 6.) Go to [System Priviledges], select [Grant All] 7.) Click Apply. Hope this can help other people who is facing the same pro