UTL_FILE Sample Code

Posted by on July 9, 2007 No comments

Here is some sample code using the UTL_FILE package:

DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE;
BEGIN
--  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','A'); -- open in append mode
  file1 := UTL_FILE.FOPEN('/usr/tmp','log_file_test','A'); -- open in append mode
  string1 := TO_CHAR(SYSDATE) || ' UTL_FILE test';
  UTL_FILE.PUT_LINE(file1, string1); -- write a string to the file
  UTL_FILE.FFLUSH(file1);
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

[amazon asin=0596514468&template=iframe image&chan=default]    [amazon asin=0137142838&template=iframe image&chan=default]    [amazon asin=0071614214&template=iframe image&chan=default]

Resolving Yellow Bar Issue

Posted by on July 4, 2007 No comments

Problem: When the Oracle Application BRAVO instance is loaded, the ‘yellow bar’ appears when the forms are loaded.

Solutions:

1. Refer to Metalink Doc ID: 162488.1, “Section 6: Troubleshooting and those Yellow Warning Bars”

Navigate to Start > All Programs > JInitiator Control Panel 1.1.8.16

Check “Show Java Console”

Exit and reload BRAVO instance.

Java Console shows no errors.

Try:

(a) Uninstall Jinitiator and clear browser cache

(b) Log back into Applications to get the new plugin, (oajinit.exe) including the new identitydb.obj

(c) Install the Jinitiator on the Client PC and then Log into the Oracle Applications to download the new signed JAR files

IE Tools > Internet Options > Security

Trusted > Custom Level

Download unsigned ActiveX controls: Prompt

Automatic Prompting for File Downloads: Enable

Had to copy jinit11816.exe manually off the server and install on client

Try:

1. Source the environment and recreate certificate:

adjkey -initialize

2. Repackage JInitiator:

adjbuild.sh /u0/bravo/bravocomn/util/jinitiator /u0/bravo/bravocomn/util/jinitiator/jinit11816.exe

3. Run adadmin and generate product jars (FORCE REGENERATION)

4. Then:

(a) Uninstall JInitiator, clear browser cache, and jcache

(b) Log back into Applications to get the new plugin, (oajinit.exe) including the new identitydb.obj

(c) Install the Jinitiator on the Client PC and then Log into the Oracle Applications to download the new signed JAR files

Rename C:Program FilesOracleidentitydb.obj

Then retry from step 4 above

Try:

1. Removed identitydb.obj from /home/applmgr

2.Source the environment and recreate certificate:

adjkey -initialize

3. Repackage JInitiator:

adjbuild.sh /u0/bravo/bravocomn/util/jinitiator /u0/bravo/bravocomn/util/jinitiator/jinit11816.exe

4. Run adadmin and generate product jars (FORCE REGENERATION)

5. Then:

(a) Cleared browser cache

(b) Removed files from C:Documents and SettingsjeffersonbOracle Jar Cache

Try:

1. Copied identitydb.obj.20070703 to identitydb.obj in /home/applmgr

2. Repackage JInitiator:

adjbuild.sh /u0/bravo/bravocomn/util/jinitiator /u0/bravo/bravocomn/util/jinitiator/jinit11816.exe

3. Run adadmin and generate product jars (FORCE REGENERATION)

4. Then:

(a) Cleared browser cache

(b) Removed files from C:Documents and SettingsjeffersonbOracle Jar Cache

(c) Launched Oracle Applications and loaded form

Solution 1:

Copied $OA_HTML/oajinit.exe off the server and onto the client machine.

Unzip oajinit.exe in a directory and run adjbuild/jinit11816.exe.

The following solution worked:

Refer to:

Subject:  How to use,create and /or update Digital Certificates for 11i Applications

Doc ID:   Note:365735.1

How to share Digital Certificate across multiple instances

———————————————————-

1. Copy Associated Files

+ Copy from alpha to bravo the following files respectively:

a. ADCERT.txt (certificate directive file)

$APPL_TOP/admin/out/adcert.txt

b. ADSIGN.txt (used to pass arguments to JRI for signing JAR files.)

$APPL_TOP/admin/adsign.txt

c. APPLTOP.cer (Certificate file that gets imported into the identitydb.obj)

$APPL_TOP/admin/appltop.cer

d. OAJINIT.exe (Repackaged Jinitiator executable that includes identitydb.obj)

$OA_HTML/oajinit.exe

Note: It will not be necessary to repackage your JInitiator file oajinit.exe if you copy it over.

e. IDENTITYDB.obj (Identity Database File used to sign JAR files on the server)

$HOME/identitydb.obj

DO NOT repackage Jinitiator.

