Table doesnot exist error-

[Veen]

I have select access to table ( Route) which in a different schema (DRT).

When i do select from sqlplus

SQL> SELECT count(1) FROM DRT.Route
I got the count 10 rows.

When i wrote a anonymous block
Declare
X number;
Begin
select count(1) into X from DRT.Route
dbms_output.put_line('Counter:'|| X);
End;
==> OutPut is Counter: 10

When i do the same block in a procedure or function
Create or replace procedure
X number;
Begin
select count(1) into X from DRT.Route
dbms_output.put_line('Counter:'|| X);
End;
==> I got an error message
00942-- Table or view does not exist.

When i can select the data from sqlplus why the same select is giving
an error in procedure or function ??

[stmontgo]

how was the select access granted? through a role?

drt needs to explicitly grant you select on the route table, this is expected behavior

steve

[jmodic]

Originally posted by stmontgo
how was the select access granted? through a role?

drt needs to explicitly grant you select on the route table, this is expected behavior
But in this case he should have gotten the same "ORA-00942: Table or view does not exist" error when running the anonymous block!

I don't belive the user that is running the anonymous block and the schema under which the procedure is created are actually the same in this case.

[stmontgo]

Ok I am mildly interested in the fact that an anonymous block would behave differently than an pl/sql but they are indeed different.

any hooo...grant the approprite privs to the user (not throuhg a role) and your procedure will compile and even work!

SQL> create user stmontgo identified by stmontgo;

User created.

SQL> grant dba to stmontgo;

SQL> conn stmontgo/stmontgo@dallas
Connected.

SQL> select count(*) from scott.emp;

COUNT(*)
----------
14

SQL> set serveroutput on
SQL> declare
2 x number;
3 begin
4 select count(*) into x from scott.emp;
5 dbms_output.put_line(x);
6 end;
7 /
14



SQL> create or replace procedure my_proc as
2 x number;
3 begin
4 select count(*) into x from scott.emp;
5 dbms_output.put_line(x);
6 end;
7 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PL/SQL: SQL Statement ignored
4/35 PL/SQL: ORA-00942: table or view does not exist


SQL> conn scott/tiger@dallas
Connected.
SQL> grant select on emp to stmontgo;

Grant succeeded.



SQL> conn stmontgo/stmontgo@dallas
Connected.

SQL> set serveroutput on
SQL> exec my_proc;
14

PL/SQL procedure successfully completed.

SQL>

[jmodic]

Originally posted by stmontgo
Ok I am mildly interested in the fact that an anonymous block would behave differently than an pl/sql but they are indeed different.

Indeed!

Now the only thing I don't know about this right now is:
- whether they have change this behavior without I noticed it
or
- it has behaved like this from the very beginning and I was living with the wrong assumptions all the time.

Thanks!

[Veen]

Thanks stmontgo
As you said "select access was granted through a role" . so requested explicit select access..