One-stop shop to Compile JSPs in 11i, r12.1 and r12.2

Introduction –

Seasoned DBAs are already aware about architectural changes in 11i, r12 and r12.2. Likewise, there are some significant changes when you compile jsp pages in these versions. This article will cover important aspects when performing same action in different versions. We will start with 11i until r12.2 followed by a summary on differences and similarities.

JSP Cache

-          It is compiled version of JSP files.

-          They have extension .class.

-          They are stored in folder - _pages.

Compilation process of jsp file

JSP file ---conversion---> .java file ---compilation---> .class file

Differences when storing compiled .class files

In 11i, Apache JServe was responsible to compile jsp and convert the same into .class file while in r12, we have oc4j handling jsps.

Default location of compiled .class files
Release	Location
Prior 11i	$OA_HTML/_pages
11i	$COMMON_TOP/_pages
12.1	$COMMON_TOP/_pages
12.2	$EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/_pages

Differences when locating perl script ojspCompile.pl

Default location of perl script ojspCompile.pl
Release	Location
11i	$JTF_TOP/admin/scripts
12.1	$FND_TOP/patch/115/bin
12.2	$FND_TOP/patch/115/bin

Differences when locating source jsp files

Default location of jsp source files
Release	Location
11i	$COMMON_TOP/html
12.1	$COMMON_TOP/webapps/oacore/html
12.2	$EBS_APPS_DEPLOYMENT_DIR/oacore/html

Differences in nomenclature for .class files

Difference in naming convention for .class files
Release	JSP file	Class file
11i	AppsLocalLogin.jsp	_AppsLocalLogin.class
12.1	AppsLocalLogin.jsp	_AppsLocalLogin.class
12.2	AppsLocalLogin.jsp	__appslocallogin.class

One more significant change from r12.1 to r12.2 is Oracle 10g Application Server in 12.1.x has now been replaced by Oracle Fusion Middleware 11g.

We have WebLogic Admin manager and node manager that will now be managing oacore, forms and oafm as managed servers.

Prior to r12.2 they were part of opmn(Oracle Process Manager).

Above factors will play vital role in understanding steps to compile jsps in 3 versions later discussed in this article.

Steps to compile jsps

We use same script - ojspCompile.pl to compile jsps in all versions. However, location has changed, and steps have been summarized below.

I follow BBYM approach when performing any activity that involves files being modified manually or being modified by scripts.

Obviously, BBYM simply stands for 'Backup Before You Modify' :)

                                                   Oracle EBS 11i

                                             Compile Single JSP in Oracle ebs 11i

                                             Compile all JSP files in Oracle ebs 11i

                                                  Oracle EBS r12.1

                                             Compile Single JSP in Oracle ebs r12.1

                                             Compile all JSP files in Oracle ebs r12.1

                                                  Oracle EBS r12.2

                                             Compile Single JSP in Oracle ebs r12.2

                                             Compile all JSP files in Oracle ebs r12.2

References -

- 12.2 E-Business Suite JSP Page Handling, File Locations, Manual Compilation, And The Allowed JSP Features (Doc ID 1928874.1)

- Pythian Blogs

Compile all JSP files in Oracle ebs r12.2

Before you start compiling jsps and following below steps, I recommend understanding some key differences between 11i, r12.1 and r12.2 when it comes to compiling jsps. Please follow below link and then proceed further -

One-stop shop to Compile JSPs in 11i, r12.1 and r12.2

1. Take a backup of _pages directory that will be modified due to jsp compilation -

$ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/

$ cp -R _pages _pages29dec2019

$ ls -ld _pages*

drwxr-xr-x 5 applmgr oinstall 249856 Dec 29 16:36 _pages

drwxr-xr-x 5 applmgr oinstall 249856 Dec 29 16:56 _pages29dec2019

2. Stop apache, oacore and oafm services -

adapcctl.sh stop

admanagedsrvctl.sh stop oacore_server1

admanagedsrvctl.sh stop oafm_server1

3. Compile the jsps manually using the below command -

$ cd $FND_TOP/patch/115/bin/

$ perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p            

4. Check class file last modification date as follows -

$ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/_pages

$ ls -ltr | tail

-rw-r--r-- 1 applmgr oinstall  13669 Dec 29 17:14 __papost.class

-rw-r--r-- 1 applmgr oinstall   9942 Dec 29 17:14 __pasmtupost.class