2. Regenerate JAR files on bravo

+ Run ADADMIN to Regenerate (sign) the JAR files on each middle tier

a. Launch ADADMIN (Ensure you are APPLMGR with permissions to write to adadmin.log)

b. Choose option number 1 to Generate Files, then 5 to regenerate JAR Files making sure to enter ‘yes for force regeneration, which will resign every JAR file using the new digital certificate that you just copied over from the alpha instance.

In Summary:

Started to get yellow bar:
1. Deleted C:Program FilesOracleidentitydb.obj
2. Deleted Profile Oracle Cache
3. Uninstalled all JInitiators, verified by checking C:Program FilesOracle
4. Manually reinstalled JInitiator (oajinit.exe) from november instance – yellow bar still appearing on logon
5. Tried regenerating JAR files using adadmin – completed with error
6. Regenerated certificate using adjkey -initialize, then reran adadmin to regenerate JAR files – yellow bar still appearing on logon
7. Cleared browser cache and jcache – C:Program FilesOracleJInitiator 1.1.8.16jcache – yellow bar still appearing on logon
8. Manually copied identitydb.obj from /home/applmgr to  C:Program FilesOracle – WORKING

Failed to generate product JAR files in JAVA_TOP

Posted by on June 28, 2007 No comments

Problem: While applying patch 3218526, the following error occurs:

Failed to generate product JAR files in JAVA_TOP -
   /u02/bravo/bravocomn/java.

adogjf() Unable to generate jar files under JAVA_TOP

AutoPatch error:
Failed to generate the product JAR files

You should check the file
/u02/bravo/bravoappl/admin/bravo/log/adpatch_3218526_20070628a.log

for errors.

Solution:

Issue encountered while running this patch on BRAVO.

To resolve this issue, on the AP node (web tier):

1. Source the environment
2. Run:
adjkey -initialize
3. Run:
adjbuild.sh /u0/bravo/bravocomn/util/jinitiator /u0/bravo/bravocomn/util/jinitiator/jinit11816.exe
4. Run adadmin on both nodes and generate product jars

EBS Cloning Environment Variables

Posted by on June 7, 2007 No comments

Problem: What environment variables must be set before cloning, assuming no prior EBS environment file is present?

Solution:

Here is a sample script to source before running post-clone steps. It sets up the location to the PERL and Java executables. Note the directories must be changed to reflect your environment.

export PERL5LIB=/u01/deltaora/iAS/Apache/perl/lib/5.00503:
/u01/deltaora/iAS/Apache/perl/lib/site_perl/5.005:/u01/deltaappl/au/11.5.0/perl
export PATH=.:/u01/deltaora/iAS/Apache/perl/bin:/u01/deltacomn/util/jre/1.1.8/bin:$PATH
export LD_ASSUME_KERNEL=2.4.19

Setting Up Oracle Applications Desktop Integrator (ADI) 7.2

Posted by on June 6, 2007 No comments

This documentation covers locating the latest version of ADI on Metalink, downloading it, installing it on the client machine and configuring it to access an Oracle Applications instance.

Step 1:        Log on to Oracle Metalink using your logon information (email address and password).

Step 2:        Search for and read “Application Desktop Integrator (ADI) – FAQ” (Doc ID: Note:106667.1) to obtain instructions to download the latest version of ADI.

(Adapted from the FAQ):

QUESTION: Where can I download ADI from?

Download ADI from Metalink

Log on to http://metalink.oracle.com
Click the Patches & Updates tab.
On the Patches & Updates page, click Advanced Search.
Enter:
Product or Product Family: Click the Torchlight icon to search for Applications Desktop Integrator (adi).
Release: ADI 7.2
Platform or Language: Microsoft Windows (32-bit) Client
Patch Type: Any
Press Go and the 7.2 base release will be available for download.

Step 3:        The results of the search are returned at the bottom of the page.

Click the link ‘3966101’ to locate the patchset.

Step 4:        You may be required to enter a username and password to access the file. Enter the email address and password that you used to log onto Metalink.

Step 5:        Details about the patchset are displayed on screen.

Click Download to begin downloading the file. Note that the download size is 71MB.

Step 6:        Click Save in the File Download dialog box, and then choose a location to save the file.

Step 7:        After downloading, execute the patchset by double-clicking on it.

Step 8:        Enter a location to unzip the files to, and then click Unzip.

Step 9:        Ensure that all files were unzipped correctly (you should see ‘5179 file(s) unzipped successfully’), and then click Ok.

Then click Close to close the WinZip Self-Extractor program.

Step 10:   In the folder that ADI was unzipped to, read the README.TXT file. It points the user to the ADI Installation Guide (ADI.PDF), which should be read before proceeding.

