35

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?

djule5
  • 2,722
  • 2
  • 19
  • 19
Victor
  • 16,609
  • 71
  • 229
  • 409

5 Answers5

53

There's a strong chance that the privileges to select from table1 have been granted to a role, and the role has been granted to you. Privileges granted to a role are not available to PL/SQL written by a user, even if the user has been granted the role.

You see this a lot for users that have been granted the dba role on objects owned by sys. A user with dba role will be able to, say, SELECT * from V$SESSION, but will not be able to write a function that includes SELECT * FROM V$SESSION.

The fix is to grant explicit permissions on the object in question to the user directly, for example, in the case above, the SYS user has to GRANT SELECT ON V_$SESSION TO MyUser;

Steve Broberg
  • 4,255
  • 3
  • 28
  • 40
  • would you be able to update this answer with a complete PROCEDURE example using that technique? – user3554664 Aug 11 '16 at 16:49
  • Unfortunately, I'm not working at a job that uses Oracle anymore, so I don't have a machine to test the validity of any syntax I'd write. If some else wants to take a stab at it, though, be my guest. – Steve Broberg Aug 11 '16 at 17:42
22

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

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
5

Make sure the function is in the same DB schema as the table.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • If the function is inside a package, the package also needs to belong to same schema as the table? – Victor Jul 12 '11 at 19:09
  • Yes, it does. Or else, it will not be able to find the table. Unless you include the schema name in the select: `select columns from schema.table1` – Adriano Carneiro Jul 12 '11 at 19:09
2

Either u dont have permission to that schema/table OR table does exist. Mostly this issue occurred if you are using other schema tables in your stored procedures. Eg. If you are running Stored Procedure from user/schema ABC and in the same PL/SQL there are tables which is from user/schema XYZ. In this case ABC should have GRANT i.e. privileges of XYZ tables

Grant All On To ABC; 

Select * From Dba_Tab_Privs Where Owner = 'XYZ'and Table_Name = <Table_Name>;
sKhan
  • 9,694
  • 16
  • 55
  • 53
Ravindra Junghare
  • 21
  • 1
0

A very simple solution is to add the database name with your table name like if your DB name is DBMS and table is info then it will be DBMS.info for any query.

If your query is

select * from STUDENTREC where ROLL_NO=1;

it might show an error but

select * from DBMS.STUDENTREC where ROLL_NO=1;

it doesn't because now actually your table is found.

piet.t
  • 11,718
  • 21
  • 43
  • 52
Ankit Neekhra
  • 9
  • 2
  • I had the same problem, if you dont know the schema, you can try option #3 here: https://www.techonthenet.com/oracle/errors/ora00942.php, For this example it would be: `SELECT owner FROM all_objects WHERE object_type IN ('TABLE','VIEW') AND object_name = 'STUDENTREC';` – chepyle Oct 19 '18 at 20:47