Toad World® Forums

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


#1

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


#2

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


#3

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


#4

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


#5

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

Toad_procs.jpeg

Regards

Raul


#6

Hi

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

Toad_procs.jpeg

Regards

Raul


#7

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


#8

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


#9

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


#10

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


#11

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


#12

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


#13

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;


#14

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


#15

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.


#16

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.

  1. Log in as as a user that does not have the SELECT privilege on the SYS.USER$ table.

  2. Create synonym SYS."_CURRENT_EDITION_OBJ" for SYS.OBJ$;


#17

Hi

Thanks. This second approach worked for me…:slight_smile:

From sys user:

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

Synonym created.

Regards

Raul