Weird procedure access problem (ORA-00942)

[neilsolent]

Hi

User SSF can see and execute procedure SP_GET_SERVER, and has granted
execute access to user SSF_ADMIN1. However (sqlplus session as
SSF_ADMIN1):


SQL> select * from all_objects where object_name = 'SP_GET_SERVER' and
object_type = 'PROCEDURE';

OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- --------------
-------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SSF SP_GET_SERVER
12266 PROCEDURE
03-MAY-13 04-MAY-13 2013-05-04:07:57:06 VALID N N N


SQL> var c refcursor;
SQL> exec SSF.SP_GET_SERVER(:c);
BEGIN SSF.SP_GET_SERVER(:c); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SSF.SP_GET_SERVER", line 8
ORA-06512: at line 1


What could be wrong here?

thanks - Neil

[Michel Cadot]

"neilsolent" <n...@solenttechnology.co.uk> a �crit dans le message de news:
a5d85760-f1ae-48d8...@r4g2000vbf.googlegroups.com...

The problem is at line 8 of the procedure.

Regards
Michel

[Adrian]

In message
<a5d85760-f1ae-48d8...@r4g2000vbf.googlegroups.com>,
neilsolent <n...@solenttechnology.co.uk> writes

I would guess that line 8 of your procedure is trying to access a table
or view. Whilst SSF probably has the correct grants for that view,
SSF_ADMIN1 probably doesn't. Give the grants explicitly to SSF_ADMIN1
and see what happens.


Adrian
--
To Reply :
replace "bulleid" with "adrian" - all mail to bulleid is rejected
Sorry for the rigmarole, If I want spam, I'll go to the shops
Every time someone says "I don't believe in trolls", another one dies.

[Mladen Gogala]

I would do something like this:

ALTER SESSION SET EVENTS='942 TRACE NAME ERRORSTACK FOREVER, LEVEL 16'


and then re-execute procedure. A trace file will be written when the
error is encountered. System process ID can be extracted using the
following query:
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
user_dump_dest string /oracle/diag/rdbms/o11/
O11/tra
ce
SQL> select p.spid
2 from v$process p,v$session s
3 where p.addr=s.paddr and
4 s.sid=(select sys_context('USERENV','SID') from dual);

SPID
------------------------
2250

With that information, you can quickly locate the desired trace file:

[root@medo mgogala]# ls -l /oracle/diag/rdbms/o11/O11/trace/*.trc|grep
2250
-rw-r----- 1 oracle oinstall 7087375 May 4 17:28 /oracle/diag/rdbms/o11/
O11/trace/O11_ora_2250.trc
[root@medo mgogala]#

The problem can be located by inspecting the trace:

Machine: x86_64
Instance name: O11
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 2250, image: ora...@medo.home.com


*** 2013-05-04 17:28:05.983
*** SESSION ID:(67.15) 2013-05-04 17:28:05.983
*** CLIENT ID:() 2013-05-04 17:28:05.983
*** SERVICE NAME:(O11.home.com) 2013-05-04 17:28:05.983
*** MODULE NAME:(SQL*Plus) 2013-05-04 17:28:05.983
*** ACTION NAME:() 2013-05-04 17:28:05.983

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0,
level=16, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=ffuh3a5qharqh) -----
select * from ttt

And here it is: the SQL that has caused the ruckus. You can get trace for
almost any oracle errors. You will automatically get trace file for 0060,
0600 and 7445.


--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[neilsolent]

>
> The problem is at line 8 of the procedure.
>
> Regards
> Michel

Ah yes obvious - sorry (me guilty of making assumptions and failing
to read the error properly!)
Thanks

[neilsolent]

.. and thanks for all the other replies - appreciated.

Basically, an old session had hung which was causing my "drop user ssf
cascade;" command to fail when running a script to delete and recreate
the database. Hence the stored procedure was not being modified as I
expected.
Guilty of getting tired and failing to read the errors on the screen!

The procedure itself referenced tables in SSF schema but had AUTHID
CURRENT_USER set - hence the potential for this kind of error ..

Oh well - we move on ...