Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Doubt (10g,win)
Procedure Doubt [message #569225] Tue, 23 October 2012 04:08 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Please explain the below error

User Scott having DBA privilege

create or replace procedure pt is
l_count integer:=0;
v_sid varchar2(1000);
begin
SELECT name
   INTO v_sid
   FROM v$database;
dbms_output.put_line('SID='||v_sid);

end pt;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE PT:

LINE/COL ERROR
-------- -----------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
7/9      PL/SQL: ORA-00942: table or view does not exist
SQL>


SQL> select name from v$database;

NAME
---------
ORCL


I can't access v$parameter,or v$database from a procedure?
Re: Procedure Doubt [message #569226 is a reply to message #569225] Tue, 23 October 2012 04:13 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
"DBA" is a Pre_defined role in Oracle DB.
To Access such objects ,Privileges must be granted directly ,but not through roles in case of Procedure.

It is not advisable to use Pre-defined roles.
create your roles.

Edit:Typo
-ROD

[Updated on: Tue, 23 October 2012 04:24]

Report message to a moderator

Re: Procedure Doubt [message #569227 is a reply to message #569225] Tue, 23 October 2012 04:15 Go to previous messageGo to next message
xerox2012
Messages: 15
Registered: October 2012
Junior Member
Hi,
I guess to access in a procedure, grants to the user should be given
Re: Procedure Doubt [message #569228 is a reply to message #569226] Tue, 23 October 2012 04:17 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Ram, It worked
Re: Procedure Doubt [message #569329 is a reply to message #569228] Wed, 24 October 2012 15:17 Go to previous messageGo to next message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
You can also use a way to get the sid that doesn't need priviledges


create or replace procedure pt is
l_count integer:=0;
v_sid varchar2(50);
begin
SELECT sys_context('USERENV','DB_NAME')
   INTO v_sid
   FROM dual;
dbms_output.put_line('SID='||v_sid);

end pt;
/
Re: Procedure Doubt [message #569338 is a reply to message #569329] Wed, 24 October 2012 23:46 Go to previous message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you should NOT use dbms_output for such thing.
Convert your procedure to a funtion and return the result via it.
dbms_output has no real usage than debugging.
PL/SQL is NOT intend to display anything but to compute, the caller, the final application, is the one that has the work to display.
And, actually dbms_output does not display anything, it just fill a buffer.

Regards
Michel
Previous Topic: NVL Function
Next Topic: Converting utl_http.read_text to PDF file
Goto Forum:
  


Current Time: Sat Dec 20 20:25:29 CST 2014

Total time taken to generate the page: 0.08826 seconds