Home » SQL & PL/SQL » SQL & PL/SQL » Help in explanation of this (10g)
Help in explanation of this [message #351567] Wed, 01 October 2008 10:34 Go to next message
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 Go to previous messageGo to next message
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 #351571 is a reply to message #351567] Wed, 01 October 2008 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
The quoted one compiles ok, does that mean the compiler didn't check the objects in the query?

Yes, because it is in a string and so is not analyzed.

Regards
Michel
Re: Help in explanation of this [message #351575 is a reply to message #351569] Wed, 01 October 2008 11:01 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks to all for the answers. Is there other way of knowing what instance the code is running? (or should i just ask a grant on that view?)
Re: Help in explanation of this [message #351579 is a reply to message #351575] Wed, 01 October 2008 11:09 Go to previous messageGo to next message
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

Re: Help in explanation of this [message #351594 is a reply to message #351579] Wed, 01 October 2008 12:03 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks! that worked. How do I know if it's running on RAC?
Re: Help in explanation of this [message #351606 is a reply to message #351594] Wed, 01 October 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
dbms_utility.is_cluster_database
dbms_utility.active_instances

Regards
Michel
Re: Help in explanation of this [message #351617 is a reply to message #351606] Wed, 01 October 2008 13:52 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much =)
Previous Topic: Ceating a series of records with a pattern
Next Topic: date and time
Goto Forum:
  


Current Time: Sun Feb 09 10:00:00 CST 2025