-rw-r--r-- 1 applmgr oinstall   9921 Dec 29 17:14 __pauppost.class

-rw-r--r-- 1 applmgr oinstall   7015 Dec 29 17:14 __paynopdf.class

-rw-r--r-- 1 applmgr oinstall   9486 Dec 29 17:14 __paynoxml.class

-rw-r--r-- 1 applmgr oinstall   8350 Dec 29 17:14 __pyshsimpslp.class

-rw-r--r-- 1 applmgr oinstall   7153 Dec 29 17:14 __peruspdftemplate.class

-rw-r--r-- 1 applmgr oinstall   7015 Dec 29 17:14 __pergbrti.class

-rw-r--r-- 1 applmgr oinstall  13248 Dec 29 17:14 __createwavebookmarkable.class

drwxr-xr-x 2 applmgr oinstall     45 Dec 29 17:14 _txkcsa

5. Start oacore,oafm and Apache-

admanagedsrvctl.sh start oacore_server1

admanagedsrvctl.sh start oafm_server1

adapcctl.sh start

6. Clear your web browser cache

Compile Single JSP in Oracle ebs r12.2

1. Take a backup of .class file that will be modified due to jsp compilation -

$ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/_pages

Unlike r12.1, there is a different naming convention here in r12.2 with additional '_' being prefixed like as follows –

$ ls -ltr _jtffmxrg.class

ls: cannot access _jtffmxrg.class: No such file or directory

$ ls -ltr __jtffmxrg.class

-rw-r--r-- 1 applmgr oinstall 69675 Sep 16 19:13 __jtffmxrg.class

Also, all .class files are in lower-case while in earlier r12.1, they were same filenames with simply an '_' prefixed.

Example –

In r12.1

$ ls -ltr *OA.class*

-rw-r--r-- 1 applprod proddba 8843 Dec 29 11:32 _OA.class

In r12.2

$ ls -ltr *OA.class*

ls: cannot access *OA.class*: No such file or directory

$ ls -ltr *oa.class*

-rw-r--r-- 1 applmgr oinstall 10123 Sep 16 19:13 __oa.class

-rw-r--r-- 1 applmgr oinstall 12874 Sep 16 19:13 __jtfwtpoa.class

2. Check for last compilation time for .class file as follows -

$ cd $COMMON_TOP/_pages

$ ls -ltr __jtffmxrg.class

3. Your JSP file that will be compiled is stored under location -  $EBS_APPS_DEPLOYMENT_DIR/oacore/html

$ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html

$ ls -ltr jtffmxrg.jsp

-rw-r--r-- 1 applmgr oinstall 33875 Sep 16 19:13 jtffmxrg.jsp

Please note that above file will be used as reference to generate class (__jtffmxrg.class) file under - $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/_pages

2.To compile JSP file, perl utility - ojspCompile.pl is located under -

$FND_TOP/patch/115/bin/

ojspCompile.pl uses "-s" option to compile single jsp.

$ cd $FND_TOP/patch/115/bin/

$ perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile -s jtffmxrg.jsp

3. Check class file last modification date as follows -

$ cd $EBS_APPS_DEPLOYMENT_DIR/oacore/html/WEB-INF/classes/_pages

$ ls -ltr __jtffmxrg.class

-rw-r--r-- 1 applmgr oinstall 69607 Dec 29 16:36 __jtffmxrg.class

4. Bounce below services –

adapcctl.sh stop

admanagedsrvctl.sh stop oacore_server1

admanagedsrvctl.sh stop oafm_server1

admanagedsrvctl.sh start oacore_server1

admanagedsrvctl.sh start oafm_server1

adapcctl.sh start

5. Clear your web browser cache

Compile all JSP files in Oracle ebs r12.1

1. Take a backup of _pages directory that will be modified due to jsp compilation -

$ cd $COMMON_TOP/

$ cp -R _pages _pages23dec2019

$ ls -ld _pages*

drwxr-xr-x 5 applprod proddba 405504 Dec 23 09:13 _pages

drwxr-xr-x 5 applprod proddba 405504 Dec 29 11:28 _pages29dec20192

2. Stop application opmn services -

./adopmnctl.sh stopall

3. Compile the jsps manually using the below command -

$ cd $FND_TOP/patch/115/bin/

$ perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 4          

4. Check class file last modification date as follows -

$ cd $COMMON_TOP/_pages

