CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle11g
Arquitecto
Arquitecto

Reputation: 119

Call of dba table on stored procedure

I'm new on Oracle database 11g R2,this is for get the information of the tablespace of the database. I run the next code like a .sql :

select  TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
    from    dba_tablespaces
    order   by TABLESPACE_NAME ;

and works fine, but when i try it to run in a Stored Procedure: 

CREATE OR REPLACE PROCEDURE TABLE_SPACE_INFO AS 
BEGIN
select  TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from    dba_tablespaces
order   by TABLESPACE_NAME ;
END TABLE_SPACE_INFO;

I got a compile error:

Error(3,3): PL/SQL: SQL Statement ignored
Error(11,7): PL/SQL: ORA-00942: table or view does not exist

What is the solution and why its wrong?

Upvotes: 1

Views: 2846

Answers (3)

LJWilkinson
LJWilkinson

Reputation: 33

You have to explicitly grant a select on dba_tablespaces to yourself in order to see that table in a package. Stored procedures or packages don't inherit the role's object-specific privileges of the user. Such as:

test.sql:

create or replace procedure test as
  ret number;
BEGIN
  select count(*) into ret
    from dba_tablespaces;
end;
/

show errors

Running it:

SQL> @c:\bob\test

Warning: Procedure created with compilation errors.

Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored    
5/10     PL/SQL: ORA-00942: table or view does not exist

SQL> connect sys/syspwd@mydb as sysdba
Connected.
SQL> grant select on dba_tablespaces to myuser;

Grant succeeded.

SQL> connect myuser/mypwd@mydb
Connected.
SQL> @c:\bob\test

Procedure created.

No errors.
SQL>

Upvotes: 0

user2173738
user2173738

Reputation:

I think you dont have enough privileges to access dba_tablespaces. You should grant privileges to the user or grant DBA role before executing a query.

Upvotes: 1

Hamidreza
Hamidreza

Reputation: 3128

you can use packages for that:

  create or replace PACKAGE YourPackge    AS   TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE TABLE_SPACE_INFO(T_List OUT T_CURSOR);
  END;

  \

  create or replace   PACKAGE BODY  YourPackge as
  PROCEDURE TABLE_SPACE_INFO(T_List OUT T_CURSOR)
  IS
  BEGIN
    OPEN T_List FOR
  select  TABLESPACE_NAME,
  INITIAL_EXTENT,
  NEXT_EXTENT,
  MIN_EXTENTS,
  MAX_EXTENTS,
  PCT_INCREASE,
  STATUS,
  CONTENTS
  from    dba_tablespaces
  order   by TABLESPACE_NAME ;
  END TABLE_SPACE_INFO;
  END;

Upvotes: 0

Related Questions