Toad for Oracle: User cannot open procedures/functions tabs - ORA-00942: table or view does not exist follows

raul.kaubi · July 19, 2018, 6:59am

Hi

Toad for Oracle 12.12.0.39
Oracle 10.2.0.5.0

I noticed that user cannot open even his own schema procedures or functions tabs.

"ORA-00942: table or view does not exists"

I have granted “execute any procedure”. But in my own schema, I do not need this grant also.

What I can see is other schema packages for instance. So this grant “execute any procedure” indeed works.

Problem looks to be only with 10g databases.

Is there something I can do without upgrading to toad 13 at the moment…? (additional grant perhaps…?)

Or is there a syntax error in these two queries…?

Regards
Raul

frank.diedrich · July 19, 2018, 10:02pm

Hi,

some objects (tables, views,…) you afre using in your procedure / function maybe not exists, not been granted to your schema or have no synonym on it to use them without schema qualification.

BR

fdi

raul.kaubi · July 19, 2018, 10:14pm

Hi

No, that is not the case.

I created new user, so meaning that I do not have any procedures/functions/tables/views in my schema at all.

Toad 12.1 ja toad 12.0 works fine with 10g database. 12.12 does not.

Regards

Raul

jbowman · July 20, 2018, 7:14am

Hi Raul,

Have you tried spooling your SQL to either the screen or a file in order to see what query might be failing?

-John

raul.kaubi · July 22, 2018, 10:15pm

Hi

This is Toad for Oracle prewritten code, I do not know how I am supposed to see that SQL…?

Schema Browser: Procedures and Functions

Regards

Raul

raul.kaubi · July 23, 2018, 5:10am

Hi

How do I know that, this is Toad for Oracle prewritten code, when I open Procedures or Functions tabs from Schema Browser.

Regards

Raul

JohnDorlon · July 23, 2018, 5:58am

From the main menu: Click Database -> Spool SQL -> Spool to Screen.

Then try showing the procedures again. The queries that Toad runs will be displayed at the bottom, and you should be able to tell which one had the error. Either grant SELECT on the appropriate view or if you can’t then let us know and we’ll try to find a solution.

-John

raul.kaubi · July 23, 2018, 7:15am

Hi

Sounds like, this table SYS."_CURRENT_EDITION_OBJ"

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO,
NVL(p.PIPELINED, ‘NO’) pipelined,
NVL(p.PARALLEL, ‘NO’) parallel,
NVL(p.DETERMINISTIC, ‘NO’) deterministic,
NVL(p.AUTHID, ‘DEFINER’) authid
from
(select u.name owner,
o.name object_name,
‘PROCEDURE’ object_type,
decode(o.status, 1, ‘V’, ‘I’) status,
o.mtime last_ddl_time,
o.obj# object_id,
o.ctime created
from SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.USER#
and o.linkname IS NULL
and o.Type# = 7
and u.name = :own) o
,(SELECT debuginfo, object_id
FROM sys.all_probe_objects
WHERE owner = :own
AND object_type = ‘PROCEDURE’) d
,(SELECT object_id, nvl(AUTHID, ‘DEFINER’) authid, pipelined, parallel, deterministic
FROM sys.user_procedures
WHERE 1=1
AND object_type = ‘PROCEDURE’
GROUP BY object_id, AUTHID, pipelined, parallel, deterministic) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
order by 2;
:own(VARCHAR[8],IN/OUT)=‘RAULTEST’

How I am supposed to grant to this table…?

SQL> grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST;
grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST;
grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST
*
ERROR at line 1:
ORA-00911: invalid character

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"
I even couldn’t grant select from sys user.

Regards

Raul

raul.kaubi · July 23, 2018, 7:19am

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"

I even couldn’t grant select from sys user.

Regards

Raul

raul.kaubi · July 23, 2018, 7:22am

Hi