$ ls -ltr | tail

-rw-r--r-- 1 applprod proddba  77656 Dec 29 11:33 _bisalrta.class

-rw-r--r-- 1 applprod proddba  47949 Dec 29 11:33 _bicsbadd.class

-rw-r--r-- 1 applprod proddba 107558 Dec 29 11:33 _asfAdminCalTypesMain.class

-rw-r--r-- 1 applprod proddba  41983 Dec 29 11:33 _amsStratDtailFrm.class

-rw-r--r-- 1 applprod proddba   9498 Dec 29 11:33 _amsProflFundTbl.class

-rw-r--r-- 1 applprod proddba  12538 Dec 29 11:33 _amsProdCatAssignmentTbl.class

-rw-r--r-- 1 applprod proddba  12262 Dec 29 11:33 _amsCodeDefinitionOverTbl.class

-rw-r--r-- 1 applprod proddba  53302 Dec 29 11:33 _amscmsgl.class

-rw-r--r-- 1 applprod proddba   5973 Dec 29 11:33 _ahlvwpTaskTreeDisplay.class

drwxr-xr-x 2 applprod proddba   4096 Dec 29 11:33 _txkcsa

4. Start opmn services -

./adopmnctl.sh startall

6. Clear your web browser cache

Compile Single JSP in Oracle ebs r12.1

1. Take a backup of .class file that will be modified due to jsp compilation -

$ cd $COMMON_TOP/_pages

$ ls -ltr _jtffmxrg.class

$ cp _jtffmxrg.class _jtffmxrg.class23dec2019

2. Check for last compilation time for .class file as follows -

$ cd $COMMON_TOP/_pages

$ ls -ltr _jtffmxrg.class

3. Your JSP file that will be compiled is stored under location -

$ cd $COMMON_TOP/webapps/oacore/html

$ ls -ltr jtffmxrg.jsp

Please note that above file will be used as reference to generate class(_jtffmxrg.class) file under - $COMMON_TOP/_pages

4.To compile JSP file, perl utility - ojspCompile.pl is located under -

$FND_TOP/patch/115/bin/

ojspCompile.pl uses "-s" option to compile single jsp.

$ cd $FND_TOP/patch/115/bin/

$ perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile -s jtffmxrg.jsp

5. Check class file last modification date as follows -

$ cd $COMMON_TOP/_pages/

$ ls -ltr _jtffmxrg.class

-rw-r--r-- 1 applprod proddba 68390 Dec 23 09:13 _jtffmxrg.class

5. Restart application opmn services-

./adopmnctl.sh stopall

./adopmnctl.sh startall

6. Clear your web browser cache

Compile all JSP files in Oracle ebs 11i

1. Take a backup of _pages directory that will be modified due to jsp compilation -

$ cd $COMMON_TOP/

$ cp -R _pages _pages23dec2019

$ ls -ld _pages*

2. Stop application opmn services -

./adapcctl.sh stop

2.Compile the jsps manually using the below command -

$ cd $JTF_TOP/admin/scripts

$ perl $JTF_TOP/admin/scripts/ojspCompile.pl --compile --flush -p 4       

3. Check class file last modification date as follows -

$ cd $COMMON_TOP/_pages

$ ls -ltr | tail

4. Start apache -

./adapcctl.sh start

5. Clear your web browser cache

Compile Single JSP in Oracle ebs 11i

1. Take a backup of .class file that will be modified due to jsp compilation -

$ cd $COMMON_TOP/_pages/_oa__html/

$ ls -ltr _jtffmxrg.class

$ cp _jtffmxrg.class _jtffmxrg.class23dec2019

2. Check for last compilation time for .class file as follows -

$ cd $COMMON_TOP/_pages/_oa__html/

$ ls -ltr _jtffmxrg.class

2. Your JSP file that will be compiled is stored under location -

$ $COMMON_TOP/html

$ ls -ltr jtffmxrg.jsp

Please note that above file will be used as reference to generate class(_jtffmxrg.class) file under - $COMMON_TOP/_pages/_oa__html/

2.To compile JSP file, perl utility - ojspCompile.pl is located under -

$JTF_TOP/admin/scripts

ojspCompile.pl uses "-s" option to compile single jsp.

$ cd $JTF_TOP/admin/scripts

$ perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile -s jtffmxrg.jsp

3. Check class file last modification date as follows -

$ cd $COMMON_TOP/_pages/_oa__html/

