Home » SQL & PL/SQL » SQL & PL/SQL » Why do I need a Declare for this?
icon1.gif  Why do I need a Declare for this? [message #196028] Tue, 03 October 2006 14:41 Go to next message
Woodster
Messages: 2
Registered: October 2006
Location: N. Fort Myers, FL
Junior Member
I'm very new to PL/SQL and have not yet written a procedure of my own. I am trying to implement a procedure for maintaining space history which I found at http://www.orafaq.com/scripts/sql/spachist.txt. However, when I try to compile the procedure I get a PLS-00201: identifier 'SYS.DBA_FREE_SPACE' must be declared. I am running Oracle 8.1.7.4. The actual code is listed below. Can anyone direct me as to why this code will not compile? If I do have to declare an existing table, how do I do that?
CREATE OR REPLACE PROCEDURE db_Space_Hist_Proc
AS
BEGIN
-- Delete old records...

  DELETE FROM db_Space_Hist
  WHERE       TIMESTAMP > SYSDATE + 364;
  -- Insert current utilization values...
  
  INSERT INTO db_Space_Hist 
	SELECT SYSDATE, Total_Space, Sys.Database_Name,
	       Total_Space-nvl(Free_Space,0) Used_Space,
	       nvl(Free_Space,0) Free_Space,
	       ((Total_Space - nvl(Free_Space,0)) / Total_Space)*100 pct_InUse,
	       num_db_Files
	FROM ( SELECT SUM(Bytes)/1024/1024 Free_Space
	       FROM   sys.dba_Free_Space ) Free,
	     ( SELECT SUM(Bytes)/1024/1024 Total_Space,
	              COUNT(*) num_db_Files
	       FROM   sys.dba_Data_Files) FULL;
  
  COMMIT;
END;


--
The Woodster
Re: Why do I need a Declare for this? [message #196038 is a reply to message #196028] Tue, 03 October 2006 15:18 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here you are; it would be pointless for me to try to explain it when Oracle did it nicely.
Oracle

PLS-00201 identifier 'string' must be declared

Cause: An attempt was made to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.

Action:

Check your spelling and declaration of the referenced name.

Verify that the declaration for the referenced item is placed correctly in the block structure.

If the referenced item is indeed declared but you do not have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared.

If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior.

Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.

It seems that you don't have a privilege to select from DBA_FREE_SPACE - connect as SYS (if you can) and GRANT SELECT ON dba_free_space TO user_which_wants_to_create_a_procedure.
icon14.gif  Re: Why do I need a Declare for this? [message #196039 is a reply to message #196038] Tue, 03 October 2006 15:42 Go to previous message
Woodster
Messages: 2
Registered: October 2006
Location: N. Fort Myers, FL
Junior Member
Littlefoot wrote on Tue, 03 October 2006 16:18


It seems that you don't have a privilege to select from DBA_FREE_SPACE - connect as SYS (if you can) and GRANT SELECT ON dba_free_space TO user_which_wants_to_create_a_procedure.


That was it! I guess I should have RTFM for that error. I just never guessed it would have been related to security.

Many thanks for your help with this Razz

--
The Woodster
Previous Topic: & symbol in data
Next Topic: Where did it go
Goto Forum:
  


Current Time: Mon Dec 05 11:20:56 CST 2016

Total time taken to generate the page: 0.09480 seconds