Sounds like, this table SYS."_CURRENT_EDITION_OBJ"

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO,
NVL(p.PIPELINED, ‘NO’) pipelined,
NVL(p.PARALLEL, ‘NO’) parallel,
NVL(p.DETERMINISTIC, ‘NO’) deterministic,
NVL(p.AUTHID, ‘DEFINER’) authid
from
(select u.name owner,
o.name object_name,
‘PROCEDURE’ object_type,
decode(o.status, 1, ‘V’, ‘I’) status,
o.mtime last_ddl_time,
o.obj# object_id,
o.ctime created
from SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.USER#
and o.linkname IS NULL
and o.Type# = 7
and u.name = :own) o
,(SELECT debuginfo, object_id
FROM sys.all_probe_objects
WHERE owner = :own
AND object_type = ‘PROCEDURE’) d
,(SELECT object_id, nvl(AUTHID, ‘DEFINER’) authid, pipelined, parallel, deterministic
FROM sys.user_procedures
WHERE 1=1
AND object_type = ‘PROCEDURE’
GROUP BY object_id, AUTHID, pipelined, parallel, deterministic) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
order by 2;
:own(VARCHAR[8],IN/OUT)=‘RAULTEST’

How I am supposed to grant to this table…?

SQL> grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST;
grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST;
grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST
*
ERROR at line 1:
ORA-00911: invalid character

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"
I even couldn’t grant select from sys user.

Regards

Raul

raul.kaubi · July 23, 2018, 7:26am

Hi

Sounds like, this table SYS."_CURRENT_EDITION_OBJ"

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO,

NVL(p.PIPELINED, ‘NO’) pipelined,

NVL(p.PARALLEL, ‘NO’) parallel,

NVL(p.DETERMINISTIC, ‘NO’) deterministic,

NVL(p.AUTHID, ‘DEFINER’) authid

from

(select u.name owner,

o.name object_name,

‘PROCEDURE’ object_type,

decode(o.status, 1, ‘V’, ‘I’) status,

o.mtime last_ddl_time,

o.obj# object_id,

o.ctime created

from SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u

where o.owner# = u.USER#

and o.linkname IS NULL

and o.Type# = 7

and u.name = :own) o

,(SELECT debuginfo, object_id

FROM sys.all_probe_objects

WHERE owner = :own

AND object_type = ‘PROCEDURE’) d

,(SELECT object_id, nvl(AUTHID, ‘DEFINER’) authid, pipelined, parallel, deterministic

FROM sys.user_procedures

WHERE 1=1

AND object_type = ‘PROCEDURE’

GROUP BY object_id, AUTHID, pipelined, parallel, deterministic) p

where p.object_id (+) = o.object_id

and o.OBJECT_ID = d.object_id (+)

order by 2;

:own(VARCHAR[8],IN/OUT)=‘RAULTEST’

How I am supposed to grant to this table…?

SQL> grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST;

grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST;

grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST

ERROR at line 1:

ORA-00911: invalid character

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"

I even couldn’t grant select from sys user.

Regards

Raul

raul.kaubi · July 23, 2018, 7:28am

Hi

Sounds like, this table SYS."_CURRENT_EDITION_OBJ"

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO,
NVL(p.PIPELINED, ‘NO’) pipelined,
NVL(p.PARALLEL, ‘NO’) parallel,
NVL(p.DETERMINISTIC, ‘NO’) deterministic,
NVL(p.AUTHID, ‘DEFINER’) authid
from
(select u.name owner,
o.name object_name,
‘PROCEDURE’ object_type,
decode(o.status, 1, ‘V’, ‘I’) status,
o.mtime last_ddl_time,
o.obj# object_id,
o.ctime created
from SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.USER#
and o.linkname IS NULL
and o.Type# = 7
and u.name = :own) o
,(SELECT debuginfo, object_id
FROM sys.all_probe_objects
WHERE owner = :own
AND object_type = ‘PROCEDURE’) d
,(SELECT object_id, nvl(AUTHID, ‘DEFINER’) authid, pipelined, parallel, deterministic
FROM sys.user_procedures
WHERE 1=1
AND object_type = ‘PROCEDURE’
GROUP BY object_id, AUTHID, pipelined, parallel, deterministic) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
order by 2;
:own(VARCHAR[8],IN/OUT)=‘RAULTEST’

How I am supposed to grant to this table, even sys gets ORA-00942…?

