troubleshooting Question

how to "execute immediate" a grant stmt inside a stored procedure.

Avatar of Tereza
TerezaFlag for United States of America asked on
Oracle Database
33 Comments2 Solutions1366 ViewsLast Modified:
create or replace
PROCEDURE patch_user(grantee_p IN VARCHAR2) IS

   stmt       VARCHAR2(300);
   grantee    VARCHAR2(30);
   

BEGIN

   grantee := UPPER(grantee_p);
   dbms_output.put_line(grantee);
   -- Grant execute for types and packages
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                      WHERE object_type = 'PACKAGE'
                         OR object_type = 'TYPE'
                         OR object_type = 'FUNCTION'
                         OR object_type = 'PROCEDURE'
                        AND object_name <> 'SETUP_IWPC_USER')
   LOOP

      stmt := 'grant execute on ' || grant_cur.object_name ||' to ' || grantee;
   
 EXECUTE IMMEDIATE stmt;
   
 dbms_output.put_line(stmt);
   END LOOP;

   dbms_output.put_line('out_execute_loop');
     
   -- Grant select, insert, delete, and update on the tables.
   -- Required for CWT, IPIB, and ACE
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                     WHERE object_type = 'TABLE' )
   LOOP
 
      stmt := 'grant select, insert, delete, update on ' || grant_cur.object_name || ' to '|| grantee;

      execute immediate stmt;
--dbms_output.put_line(stmt);
  END LOOP;
 dbms_output.put_line('here');
   -- Grant select on Sequences (required for CNOAT but not the others)
   FOR grant_cur IN (SELECT object_name
                       FROM user_objects
                      WHERE object_type = 'SEQUENCE')
   LOOP

      stmt := 'grant select on ' ||
              grant_cur.object_name || ' to ' || grantee;

      EXECUTE IMMEDIATE stmt;

   END LOOP;

   stmt := 'grant select on DEFAULT_CAVEAT to ' || grantee;

   EXECUTE IMMEDIATE stmt;

   -- Drop synonyms
   FOR synonym_cur IN (SELECT owner,
                              synonym_name
                         FROM all_synonyms
                        WHERE owner = grantee)
   LOOP

      -- Drop the synonyms
      stmt := 'drop synonym ' || synonym_cur.owner || '.' || synonym_cur.synonym_name;

      EXECUTE IMMEDIATE stmt;

   END LOOP;

   BEGIN

   stmt := 'drop synonym ' || grantee || '.DEFAULT_CAVEAT';

   EXECUTE IMMEDIATE stmt;

   EXCEPTION
      WHEN  OTHERS THEN -- sqlcode = 'ORA-01434' THEN
      NULL;

   END;

   -- Create synonyms
   FOR synonym_cur IN (SELECT object_name
                         FROM user_objects
                        WHERE object_type = 'TABLE'
                           OR object_type = 'TYPE'
                           OR object_type = 'SEQUENCE'
                           OR object_type = 'PACKAGE'
                           OR object_type = 'FUNCTION'
                           OR object_type = 'PROCEDURE'
                          AND object_name <> 'SETUP_IWPC_USER')
   LOOP

      -- Create the synonyms
      stmt := 'create synonym ' || grantee || '.' || synonym_cur.object_name ||
              ' for ' || synonym_cur.object_name;

      EXECUTE IMMEDIATE stmt;

   END LOOP;


   stmt := 'create synonym ' || grantee || '.DEFAULT_CAVEAT for DEFAULT_CAVEAT';

   EXECUTE IMMEDIATE stmt;

END patch_user;

I understand the extra  semi-colon is the problem but how will I be able to execute the statement ..insdie this stored procedure without them semi colons?
ASKER CERTIFIED SOLUTION
Muhammad Khan
Manager, IT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 33 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 33 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros