Temple of Oracle: April 2013

Wednesday, April 24, 2013

Query to check value of Profile Options in R12

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
to_char(v.last_update_date,'DD-MON-RR') "Last Updated",
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set

Caused by: java.lang.SecurityException: class "oracle.apps.fnd.common.Version"'s signer information does not match signer information of other classes in the same package

Oracle R12 application and getting following error when trying to launch General Ledger Account Hierarchy Manager
Java Plug-in 1.6.0_07
Using JRE version 1.6.0_07 Java HotSpot(TM) Client VM
User home directory = C:\Documents and Settings\Administrator
---
c: clear console window
f: finalize objects on finalization queue
g: garbage collect
h: display this help message
l: dump classloader list
m: print memory usage
o: trigger logging
p: reload proxy configuration
q: hide console
r: reload policy configuration
s: dump system and deployment properties
t: dump thread list
v: dump thread stack
x: clear classloader cache
0-5: set trace level to
----
proxyHost=null
proxyPort=0
connectMode=HTTP, native.
Forms Applet version is : 10.1.2.3
java.lang.ExceptionInInitializerError
at oracle.apps.fnd.tcf.ClientDispatcher.initServer(Cl ientDispatcher.java:381)
at oracle.apps.fnd.tcf.ClientDispatcher.( ClientDispatcher.java:216)
at oracle.apps.fnd.tcf.ClientDispatcher.( ClientDispatcher.java:193)
at oracle.apps.gl.jahe.javaui.client.Jahe.connectDispatcher(J ahe.java:1384)
at oracle.apps.gl.jahe.javaui.client.Jahe.jbInit(Jahe.java:10 46)
at oracle.apps.gl.jahe.javaui.client.Jahe.jump(Jahe.java:863)
at oracle.apps.gl.jahe.javaui.client.Jahe.start(Jahe.java:759 )
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.SecurityException: class "oracle.apps.fnd.common.Version"'s signer information does not match signer information of other classes in the same package
at java.lang.ClassLoader.checkCerts(Unknown Source)
at java.lang.ClassLoader.preDefineClass(Unknown Source)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknow n Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$000(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)

Solution:
You can try the following:
1. Run Adadmin and regenerate all the jar files with FORCE option.
This can be done as per the steps below:
1.1. Run adadmin
a)select AD administration main menu option as 1) generate applications file menu
b) select generate applications files option as 4)generate product jar files
When asked for the option,
Do you wish to force regeneration of all jar files? [No] ?
Give Yes as the answer.
This will force all the jars to be rebuilt.
1.2 Once jar rebuild process is over, do the following to verify whether jars have been rebuilt successfully or not.
a. cd $FND_TOP/java/jar
b. ls -lrt
1.3 Verify, that the timestamps are the latest and reflect the jar rebuilt timing.
2. Clear java/jre cache on client PC.
3. Bounce Apache.
4. Retest the problem by logging out and logging back in.

pragma autonomous_transaction

Autonomous transaction clause will initiate an independent transaction , not new session as per below test case.
DECLARE
PROCEDURE A
AS
pragma autonomous_transaction;
BEGIN
dbms_output.put_line ( 'Inside Autonomous transaction My session id ' || sys_context ( 'USERENV' , 'SID' ) ) ;
ROLLBACK;
END ;
BEGIN
dbms_output.put_line ( 'My session id ' || sys_context ( 'USERENV' , 'SID' ) ) ;
a;
dbms_output.put_line ( 'Outside Autonomous transaction My session id ' || sys_context ( 'USERENV' , 'SID' ) ) ;
END;

How to use special character in APPS password in R12?

How to use special character in APPS password in R12?

How to change passwords to include special characters using FNDCPASS [ID 1336479.1]
FNDCPASS Troubleshooting Guide For Login and Changing Applications Passwords [ID 1306938.1]
Secure Configuration Guide for Oracle E-Business Suite Release 12 [ID 403537.1]
Secure Configuration Guide for Oracle E-Business Suite 11i [ID 189367.1]

AutoPatch error:

Missing prerequisite codelevel for entity 'txk'.

This patch requires the following codelevel or higher: 'R12.TXK.B.3'.
The current on-site codelevel for the entity 'txk' is : 'R12.TXK.B.1'.

In order to apply this patch you must first apply a patch that introduces
at least the minimum required codelevel.

You may merge this patch with a patch that introduces the required codelevel.

AutoPatch error:

A fatal error related to codelines or codelevels has occurred.

You need to apply this patch as a pre-req -- Patchset 8919489 R12.TXK.B.delta.3
It is mentioned in the patch README file -- https://updates.oracle.com/Orion/Services/download?type=readme&aru=13428075
Required Code levels
This patch requires additional code levels to have been successfully
applied to your system before it can be applied. These code levels,
as at the time that this patch was built, are listed below. Please check
'My Oracle Support' to determine if any of these have been superseded
R12.TXK.B.delta.3

SRW.USER_EXIT('FND SRWINIT') in the Before-Report trigger

Problem:
I am using a Global temporary table in one of my Oracle reports which is being populated in After-Param-Form trigger. But the problem I am facing is if I use the SRW.USER_EXIT('FND SRWINIT') in the Before-Report trigger, the records in Global temporary table gets deleted and the data can't be used in the report query.

Solution:
Two ideas:
- Move the population of the GT table to after the call to SRWINIT
or
- change the GT table to be ON COMMIT PRESERVE ROWS - I expect that SRWINIT is doing a commit/rollback which would remove your data if the GT table is set to ON COMMIT DELETE ROWS (the default).

Oracle error -1552: ORA-01552: cannot use system rollback segment for non-system tablespace 'APPS_TS_TX_DATA' has been detected in FND_GSM_UTIL.upload_context_file.

Problem:
Attempting upload of Context file and templates to database...ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -1552: ORA-01552: cannot use system rollback segment for non-system tablespace 'APPS_TS_TX_DATA' has been detected in FND_GSM_UTIL.upload_context_file.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -1552: ORA-01552: cannot use system rollback segment for non-system tablespace 'APPS_TS_TX_DATA' has been detected in FND_GSM_UTIL.upload_context_file.
        at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:281)
        at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
        at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:678)
        at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:222)
        at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1680)
        at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:755)
FAILED
COMPLETED

Updating rdbms version in Context file to db111
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed with errors.
[oraprod@bperpdb EBSPROD_bperpdb]$

Solution;
Check Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1], as mention note the recommended parater for undo_management is AUTO.

Tuesday, April 23, 2013

Scripts to compile invalid objects in Oracle

sqlplus -s / as sysdba << EOF
set heading off;
set verify off;
set feedback off;
set trims on;
spool compile_objects.sql
prompt set echo on
select 'Alter '||rtrim(object_type, ' BODY')||' '||owner||'.'||object_name
||' compile'||decode(object_type, 'PACKAGE BODY', ' BODY', NULL)||chr(10)||'/'
from dba_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE',
'PACKAGE BODY', 'TRIGGER', 'VIEW','MATERIALIZED VIEW')
and status = 'INVALID'
and owner='APPS'

After installing Oracle apps R12. but at the end of installation I got below error.

After installing Oracle apps R12. but at the end of installation I got below error.
RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button.

This is a generic error, so please check teh installation log files for details about this error.

R12 Installation Post Steps: Apache Fails : RW-50015 Cannot Load Libphp4.So, Unresolved Symbols [ID 736689.1]
Fresh R12 Installation Fails with RW-50015 [ID 452101.1] To BottomTo Bottom
R12 Rapidwiz Postinstall Steps Http, Login page, Virtual directory, JSP, Help Page Fails [ID 414414.1]

Journal posting error in R12

Journal posting error in R12

<< glpsqp() 23-APR-2013 16:26:08

glpsqs() 23-APR-2013 16:26:08

PERF0005: Oracle error detected in glpsqs() - ORA-20001: An active sequence assignment is required.
ORA-06512: at "A

Please find the below document for the issue.