Step 11:    After reading the installation guide, double-click Setupadi.exe to begin installation.

Caution: Remove headphones while installing this product since it uses a lot of extremely annoying sound effects.

Step 12:   The Oracle ADI Installer loads.

Click Oracle Applications Desktop Integrator for Excel 2000/XP/2003.

Step 13:   Click Yes to begin installation.

Step 14:   The Oracle Installer Settings form is displayed. In the Company Name field, enter the name of the company. Then click Ok.

Step 15:   The Software Asset Manager appears.

Install all of the products listed under Available Products. The products can be installed individually (one at a time), or all products can be selected and installed.

Note: The ADI Online Help will require you to choose a language to install.

Step 16:   After installing the products, click Exit to exit the installer. You will be required to reboot the machine after exiting the installer.

Step 17:   After the computer restarts, load Applications Desktop Integrator by clicking on the Start button, All Programs, Oracle ADI, and finally Applications Desktop Integrator.

Note: Let the client log on to the machine so that you can configure ADI under the user’s id. This is necessary since signon details are not shared between users.

Step 18:   When the application is first loaded, a message appears informing the user to select an Applications database.

Step 19:   After reading the message by the Office Assistant, click on the Signon button.

Step 20:   The Signon form appears. Since there are no predefined databases, click Define Databases to define a new Applications database.

Step 21:   The Select Database form appears. Click Add to add a database.

Step 22:   Fill out the fields in the Add Database Details form, and then click OK.

Copy sqlnet.ora and tnsnames.ora from the “806 NET80” directory in the Project Folder to c:orantnet80admin and c:orantnetworkadmin. Note that these files contain configuration information for the production instance only.

In addition, each instance has a unique server ID. To obtain the server ID for the production instance, navigate to the Project Folder, and then open the file “Server ID.txt”. Copy the hexadecimal string under MFGP and paste it into the Server ID field in the Add Database Details form.

Step 23:   After setting up the database details, save the information and log on to ADI using an Oracle Applications username:

After logging on, perform the following:

  • Choose a Responsibility (for example, GL Super User)
  • From the ADI Toolbar, click Submit Report
  • Click Financial Statement
  • Click in the Report Name, enter % as criteria, click OK and then select a report (for example, Unaudited Balance Sheet)
  • Choose Period (for example, APR-07)
  • Click the Publishing button
  • Select Spreadsheet
  • Click Submit

The job is submitted and completes within a few minutes. It will automatically open in Excel on completion.

Errors Encountered

Error Message: “No Listener”

Solution: Either instance on server does not exist or entry in TNSNAMES.ORA is incorrect. If TNS entry in the local TNSNAMES.ORA file is correct, then listener or database is not started on the server.

Error Message: “ORA-12571: TNS Packet Writer Failure”

Solution: Cannot send packets to the server. A firewall is blocking packets on the client machine.

Error Message: “ORA-12154: TNS: could not resolve service name”

Solution: Ensure that the service name is in c:orantnet80admintnsnames.ora

Check that c:orantnet80adminsqlnet.ora has names.default_domain and name.default_zone commented off.

Check %WINDIR%system32driversetchosts file has hostname and ip address.

Error Message: “An error occurred while attempting to establish an Applications File Server connection. There may be a network configuration problem, or the TNS listener may not be running. Nodename : ORACLESRV”

Solution: Incorrect FNDFS entry is in the TNSNAMES.ORA file. Recopy sqlnet.ora and tnsnames.ora from server onto client machine.

Metalink References

Subject: Tns Packet Writer Failure When Trying To Connect To ADI Ora-12571 (Doc ID: Note:264952.1)

Subject: Unable To Start ADI ORA-12571:TNS:Packet Writer Failure (Doc ID: Note:351088.1)

Subject: Troubleshooting the “Error Occurred While Attempting to Establish an Applications File Server Connection” (Doc ID:  Note:117012.1)

[amazon asin=0071779728&template=iframe image&chan=default]     [amazon asin=1849680620&template=iframe image&chan=default]     [amazon asin=0071622292&template=iframe image&chan=default]

Running Oracle Report From Command Line

Posted by on June 5, 2007 No comments

Problem: How is an Oracle Report running from the command line?

Solution:

1. Source the appl_top environment file

2. ar60run report=/u0/thetaappl/fnd/11.5.0/reports/US/FNDSCARU.rdf batch=yes
destype=file desname=/u0/thetacomn/admin/out/theta_zetadb/otest_tcl_20070605a.out
desformat=/u0/thetaappl/fnd/11.5.0/reports/HPL printer=noprint pagesize=132x45

