CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
user158017
user158017

Reputation: 2991

delete specific tables using stored procedure in Oracle

I am puzzled. I have a user which can run the below in SQL Developer and it works perfectly.

begin
 FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
end;

However - if I wrap the functionality in a stored procedure and run it, while no exceptions are thrown, the table beginning with "temp" does not delete. I actually am deleting from three different schemas - thus the repetition.

CREATE OR REPLACE PROCEDURE DELETETEMPTABLES AS 
BEGIN
  --DROP ANY TABLES THAT START WITH "TEMP_"
    FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;

        FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;

    FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
exception
    WHEN OTHERS THEN
         log_errors (p_error_message => 'Nightly Processing->DeleteTempTables-> ' ||SQLERRM);
END DELETETEMPTABLES;

Upvotes: 0

Views: 1402

Answers (2)

tbone
tbone

Reputation: 15473

Justin is correct. But you can often use AUTHID to overcome the priv issue. Try:

CREATE OR REPLACE PROCEDURE DELETETEMPTABLES
AUTHID CURRENT_USER
AS

This is called "invokers rights" in Oracle. The default is definers rights (authid definer). For more see here and here.

The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.

Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

Most likely, this is a privilege issue.

ALL_TABLES lists all the tables that you have privileges on. When you run it interactively, that includes all the tables that you have privileges on via a role. When you try to create a stored procedure, however, privileges that are granted via a role are excluded and you only see those tables that you have direct grants on. If you want your code to work, the owner of the objects (or the DBA) would need to grant privileges on the object directly to the procedure owner, not via a role. Realistically, the owner of the procedure would also need the DROP ANY TABLE privilege granted directly to it (not via a role like DBA) for the DROP TABLE statements to succeed.

Upvotes: 1

Related Questions