SQL> grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST;
grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST;
grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST
*
ERROR at line 1:
ORA-00911: invalid character

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"

Regards

Raul

JohnDorlon · July 23, 2018, 8:54am

For what it’s worth, I created this user in 10g, then logged in with it. I could not reproduce any problem in Toad 12.12 or 13.0 when going to SB-Procedures.

create user low_priv identified by low_priv;
grant create session to low_priv;
grant execute any procedure to low_priv;

raul.kaubi · July 23, 2018, 10:09pm

Hi

Sounds like, this table SYS."_CURRENT_EDITION_OBJ"

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO,
NVL(p.PIPELINED, ‘NO’) pipelined,
NVL(p.PARALLEL, ‘NO’) parallel,
NVL(p.DETERMINISTIC, ‘NO’) deterministic,
NVL(p.AUTHID, ‘DEFINER’) authid
from
(select u.name owner,
o.name object_name,
‘PROCEDURE’ object_type,
decode(o.status, 1, ‘V’, ‘I’) status,
o.mtime last_ddl_time,
o.obj# object_id,
o.ctime created
from SYS."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.USER#
and o.linkname IS NULL
and o.Type# = 7
and u.name = :own) o
,(SELECT debuginfo, object_id
FROM sys.all_probe_objects
WHERE owner = :own
AND object_type = ‘PROCEDURE’) d
,(SELECT object_id, nvl(AUTHID, ‘DEFINER’) authid, pipelined, parallel, deterministic
FROM sys.user_procedures
WHERE 1=1
AND object_type = ‘PROCEDURE’
GROUP BY object_id, AUTHID, pipelined, parallel, deterministic) p
where p.object_id (+) = o.object_id
and o.OBJECT_ID = d.object_id (+)
order by 2;
:own(VARCHARMusic,IN/OUT)=‘RAULTEST’

How I am supposed to grant to this table…?

SQL> grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST;
grant select on SYS."_CURRENT_EDITION_OBJ" to RAULTEST
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST;
grant select on SYS._CURRENT_EDITION_OBJ to RAULTEST
*
ERROR at line 1:
ORA-00911: invalid character

I even couldn’t grant select from sys user.

That is interesting, when I open Procedures tab with for example SYSTEM account, this query is different, and there seems to be no such table like SYS."_CURRENT_EDITION_OBJ"

Regards

Raul

JohnDorlon · July 24, 2018, 1:57pm

we shouldn’t be using that at all table if you don’t have privs on it. You shouldn’t have to grant that one. I’ll get back to you after I look at the code.

Can you give me the “create user” script for the user you are logged in as? I just care about the grants…roles, system privs, and object grants.

JohnDorlon · July 24, 2018, 2:19pm

That _CURRENT_EDITION_OBJ$ table didn’t come along until Oracle 11. We should not be querying it Oracle 10.

It looks like this is a bug in 12.12. It was fixed in 13.0.

Here are a couple of workarounds. Either of these should work.

Log in as as a user that does not have the SELECT privilege on the SYS.USER$ table.
Create synonym SYS."_CURRENT_EDITION_OBJ" for SYS.OBJ$;

raul.kaubi · July 25, 2018, 1:18am

Hi

Thanks. This second approach worked for me…

From sys user:

SQL> CREATE SYNONYM “_CURRENT_EDITION_OBJ” FOR SYS.OBJ$;

Synonym created.

Regards

Raul

Topic		Replies	Views
ORA-00942 when loggin into DB or viewing schema Toad for Oracle	14	1459	July 31, 2023
Toad Usage and Oracle Autonomous Database Toad for Oracle	12	1477	March 9, 2021
Toad data Point 4.3 ORA-00942: table or view does not exist Toad Data Point & Toad Intelligence Central	6	3052	October 24, 2022
Copy ONE table in Toad Xpert 12.5 Toad for Oracle	3	1583	September 22, 2020
Object Palette & Schema Browser errors Toad for Oracle	4	1336	March 13, 2015

Toad for Oracle: User cannot open procedures/functions tabs - ORA-00942: table or view does not exist follows

Related topics