R12: Posting Throws ORA-06512: at "APPS.GL_GLPPOS_ACCTSEQ_PKG" [ID 1058014.1]
Posting a Journal Fails in GLPHOK due to ORA-20001 error [ID 242002.1]
GL Posting Program Terminated With Errors in GLPUBR [ID 292683.1]

List of documents on OID and OAM integration.

List of documents on OID and OAM integration.

Overview of Single Sign-On Integration Options for Oracle E-Business Suite" [ID 1388152.1]
Integrating Oracle E-Business Suite Release 12 with Oracle Access Manager 11gR1 (11.1.1.5) using Oracle E-Business Suite AccessGate" [ID 1309013.1]
Oracle E-Business Suite Release 12 Technology Stack Documentation Roadmap [ID 380482.1] -- 3.2 External Integrations
Useful Articles for the Oracle E-Business Suite Technology Stack for Technical Consultant / DBA [ID 828157.1] -- SSO/OID

Unable to login after adcfgclone.pl on both tier on EBS R12 on OEL OS.

Unable to login after adcfgclone.pl on both tier on EBS R12 on OEL OS.

Kindly verify the following:
1. grep login $CONTEXT_FILE. Is the output what you are using to connect?
2. Did autoconfig complete without errors?
3. Are you able to try it on another browser and see what it says?
4. Check the post the output of "adopmnctl.sh status" command for eny error.
5. Can you access the main login page from the server?
6. Do you meet the requirements in (Recommended Browsers for Oracle E-Business Suite Release 12 [ID 389422.1])?

Saturday, April 6, 2013

Oracle9i Features

Oracle9i Features
Oracle offers a comprehensive high-performance infrastructure for e-business. It is called Oracle9i.
Oracle9i includes everything needed to develop, deploy, and manage Internet applications.
Benefits include:
• Scalability from departments to enterprise e-business sites
• Robust, reliable, available, secure architecture
• One development model, easy deployment options
• Leverage an organization’s current skillset throughout the Oracle platform (including SQL,
PL/SQL, Java, and XML)
• One management interface for all applications
• Industry standard technologies, no proprietary lock-in

Referencing a Nested Table Element

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  PROCEDURE verify_name(the_name VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(the_name);
  END;
BEGIN
  FOR i IN names.FIRST .. names.LAST
  LOOP
      IF names(i) = 'J Hamil' THEN
        DBMS_OUTPUT.PUT_LINE(names(i)); -- reference to nested table element
      END IF;
  END LOOP;
  verify_name(names(3));  -- procedure call with reference to element
END;
/

Declaring an Associative Array

DECLARE
   TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   emp_tab EmpTabTyp;
BEGIN
   /* Retrieve employee record. */
   SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100;
END;
/

Declaring Collections with %TYPE

DECLARE
   TYPE few_depts IS VARRAY(10) OF VARCHAR2(30);
   TYPE many_depts IS VARRAY(100) OF VARCHAR2(64);
   some_depts few_depts;
-- If we change the type of some_depts from few_depts to many_depts,
-- local_depts and global_depts will use the same type 
-- when this block is recompiled
   local_depts some_depts%TYPE;
   global_depts some_depts%TYPE;
BEGIN
   NULL;
END;
/

DB Connection format for R12 OAF

The –dbconnection information can actually be in one of two formats:

Format 1: The long format

You can put the entire TNS Names entry. For example, your entry might read as

follows:

-dbconnection "(description = (address_list = (address =

(community = tcp.world)(protocol = tcp) (host

=es0006.oracle.com)(port = 1521))) (connect_data = (sid =

es0006)))“

Format 2: The short format (preferred)

You can put an entry similar to the following:

-dbconnection es0006.oracle.com:1521:es0006

What’s in BC4J?

What’s in BC4J?

BC4J is the Model layer of the MVC Pattern.
It includes the following basic BC4J components.

Application Module (AMs)
Entity Objects(EOs)
View Objects(VOs)
and Others

They also have additional capabilities like Data Binding, AM pooling and session management, and datanase transactions.

Temple of Oracle