$ ls -ltr _jtffmxrg.class

-rw-r--r-- 1 applprod proddba 68390 Dec 23 09:13 _jtffmxrg.class

4. Restart application apache -

adapcctl.sh stop

adapcctl.sh start

5. Clear your web browser cache

Scaling New Heights!!! My Oracle Support Community rewards and recognition

This blog is about my experience so far with below 2 world’s largest interactive community dedicated to Oracle technologies so far,

1. My Oracle Support Community

2. OTN community

You can simply register yourself using below link –

https://community.oracle.com

It is hard to decide which one is my favorite out of the two. I believe it is more about purpose of these two communities and differences that makes nerds like us to choose between the two or follow both.

Understanding the difference-

OTN: Available for everyone

MOSC: Only accessible with My Oracle Support access.

OTN: Possibly larger audience, but not the place to discuss MOS notes or SRs, since you can't assume that everyone accessing an OTN community also has MOS access.

Basically, both communities are managed by different teams. OTN space is open and visible to anyone. Anyone can create an OTN account.

MOSC spaces are available to only those who have a valid support contract. You need an account(MOS account) attached to valid Customer Support Identifier.

Both MOSC and OTN platforms work on Jive and are merged for users to participate in both forums.

My experience when working with both -

1. You can raise any query and there are members who will support you, guide you and you never know, you will get solution from 

community before Oracle Service Request ticket you raised.

2. You relate to experts worldwide and both communities provide you common platform for technical discussions.

3. Even if you know solution for one issue, you may be surprised to know there are other ways/possibilities for same issue that

can be more effective and efficient than solution you already have.

4. Like I read this quote - 

     'Gaining knowledge is the first step to wisdom. Sharing it is first step to humanity.'                                                                                             -- Unknown

They help you gain knowledge and share your own experiences on the same platform.

Lastly, I would like to mention that this article will only evolve with time as I wish to continue serving these 2 communities.

So, say tuned!!!

Oracle EBS r12 migration to Solaris SPARC ** issues and solutions

Let me share a list of issues I faced when migrating a single node Oracle ebs r12.1.3 environment from Linux x86_64 to multinode Solaris SPARC machines. I have already shared ppt that was point of discussion before migration.

Oracle ebs db platform migration

Please note that below is set of issues and respective solutions while migration. I have not covered steps for migration in this post. I would also like to share list of references from metalink that were very handy during complete migration process.

·         Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2 (Doc ID 741818.1)

-          This document will be primarily used for database migration.

·         Application Tier Platform Migration with Oracle E-Business Suite Release 12 (Doc ID 438086.1)

-          Apps tier will be migrated referring above document.

·         Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 (Doc ID 387859.1)

-          We will be using this document to generate context file and executing autoconfig successfully.

·         Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12 (Doc ID 454811.1)

-          Referred for IAS_HOME upgrade

·         R12.1.1 : Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Oracle Solaris on SPARC (64-bit) - Doc ID 761568.1

1.1.               Appendix 1a

Error Details –

22:57:15 SQL> exec ctx_ddl.sync_index('IBC.IBC_ATTRIBUTE_BUNDLES_CTX');

BEGIN ctx_ddl.sync_index('IBC.IBC_ATTRIBUTE_BUNDLES_CTX'); END;

*

ERROR at line 1:

ORA-20000: Oracle Text error:

DRG-11422: linguistic initialization failed

DRG-11446: supplied knowledge base file

/u02/oracle/ERPDEV/db/11.2.0/ctx/data/enlx/droldUS.dat not installed

ORA-06512: at "CTXSYS.DRUE", line 160

ORA-06512: at "CTXSYS.CTX_DDL", line 948

ORA-06512: at line 1

                       

References –

https://naveenappsdba.wordpress.com/2012/03/14/oracle-e-business-suite-12-0-6-to-12-1-3-upgradation-issues-fixes/

                 Solution –

 a. Download the Oracle Examples Installation (formally Companion CD)

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

b. Unzip the Oracle Examples Installation

$ unzip linux.x64_11gR2_examples.zip

c. Go to the location in the installation media for the CTX File groups

$ cd examples/stage/Components/oracle.ctx.companion/11.2.0.1.0/1/DataFiles

d. Unzip the CTX Knowledge base files

$ unzip filegroup1.jar

Archive: filegroup1.jar

inflating: ctx/data/enlx/drofdUS.dat

