Copy APPS user / Create Read Only eBS databse user

-- Connect to APPS Database Schema

CREATE USER APPS_RO IDENTIFIED BY APPS_RO;
GRANT CONNECT, RESOURCE TO APPS_RO;


      ------------------ Synonyms List ------------------------
      /* Generate script for synonyms for new schema
      */

    SELECT   'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.synonym_name|| '  FOR  '|| DS.table_owner||'.'||DS.table_name||';' script
    FROM dba_synonyms DS
    WHERE owner = 'APPS';


      ------------------ Tables List ------------------------

      /* Generate script for tables synonym for new schema
      */


    SELECT   'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.TABLE_NAME|| '  FOR  '|| DS.OWNER||'.'||DS.table_name||';' script
    FROM dba_tables DS
    WHERE owner = 'APPS'
    ;


      ------------------ View List ------------------------

      /* Generate script for view synonym for new schema
      */


      SELECT   'CREATE OR REPLACE SYNONYM APPS_RO.'||DS.VIEW_NAME|| '  FOR  '|| DS.OWNER||'.'||DS.VIEW_name||';' script
      FROM dba_views DS
      WHERE owner = 'APPS';


------------------------------------ Grant SELECT(table,views) / EXECUTION(pkg,procudure,function) to Synonyms----------------------------------------

-- Connect to APPS Schema to assign rights


DECLARE
  LV_SCRIPT VARCHAR(500);
  LV_SCRIPT_PRO VARCHAR(500);
  LV_COUNTER NUMBER := 0;
  --IS_PKG_PRO_FUN_OBJ exception;
   -- pragma EXCEPTION_INIT(IS_PKG_PRO_FUN_OBJ, '-980');
 
BEGIN
 
            FOR CUR IN (
                    SELECT  'GRANT SELECT ON '||DS.synonym_name||' TO APPS_RO' SCRIPT,
                            'GRANT EXECUTE ON '||DS.synonym_name||' TO APPS_RO' SCRIPT_PRO ,
                    DS.*
                    FROM dba_synonyms DS
                    WHERE owner = 'APPS_RO'
                   -- and DS.synonym_name IN ('ACK','AP_INVOICE_DISTRIBUTIONS_ALL')
                   
             ) LOOP
           

             
                       LV_SCRIPT := CUR.SCRIPT;
                       LV_SCRIPT_PRO := CUR.SCRIPT_PRO;
                     

                       BEGIN
                       
                          EXECUTE IMMEDIATE(LV_SCRIPT);

                       
                        EXCEPTION WHEN OTHERS THEN
                         
                           BEGIN
                                 
                                EXECUTE IMMEDIATE(LV_SCRIPT_PRO);

                             EXCEPTION WHEN OTHERS THEN

                                  dbms_output.put_line(LV_SCRIPT);
                                  dbms_output.put_line(LV_SCRIPT_PRO);
                                  dbms_output.put_line('**Internal Exception ******');

                             END;
                       
                         
                       END;

             END LOOP;
           
              dbms_output.put_line('PROCESS COMPLETED');

EXCEPTION WHEN OTHERS THEN
 
       dbms_output.put_line('------------------Main Exception------------------------');

       dbms_output.put_line(LV_SCRIPT_PRO);
       dbms_output.put_line(LV_SCRIPT);

        dbms_output.put_line(SQLERRM||'~'||SQLCODE);


END;



------------------------------------------
-- Connect to APPS and execute for ('FUNCTION','PACKAGE','PROCEDURE')


BEGIN
   

    FOR CUR IN ( SELECT  'CREATE OR REPLACE SYNONYM APPS_RO.'||DP.object_name|| '  FOR  APPS.'|| DP.object_name CREATE_SYNONYME
                      ,'GRANT EXECUTE ON '||DP.object_name||' TO APPS_RO' GRANT_OBJECT
                     

              FROM DBA_PROCEDURES DP
              WHERE  1 = 1
              AND DP.OWNER = 'APPS'
              AND DP.object_type IN ('FUNCTION','PACKAGE','PROCEDURE')
              --AND DP.object_name IN('AP_INVOICES_PKG','AP_CHECKS_PKG')
              GROUP BY DP.object_name
              )
     LOOP
     
             
              EXECUTE IMMEDIATE (CUR.CREATE_SYNONYME);
              EXECUTE IMMEDIATE (CUR.GRANT_OBJECT);
         
     END LOOP;

               DBMS_OUTPUT.PUT_LINE('Process Completed !')  ;

EXCEPTION WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM)  ;

 
END;