Deployment process for OAF controller classes in R12.2.x

 - When it comes to deployment of OAF classes, R12.2.x moves away from conventional Oracle Applications Server setup to Weblogic server and jar concept comes into the picture.

- Here are the steps to follow to deploy controller classes in 12.2 –

1. Move the controller class file to the desired product top under JAVA_TOP

(e.g. $JAVA_TOP/oracle/apps/xxcustom/oracle/apps/icx/por/req/webui)

2. Attach extended controller to OAF page via personalization

3. Generate the product jar file. This can be done by running ‘adcgnjar’ utility in UNIX.

This needs WebLogic credentials so DBA team needs to perform below task -

Run adcgnjar utility and regenerate product jar file (customall.jar) for <product> (e.g. XXAP)

adcgnjar generates and signs a file named customall.jar file containing the custom Java and BC4J code and extensions. The customall.jar file resides on $JAVA_TOP as indicated by CLASSPATH.

It internally performs the following steps:

- Creates a temporary custom.zip file that contains all the directories under $JAVA_TOP except the oracle, META-INF, and policies directories.

- Generates and signs the customall.jar file with the contents of the custom.zip file.

- Deletes the temporary custom.zip file.

4. Bounce oacore and apache server, as needed.

Please note, since introduction to WebLogic in 12.2, adcgnjar utility and oacore bounce require WebLogic password, hence usually DBA team performs these two tasks.

Read More

Creating and maintaining Custom Tables in R12.2.x

 1. To create a custom table in custom schema – XXCUSTOM

CREATE TABLE XXCUSTOM.TABLE_NAME (COL1 NUMBER,….);

2. To generate editioning view and synonym for the table execute below script

exec AD_ZD_TABLE.UPGRADE('XXCUSTOM','TABLE_NAME');

This will create two new objects:

(i) An editioned view (having # in the end) in XXCUSTOM schema (e.g. XXCUSTOM.TABLE_NAME#)

(ii) A synonym (same as table_name) in APPS schema (APPS.TABLE_NAME)

3. If you alter the table definition in future then after running the alter table command, run below script to regenerate the editioning view and sync the table changes -

exec AD_ZD_TABLE.PATCH('XXCUSTOM','TABLE_NAME');

4. To see the objects across all editions, please query all_objects_ae or user_objects_ae

SELECT * FROM all_objects_ae WHERE OBJECT_NAME like 'TABLE_NAME%';

5. To issue Grants/Revokes use below commands (Please request DBA team to execute below commands):

Connect to XXCUSTOM schema and run below commands -

grant <grant> on XXCUSTOM.TABLE_NAME to APPS WITH GRANT OPTION;

grant <grant> on XXCUSTOM.TABLE_NAME# to APPS WITH GRANT OPTION;

Connect to APPS and run below commands to perform Grant or Revoke operations –

execute APPS.AD_ZD.GRANT_PRIVS('<grant>','TABLE_NAME','<SCHEMA_RECEIVING_GRANT');

execute

APPS.AD_ZD.REVOKE_PRIVS('<grant>', 'TABLE_NAME','SCHEMA_TOBE_REVOKED');

Read More

Creating Materialized Views in R12.2.x

- In 12.1.3 where we create a materialized views with simple CREATE statement but in 12.2.x, we need to do below steps –

- Create a logical view

- Use ad_zd_mview upgrade script to create a materialized view.

- Oracle internally creates required edition materialized view.

e.g.

- Create a logical view. Basically, create a normal view but suffixed by the #

CREATE OR REPLACE VIEW APPS.XYZ_VIEW_NAME# AS

<query>;

- Upgrade to materialized view. The first parameter is the schema name and second is the view name without #

BEGIN

AD_ZD_MVIEW.UPGRADE('APPS', 'XYZ_VIEW_NAME');

END;

- Verify all components

SELECT * FROM dba_objects WHERE object_name LIKE 'XYZ_VIEW_NAME%';

You should see below 3 components

- XYZ_VIEW_NAME# : LOGICAL VIEW

- XYZ_VIEW_NAME : TABLE

- XYZ_VIEW_NAME : MATERIALIZED VIEW

To access the materialized view just query on XYZ_VIEW_NAME (without # suffix)

SELECT * FROM XYZ_VIEW_NAME;

Read More

Replace SQL Loader with Interactive Data Loader in Oracle APEX

Are you still using the age-old SQL Loader to load your data files in Oracle tables ? While it has it's own advantages, it certainly doesn't deliver a pleasant user experience, let alone the need of having Unix box access to place/upload data files on specific folders so that SQL Loader can read them from there.

In comes the Oracle APEX Data Load wizard that lets you create Interactive data load pages delivering a wonderful user experience and thereby eliminating a need of uploading data files on a Unix box and having SQL Loader run/load them.

Let's see how to create the Data Loader in Oracle APEX.

- First, create a brand new page in your application

- Select Page Type as 'Data Loading' -

- This will guide you through the Data Load Wizard.

- Enter Data Load Definition Name

- Select the destination Table where the data will be loaded when users use this feature

- Select at least one Unique Column that serves as a primary key in the table

- You can create Transformation Rules to transform data in any of the columns, if needed -

- You can also add lookups for any of the columns, if needed -

- Enter desired Page Numbers and Page Names for the pages in Data Load process -

- Create a new Navigation Menu Entry so that User can access the newly created Data Load process -

- Indicate which pages the Data Load process should navigate to in case it's cancelled or even when it's successfully finished -

- Click Create to finish creating the new Data Load wizard.

Loading data using newly created Data Load process -

- Users can either upload a data file (CSV or TSV) by selecting option 'Upload file, comma separated (*.csv) or tab delimited'

- OR Users can also copy and paste contents of Data File in the box below by using option 'Copy and Paste'

- Users can mention whether data is separated by Comma or Tab (or any other character) by entering desired value in 'Separator' field

Next page will show you the column mappings and users can change/correct the mappings, if needed -

Next page will show users that all the data has been validated successfully and is ready to load -

- Upon clicking Load Data, the wizard will load all the data into the table and final page will be displayed with data load results -

- Here, we can see all the records are successfully loaded into the table -

Read More