
rem 
rem NAME 
rem   ckcrerle.sql
rem DESCRIPTION
rem   Creates a role to enable access via synonyms for all objects required by a synonym user to
rem   access a Designer/2000 Repository  table owner.
rem RETURNS 
rem NOTES
rem MODIFIED (MM/DD/YY) 
rem    18/5/2000 #1302903 - ERROR AT STAGE CKCRERLE IS NOT REPORTED BY THE RAU. 
rem


prompt *************************************************************
prompt *                                                           
prompt * Creating Role 
prompt * Please wait ... 
prompt *                                                           
prompt *************************************************************
prompt

DECLARE
   PROCEDURE create_role (l_role IN varchar2) is
      csr NUMBER;
      role_conflicts EXCEPTION;
      PRAGMA EXCEPTION_INIT(role_conflicts, -01921);
   BEGIN
      DBMS_OUTPUT.PUT_LINE('... Creating role '||l_role);
      csr := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(csr,'create role '||l_role, DBMS_SQL.NATIVE);
      DBMS_SQL.CLOSE_CURSOR(csr);
      DBMS_OUTPUT.PUT_LINE('... Role created');
   EXCEPTION
      WHEN role_conflicts THEN
         DBMS_OUTPUT.PUT_LINE('... Role already exists');
         DBMS_SQL.CLOSE_CURSOR(csr);
      WHEN others THEN
	     DBMS_SQL.CLOSE_CURSOR(csr); 
         RAISE;
   END;
BEGIN
   
   dbms_output.enable(8000);
   create_role('CKR_'||user);
  

EXCEPTION
   WHEN others THEN
      -- #1302903 added RAISE to trap error
      DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);  
	  RAISE;
END;  
/


