-
Table doesnot exist error-
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 ??
-
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
I'm stmontgo and I approve of this message
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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>
I'm stmontgo and I approve of this message
-
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!
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks stmontgo
As you said "select access was granted through a role" . so requested explicit select access..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|