inflating: ctx/data/enlx/drofiUS.dat

inflating: ctx/data/enlx/droldUS.dat

inflating: ctx/data/enlx/droliUS.dat

inflating: ctx/data/enlx/drolkUS.dat

inflating: ctx/data/enlx/drolsUS.dat

e. Go to the location of the knowledge base files we just extracted and copy them to the Oracle Database Home location

$ cd ctx/data/enlx/

$ cp * $ORACLE_HOME/ctx/data/enlx

1.2.               Appendix 1b

Error Details –

Enter value for 1: Enter value for 2: Enter value for 3: ERROR:

ORA-12705: Cannot access NLS data files or invalid environment specified

References –

AutoConfig is Failing with status 4 for a New Database with NLS Environment Error ORA-12705 (Doc ID 1612017.1)

Solution –

1. Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

2. After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.

3. Verify there are files in 9idata directory.

4. export ORA_NLS10=/u01/oratst/ERPDEV/db/11.2.0/nls/data/9idata.

1.3.                      Appendix 1c

Error Details –

Logfile -  $INST_TOP/logs

Unzip Count:83

Processing Disk1....

RC-20200: Fatal: Could not find Unzip. At this time only Native UnZip 5.X is supported.

Please make sure you have UnZip 5.X in your path and try again...

Cannot execute install of Applications Technology Stack

References –

      Rapidwiz Fails with error: RC-20200: Fatal: Could not find Unzip (Doc ID 858844.1)

https://community.oracle.com/message/11581966#11581966

     

Solution 1 –

1.     Update StartCD by renaming current one present in Stage directory (/backup/R12.1.1_Dump/StageR12)

2.     Copy and unzip patch  p8639046_R12_GENERIC.zip. This should overwrite files present in StartCD

Solution 2 –

1.     Upgrade unzip version present under /root/unzip541

  

1.4.                      Appendix 1d

Error Details –

While applying patch - 16778382 on 10.1.2 HOME for ebs r12.1.3 env.

ld: warning: file libucrypto.so.1: required by /usr/lib/libnsl.so, not found

Undefined                       first referenced

 symbol                             in file

MD5Init                             /usr/lib/libnsl.so

MD5Final                            /usr/lib/libnsl.so

MD5Update                           /usr/lib/libnsl.so

ld: fatal: symbol referencing errors