ORA-06502 Saving Work Order

Posted by on June 4, 2007 No comments

Problem: The following error is occurring:

The following errors occur when trying to update operations of Firm work orders:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at “APPS.FND_MESSAGE”, line 418

ORA-06512: at “APPS.FND_MESSAGE”, line 461

ORA-06512: at “APPS.FND_MESSAGE”, line 585

ORA-06512: at “APPS.FND_MESSAGE”, line 537

ORA-06512: at “APPS.APP_EXCEPTION”, line 42

ORA-06512: at “APPS.EAM_WORKORDER_UTIL_PKG”, line 1165

ORA-06512: at “APPS.EAM_OPERATIONS_PKG”, line 347

Steps to Reproduce Issue:

  1. Log on as user operations
  2. Enterprise Asset Management
  3. Work Orders > Work Order
  4. Seattle Maintenance
  5. WO90203
  6. Click Operations
  7. When the start and end time is changed, error occurs.

Solution: Apply Patch 4432063.

Refer to Metalink Doc ID: 357568.1 (Error ‘ORA-06502’ Occurs When Saving Changes To Work Order Operation)

Upgrading 8.1.7 to 9.2.0

Posted by on May 28, 2007 No comments

This documentation shows how to upgrade Oracle Applications RDBMS version 8.1.7 to 9.2.0.

Step 1:        Go to Metalink and order or download the three CDs for 9.2.0.1, for the correct platform

Step 2:        Shutdown the application tier services and the database and listener services.

Step 3:        The current RDBMS top is proddb/8.1.7. Create a new RDBMS top directory:

mkdir /raid01/oratest/oracle/proddb/9.2.0

Step 4:        Set the following environment variables:

ORACLE_HOME=/raid01/oratest/oracle/proddb/9.2.0

ORACLE_SID=TEST

Step 5:        Ensure that any TNSLSNR variables are unset.

Step 6:        If the installation was previously cancelled half-way, deinstall the installed products and/or delete the files in 9.2.0:

cd /raid01/oratest/oracle/proddb/9.2.0

rm –rf *

Step 7:        When installing, use the following values:

Destination Name: TEST_920

Destination Path: /raid01/oratest/oracle/proddb/9.2.0

Begin installation.

Relink of module “WFRESGEN” failed

Posted by on May 18, 2007 No comments

While applying the patch AD.I.4 patch 4712852 on a multi node instance, the following error encountered during the relinking process on Apps server, which was successfully run on the Admin Node.

Relinking module 'WFRESGEN' in product fnd ...
make -f /disk1/oratest/oracle/prodappl/admin/TEST/out/link_fnd_26515.mk
/disk1/oratest/oracle/prodappl/fnd/11.5.0/bin/WFRESGEN
Starting link of fnd executable 'WFRESGEN' on Fri May 18 11:57:46 GMT 2007
make: Fatal error: Don't know how to make target
`/disk1/oratest/oracle/prodappl/fnd/11.5.0/lib/wfresgcp.o'
Done with link of fnd executable 'WFRESGEN' on Fri May 18 11:57:46 GMT 2007
Relink of module "WFRESGEN" failed.
See error messages above (also recorded in log file) for possible
reasons for the failure. Also, please check that the Unix userid
/
/disk1/oratest/oracle/prodappl/fnd/11.5.0/bin/WFRESGEN
Starting link of fnd executable 'WFRESGEN' on Fri May 18 11:57:46 GMT 2007
make: Fatal error: Don't know how to make target
`/disk1/oratest/oracle/prodappl/fnd/11.5.0/lib/wfresgcp.o'
Done with link of fnd executable 'WFRESGEN' on Fri May 18 11:57:46 GMT 2007
Relink of module "WFRESGEN" failed.
See error messages above (also recorded in log file) for possible
reasons for the failure. Also, please check that the Unix userid
running adrelink has read, write, and execute permissions
on the directory /disk1/oratest/oracle/prodappl/fnd/11.5.0/bin,
and that there is sufficient space remaining on the disk partition
containing your Oracle Applications installation.

Solution:-

a) Aborted the patch

b) Copied the missing file wfresgcp.o from the Admin Node to Apps node

c) Relinked all the modules (this process took 1 hr on each node)

d) Re-run the patch and successfully completed

Troubleshooting APP-FND-00500 Error

Posted by on May 5, 2007 No comments

Problem:

The APP-FND-00500 error was encountered when printing TD4 slips.

Solution:

Set printer output to RAW.

Reference:

Metalink Doc ID: 200359.1 (Oracle Application Object Library Printer Setup Test), which will assist in validating the printer configuration/setup in Oracle Applications.