0

I did try to create a stored procedure in oracle 10g database, that selects data from dba_segments and inserts into another table (viz. table_space). 

CREATE OR REPLACE
PROCEDURE P_DAILY_ENTRY
IS
  an_job_id number;
BEGIN
    INSERT INTO table_space(table_name, max_size, date_of_max_size)
        SELECT table_name,
          TRUNC(SUM(bytes)/1024) max_size,
          sysdate date_of_max_size
        FROM
          (
          SELECT segment_name table_name, owner, bytes
          FROM dba_segments
          WHERE segment_type = 'TABLE'
          UNION ALL
          SELECT segment_name table_name, owner, bytes
          FROM dba_segments
          WHERE segment_type = 'INDEX'
          )
        WHERE owner IN ('CARAT')
        GROUP BY table_name, owner
        ORDER BY SUM(bytes) DESC;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
END P_DAILY_ENTRY;

Whin I run the above it shows following two errors:

  • line 05 | Execution | PL/SQL: SQL Statement ignored
  • line 12 | Execution | PL/SQL: ORA00942: table or view does not exist

However, if INSERT statement is executed separately, it populates the table_space table.

Satyendra
  • 1,635
  • 3
  • 19
  • 33
  • 2
    dba_segments is a system table. It exists, but you do not have access to it. Talk with your DBA about getting the necessary permission. – Joe Nov 06 '13 at 14:19
  • @Joe I do have `SELECT` permission on `dba_segments`, this is why the `INSERT` statement is working when executed separately. I can't understand why this error is showing up in case of the procedure only! – Satyendra Nov 06 '13 at 14:30
  • 1
    @Satyendra - are your privileges on `dba_segments` granted directly to you, or via a role? [Roles are disabled in named blocks with definer's rights](http://docs.oracle.com/cd/E11882_01/network.112/e16543/authorization.htm#DBSEG99883). – Alex Poole Nov 06 '13 at 14:57
  • @AlexPoole Thanks for the link! The answer of cagcowboy(accepted one) says that owner of the procedure must also have the rights on table. But, in my case the 'owner' is same for both cases. – Satyendra Nov 07 '13 at 06:24
  • 1
    @Satyendra - the accepted answer also stresses the rights have to be direct, not via a role, which was what my link said. Vincent's answer shows how to confirm that is your issue. – Alex Poole Nov 07 '13 at 07:45

1 Answers1

1

This is a permissions issue, most probably, since the INSERTs you can execute are not necessarily commands that the owner of the procedure can execute (this has to do with the Invoker/Definer rights architecture within Oracle).

ORA-00942 can cover a multitude of problems, a common one being that the table is regarded as non-existent if you cannot "see" it (which makes sense: many oracle error messages are designed to stop people finding out more about the database setup by being deliberately terse).

davek
  • 22,499
  • 9
  • 75
  • 95
  • @devek Initially I too considered it to be a 'permission issue', but then I executed the `INSERT` statement seperatly and it worked. This issue is showing up only in the case of stored procedure. – Satyendra Nov 06 '13 at 14:26
  • Yes, but it is possible for "you" to have INSERT rights, but that the owner of the procedure does not. And your rights are not transferred to the procedure which you create. – davek Nov 06 '13 at 14:53