Help in explanation of this [message #351567] |
Wed, 01 October 2008 10:34  |
ehegagoka
Messages: 493 Registered: July 2005
|
Senior Member |
|
|
Hi,
I'm aware that direct grants should be made to access objects inside a procedure. Just want to ask what rule applies to this code:
SQL> create or replace procedure p is
2 begin
3 declare
4 cu sys_refcursor;
5 v_env varchar2(100);
6 begin
7 open cu for 'select upper(instance_name) from v$instance';
8 fetch cu into v_env;
9
10 dbms_output.put_line(v_env);
11
12 close cu;
13 end;
14 end;
15 /
Procedure created.
SQL> create or replace procedure p is
2 begin
3 declare
4 cu sys_refcursor;
5 v_env varchar2(100);
6 begin
7 open cu for select upper(instance_name) from v$instance;
8 fetch cu into v_env;
9
10 dbms_output.put_line(v_env);
11
12 close cu;
13 end;
14 end;
15 /
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE P:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/21 PL/SQL: SQL Statement ignored
7/54 PL/SQL: ORA-00942: table or view does not exist
SQL>
The quoted one compiles ok, does that mean the compiler didn't check the objects in the query? Although it compiles, it won't run still.
SQL> create or replace procedure p is
2 begin
3 declare
4 cu sys_refcursor;
5 v_env varchar2(100);
6 begin
7 open cu for 'select upper(instance_name) from v$instance';
8 fetch cu into v_env;
9
10 dbms_output.put_line(v_env);
11
12 close cu;
13 end;
14 end;
15 /
Procedure created.
SQL> exec p
BEGIN p; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "MATO.P", line 7
ORA-06512: at line 1
What features/issues are involved here? Thanks!
|
|
|
Re: Help in explanation of this [message #351569 is a reply to message #351567] |
Wed, 01 October 2008 10:52   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The first procedure does not directly reference the v$instance view - it only references it via dynamic SQL.
Dependencies and privileges are resolved at execution time, rather than at compile time, as that's the only time that it can realistically be done.
|
|
|
|
|
Re: Help in explanation of this [message #351579 is a reply to message #351575] |
Wed, 01 October 2008 11:09   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
You could try the following but I am not sure about the restrictions.
select global_name from global_name
Regards
Raj
[Edit:] Assumption is you are not running on RAC. Because normally database name and instance name will be spelt the same.
[Updated on: Wed, 01 October 2008 11:12] Report message to a moderator
|
|
|
|
|
|