make: Fatal error: Command failed for target `/u01/appltst/ERPDEV/apps/tech_st/10.1.2/lib3

2/librw.so'

References –

http://erman-arslan-s-oracle-forum-read-only-until-june-12.2340467.n4.nabble.com/Reports-td3613.html

Solution 2 –

This was resolved after setting in .profile on apps node-

export LD_OPTIONS="-L/lib -L/lib/sparcv9"

1.5.                      Appendix 1e

One-off patches failed with below error.

"Run One-off Generic OPatches" configuration assistant has failed.

NOTE: this issue can be avoided with fixing patch with 8920270

Solution –

1) Make a backup of the file $ORACLE_HOME/config/run_opatch_generic.sh

   cd $ORACLE_HOME/config

   cp run_opatch_generic.sh run_opatch_generic.sh.pre8920270

2) Edit the $ORACLE_HOME/config/run_opatch_generic.sh and add the line

"-ocmrf <$ORACLE_HOME>/config/ias.properties" immediately after the -silent

switch. i.e.

#!/bin/sh

#

#opatch apply -silent -oh <ORACLE_HOME> -invPtrLoc <OH/oraInst.loc> <opatch dir>#

#

<oracle_home>/OPatch/opatch apply -silent -ocmrf <oracle_home>/config/ias.properties -oh <oracle_home> -invPtrLoc <oracle_home>/oraInst.loc <oracle_home>/opatches/4601861

if [ "$?" != "0" ]; then

echo "OPatch did not complete successfully..."

exit 1

fi

3) Make a backup of the file $ORACLE_HOME/config/run_opatch.sh

   cd $ORACLE_HOME/config

   cp run_opatch.sh run_opatch.sh.pre8920270

4) Edit the $ORACLE_HOME/config/run_opatch.sh and add the line

"-ocmrf <$ORACLE_HOME>/config/ias.properties" immediately after the -silent

switch.

5) If this is a GUI install then retry the configuration assistant "Run One-off Generic OPatches". For a failure in the silent install

a) Set the LD_LIBRARY_PATH

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib

b) Set the PERL5LIB

export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$PERL5LIB

6) Try “Run One-off Generic OPatches” again.

  

1.6.                      Appendix 1f

Error Details –

References –

Wrong Source Reference In Inventory Comps.Xml (Doc ID 580306.1)

R12.x COPYRETRY Error occurs writing to file dsaServer.sh when applying IAS patch 8626084 (Doc ID 1086315.1)

Solution -

cd $IAS_OH/inventory/ContentsXML

cp comps.xml comps.xml25mar2019

sed "s#/d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3#/u01/appltst/ERPDEV/apps/tech_st/10.1.3#g" comps.xml > comps_fixed.xml

View comps_fixed.xml file and after validation, replace it with original comps.xml file.

mv comps_fixed.xml comps.xml

1.7.                      Appendix 1g

Error Details –

 Solution –

Start opmn services before upgrading IAS_OH.

./adopmnctl startall

1.8.                      Appendix 1h

Error Details –

Error Snippet from Apache log -

--------

19/03/27 23:08:05 Start process

--------

Can't stat executable /u01/appltst/ERPDEV/inst/apps/ERPDEV_erpmgntstapp/ora/10.1.3/Apache/Apache/bin/.apachectl

               

Reference –

SR 3-19706670330 : Spin-off for SR 3-19705183621: Can't stat executable .apachectl

Solution –

1.     Download and review the readme of Patch 12722501.

/mig_backup/dev_bkp/apache_issue_patch_solaris/p12722501_101350_SOLARIS

2.  Apply patch 12722501 in Oracle Application Server 10.1.3.5 Oracle Home

    using OPatch after a backup.

3.  Run AutoConfig (adautocfg.sh) on the application tier.

1.9.                      Appendix 1i

Error Details –
The Export Extraction feature is not popping-up any reports in ERP
Cannot Export Data from E-Business Suite to Excel.

Issue has been resolved for "The Export Extraction feature is not popping-up any reports in ERP"

Below are reference notes and action plan performed to resolve this -

1. Domain Index - FND_LOBS_CTX was in failed/unusable state.

2. Sample test case -

SQL> set serveroutput on;

declare

db_file number;

mime_type varchar2(255) :='text/plain' ;

out_string varchar2(32767) :='Just some plain text that is stored' ;

web_server_prefix varchar2(500);

url varchar2(500);

begin

db_file :=fnd_gfm.file_create(content_type =>mime_type,program_name=>

'export');

fnd_gfm.file_write_line(db_file,out_string);

db_file :=fnd_gfm.file_close(db_file);

url:=fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,db_file,TRUE)

;

SQL>   2    3  dbms_output.put_line(url);

  4  end;

/  5    6    7    8    9   10   11   12   13   14   15   16

declare

*

ERROR at line 1:

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

ORA-06512: at "APPS.FND_GFM", line 696

ORA-06512: at line 8

3. Action performed -

Drop and recreated domain index -

SQL> DROP INDEX APPLSYS.FND_LOBS_CTX;

Index dropped.

SQL> @$FND_TOP/sql/aflobbld.sql applsys apps

PL/SQL procedure successfully completed.

Commit complete.

4. Testcase 2 -

SQL> set serveroutput on;

declare

db_file number;

mime_type varchar2(255) :='text/plain' ;

out_string varchar2(32767) :='Just some plain text that is stored' ;

web_server_prefix varchar2(500);

url varchar2(500);

begin

db_file :=fnd_gfm.file_create(content_type =>mime_type,program_name=>

'export');

SQL>   2    3    4    5    6    7    8  fnd_gfm.file_write_line(db_file,out_string);

  9   10  db_file :=fnd_gfm.file_close(db_file);

url:=fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,db_file,TRUE)

;

dbms_output.put_line(url);

 11   12  end;

/ 13   14   15   16

https://erpprdapp.psuad.ac.ae:443/OA_HTML/fndgfm.jsp?mode=download_blob&fid=1257

513&accessid=286223592715738799812792060162859000305

PL/SQL procedure successfully completed.

SQL>

Reference Notes -

1. Cannot Export Data from E-Business Suite to Excel (Doc ID 731921.1)

2. ORA-29861: Domain Index Is Marked LOADING/FAILED/UNUSABLE In Package OE_Atchmt_UTIL Procedure Delete (Doc ID 2247134.1)