How to create Apps Read Only (APPSRO) in EBS R12.2

Mohsin Ali

Follow these steps to create APPS read only custom schema APPSRO in EBS R12.2:

1) Start an online patching cycle

. /u01/oracle/PROD/EBSapps.env run
adop phase=prepare

2) Connect as sysdba and create the database user to be used for apps read only schema

$ sqlplus "/ as sysdba"

SQL > create user APPSRO identified by appsro default tablespace APPS_TS_TX_DATA;
SQL> grant connect, resource to appsro;
SQL> grant create synonym to appsro;
SQL> exit;

3.1) Connect to the patch edition (source EBSapps.env patch)

. /u01/oracle/PROD/EBSapps.env patch
sqlplus apps/<apps_password>

3.2) Connect as APPS user and run the SQL commands

$ sqlplus apps/<apps_password>

SQL>set head off
SQL> set newpage none
SQL> set pagesize 9999

SQL> spool create_synonyms.sql
SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_privs.sql
SQL> select 'exec AD_ZD.grant_privs (''SELECT'''||',' ||''''||OBJECT_NAME||''',' || ' ''APPSRO'');' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
SQL> spool off

SQL> exit;

Confirm both the Spool files: create_synonyms.sql and grant_privs.sql are created.

3.3) Run create_synonym.sql script to create synonyms:

$ sqlplus "/as sysdba"

SQL>
@create_synonyms.sql
SQL> @grant_privs.sql
SQL> exit;

4) The custom schema can be registered with the following APIs:

If Online Patching Enablement is not yet enabled (as suggested in ADZDPCUST.sql):
SQL> exec FND_ORACLE_USER_PKG.LOAD_ROW('CUSTOM_SCHEMA', 'CUSTOM', 'INVALID', NULL, 'N', 'B');

If Online Patching Enablement has already been enabled:
sqlplus SYSTEM
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('CUSTOM_SCHEMA');

Example:
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

5) Create Synonyms for APPSRO user:

$ sqlplus appsro/appsro
SQL> @create_synonyms.sql
SQL> exit;

6) Complete the patching cycle (finalize, cutover, cleanup)

Test it in TEST environment before doing in Production environment

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Responses (1)

Write a response

Hello Sir, we are facing error while granting. please help
SQL> @grant_privs.sql
BEGIN AD_ZD.grant_privs ('SELECT','FND_DOC_SEQ_2655_S', 'APPSRO'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'AD_ZD.GRANT_PRIVS' must be…

--