Vikram Das
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps
Updated: 16 hours 3 min ago
550 5.7.1 Relaying denied. IP name lookup failed
Recently we configured a BigIP virtual for SMTP called smtp.justanexample.com
Whenever I tried sending mail through this it would complain:
# telnet smtp.justanexample.com 25
Trying 192.168.10.201...
Connected to smtp.justanexample.com
Escape character is '^]'.
220 smtp1.justanexample.com ESMTP Sendmail 8.13.8/8.13.8; Mon, 15 Apr 2013 22:34:54 -0400
mail from:vikram.das@justanexample.com
250 2.1.0 vikram.das@justanexample.com... Sender ok
rcpt to:vikram.das@ge.com
550 5.7.1 vikram.das@justanexample.com... Relaying denied. IP name lookup failed [192.168.10.2]
Doing an nslookup on this name failed. If I did https://192.168.10.2 it would go to the BigIP user interface. This IP address was somewhere related to the BigIP device, and did not have a hostname in DNS. I have sent a mail to the network team for further investigation about this IP.
The usual practice for allowing relay from certain hosts is to add those hosts to /etc/mail/local-host-names. However since this IP was not present in DNS, I added the following line to /etc/mail/access on all smtp nodes :
Connect:192.168.10.2 RELAY
and bounced sendmail on Linux
/etc/init.d/sendmail restart
The Relaying Denied is not coming anymore.
Whenever I tried sending mail through this it would complain:
# telnet smtp.justanexample.com 25
Trying 192.168.10.201...
Connected to smtp.justanexample.com
Escape character is '^]'.
220 smtp1.justanexample.com ESMTP Sendmail 8.13.8/8.13.8; Mon, 15 Apr 2013 22:34:54 -0400
mail from:vikram.das@justanexample.com
250 2.1.0 vikram.das@justanexample.com... Sender ok
rcpt to:vikram.das@ge.com
550 5.7.1 vikram.das@justanexample.com... Relaying denied. IP name lookup failed [192.168.10.2]
Doing an nslookup on this name failed. If I did https://192.168.10.2 it would go to the BigIP user interface. This IP address was somewhere related to the BigIP device, and did not have a hostname in DNS. I have sent a mail to the network team for further investigation about this IP.
The usual practice for allowing relay from certain hosts is to add those hosts to /etc/mail/local-host-names. However since this IP was not present in DNS, I added the following line to /etc/mail/access on all smtp nodes :
Connect:192.168.10.2 RELAY
and bounced sendmail on Linux
/etc/init.d/sendmail restart
The Relaying Denied is not coming anymore.
Categories: APPS Blogs
Path not found (/shared/FTI Reports/_Portal/Main Dashboard/dashboard layout)
In OTM FTI you may get an error like this:
OTM > Transportation Intelligence > Dashboard
Path not found (/shared/FTI Reports/_Portal/Main Dashboard/dashboard layout)
Error Details
Error Codes: U9KP7Q94
From OTM install guide In the $OBIEE_DATA/web/config/ directory, edit the instanceconfig.xml as follows:
a. Change the CatalogPath to point the new “aa” directory that was created when you extracted the aa_webcat.zip file. The template shows the following line.
vi instanceconfig.xml
Search for string CatalogPath and you'll find this
$OBIEE_DATA/web/catalog/samplesales
Edit this line so that it looks like this:
$OBIEE_DATA/web/catalog/aa
Note: Do not put in the $OBIEE_DATA only add the reference to the “aa” directory.
The entry may look like this:
/opt/oraclebi/oraclebidata/web/catalog/aa Save.
Restart services.
oc4j -start
./run-sa.sh start64
./run-saw.sh start64
./run-sch.sh start64
OTM > Transportation Intelligence > Dashboard
Path not found (/shared/FTI Reports/_Portal/Main Dashboard/dashboard layout)
Error Details
Error Codes: U9KP7Q94
From OTM install guide In the $OBIEE_DATA/web/config/ directory, edit the instanceconfig.xml as follows:
a. Change the CatalogPath to point the new “aa” directory that was created when you extracted the aa_webcat.zip file. The template shows the following line.
vi instanceconfig.xml
Search for string CatalogPath and you'll find this
$OBIEE_DATA/web/catalog/samplesales
Edit this line so that it looks like this:
$OBIEE_DATA/web/catalog/aa
Note: Do not put in the $OBIEE_DATA only add the reference to the “aa” directory.
The entry may look like this:
/opt/oraclebi/oraclebidata/web/catalog/aa Save.
Restart services.
oc4j -start
./run-sa.sh start64
./run-saw.sh start64
./run-sch.sh start64
Categories: APPS Blogs
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06512: at "APPS.WF_EVENT", line 3600
Akhilesh pinged me today and told me that autoconfig was not working on a newly upgraded R12 instance. It was failing with these errors:
AutoConfig could not successfully execute the following scripts: Directory: /r12ascp/erpapp/10.1.3/perl/bin/perl -I /r12ascp/erpapp/10.1.3/perl/lib/5.8.3 -I /r12ascp/erpapp/10.1.3/perl/lib/site_perl/5.8.3 -I /usnapss1/erpapp/appl/au/12.0.0/perl -I /r12ascp/erpapp/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi /r12ascp/erpapp/inst/apps/usnapss1_r12ascp/admin/scripts/adexecsql.pl sqlfile=/r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install afwebprf.sql INSTE8_PRF 1 amscmprf.sql INSTE8_PRF 1 amswebprf.sql INSTE8_PRF 1 cncmprf.sql INSTE8_PRF 1 csfadmprf.sql INSTE8_PRF 1 oksfrmprf.sql INSTE8_PRF 1
Directory: /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install ibywebprf.sh INSTE8_PRF 1
If we tried running any of the above scripts, they failed with this error:
SQLPLUS Executable : /r12ascp/erpapp/10.1.2/bin/sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 4 12:04:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "APPS.WF_EVENT", line 3600
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
So I checked the code on line 3600 for wf_event package:
select line,textfrom dba_sourcewhere name='WF_EVENT'and line between 3550 and 3610;
3600
wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));I did another code search:
select line,textfrom dba_sourcewhere text like '%WF_SYSTEM_GUID%';This was the result of the query: select text into l_result from wf_resources where name='WF_SYSTEM_GUID' andlanguage='US';
SQL> select text from wf_resources where name='WF_SYSTEM_GUID' and 2 language='US';
TEXT--------------------------------------------------------------------------------CAFECAFE-0013-0001-0029-ABCDEFABCDEF
That was weird as that is the GUID used by Oracle JRE plugin 1.3.1.29.
I checked the actual GUID:SQL> select guid from wf_systems;
GUID--------------------------------D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources 2 set text='D37180AE23A4479AE04400212846C6CE' 3 where name='WF_SYSTEM_GUID' and 4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
New error this time:
SQL> @afwebprf.sql apps appspassword r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_EVENT", line 3602
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Checked the code again:
3602
wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
SQL> select text from wf_resources where name='WF_SYSTEM_STATUS' and 2 language='US';
TEXT--------------------------------------------------------------------------------CAFECAFE-0013-0001-0029-ABCDEFABCDEF
So I checked with query:
select count(*) from wf_resources where text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';1207 rows
select count(*) from wf_resources;1775 rows
So I inquired about an existing good instance. No R12 instance was available, so I copied from an 11i instance with sqlplus COPY command:
First put the tnsnames.ora entry for the good instance in the DB $TNS_ADMIN/tnsnames.ora file
Then used the sqlplus copy command, which is the fastest way to copy data between two oracle databases:
SQL> copy from apps/****@11iascp to apps/****@r12ascp create wf_resources_d1 using select * from wf_resources
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table WF_RESOURCES_11i created.
1717 rows selected from apps@usnapsd1.
1717 rows inserted into WF_RESOURCES_11i.
1717 rows committed into WF_RESOURCES_11i at apps@r12ascp.
Since there was a difference of 58 rows, I first copied those rows in a different table:
create table wf_resources_more as
( select * from wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Which created table wf_resources_more with 58 rows.
Then I delete those 58 rows from the wf_resources table:
delete wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Then I updated the table with 11i values, through a correlated update statement:
update wf_resources aset a.text=(select text from wf_resources_11i bwhere a.type||a.name = b.type||b.name)
That updated the 1717 common rows with correct values.
Then I inserted the extra 58 rows new in R12
insert into wf_resources (select * from wf_resources_more);commit;
I ran adwebprf.sql again and it gave a new error:
SQL> @afwebprf.sql apps appspass r12ascpConnected.declare*ERROR at line 1:ORA-01403: no data foundORA-06512: at "APPS.WF_EVENT", line 3604ORA-06512: at "APPS.FND_WF_ENGINE", line 1025ORA-06512: at "APPS.FND_PROFILE", line 3064ORA-06512: at "APPS.FND_PROFILE", line 3514ORA-06512: at "APPS.ADX_PRF_PKG", line 203ORA-06512: at line 167ORA-06512: at line 217
Again I checked code on line 3604 of WF_EVENT:
select name into wf_event.local_system_name
3605 from wf_systems
3606 where guid = wf_event.local_system_guid;Then I realized that after the correlated update, the wf_resources table now had the GUID of 11i system:
SQL> select name,text 2 from wf_resources 3 where name='WF_SYSTEM_GUID';
NAME------------------------------TEXT--------------------------------------------------------------------------------WF_SYSTEM_GUIDCA43363B52162863E04400212846C6CE
So I updated it again:
I checked the actual GUID:SQL> select guid from wf_systems;
GUID--------------------------------D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources 2 set text='D37180AE23A4479AE04400212846C6CE' 3 where name='WF_SYSTEM_GUID' and 4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
SQL> @afwebprf.sql apps appsapss1 usnapss1Connected.[ APPS_WEB_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004/pls/usnapss1Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_SERVLET_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004/OA_HTMLLevel Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_JSP_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_FRAMEWORK_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ WF_MAIL_WEB_AGENT ]Application Id : 0Profile Value :Level Name: SITEINFO : Updated/created profile option value....
It succeeded.
I told Akhilesh about the result and asked him to run autoconfig. We tailed the log at
$INST_TOP/admin/log/02042015/adconfig.log
Adconfig succeeded without errors this time:
AutoConfig is exiting with status 0
AutoConfig execution completed on Mon Feb 4 20:17:58 2013
Time taken for AutoConfig execution to complete : 2 mins 38 secs
I was overjoyed, and so was Akhilesh. We still had 53 rows which had the value of text as:CAFECAFE-0013-0001-0029-ABCDEFABCDEF
select count(*) from wf_resourceswhere text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';53 rows.
We would investigate further, how this had happened. I suspect human error. Human error makes troubleshooting more interesting, just like human error makes chess more interesting. If it was a computer playing chess, it would not make any mistakes and win the game. It is the human errors which make chess and troubleshooting, an interesting pursuit.
AutoConfig could not successfully execute the following scripts: Directory: /r12ascp/erpapp/10.1.3/perl/bin/perl -I /r12ascp/erpapp/10.1.3/perl/lib/5.8.3 -I /r12ascp/erpapp/10.1.3/perl/lib/site_perl/5.8.3 -I /usnapss1/erpapp/appl/au/12.0.0/perl -I /r12ascp/erpapp/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi /r12ascp/erpapp/inst/apps/usnapss1_r12ascp/admin/scripts/adexecsql.pl sqlfile=/r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install afwebprf.sql INSTE8_PRF 1 amscmprf.sql INSTE8_PRF 1 amswebprf.sql INSTE8_PRF 1 cncmprf.sql INSTE8_PRF 1 csfadmprf.sql INSTE8_PRF 1 oksfrmprf.sql INSTE8_PRF 1
Directory: /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install ibywebprf.sh INSTE8_PRF 1
If we tried running any of the above scripts, they failed with this error:
SQLPLUS Executable : /r12ascp/erpapp/10.1.2/bin/sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 4 12:04:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "APPS.WF_EVENT", line 3600
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
So I checked the code on line 3600 for wf_event package:
select line,textfrom dba_sourcewhere name='WF_EVENT'and line between 3550 and 3610;
3600
wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));I did another code search:
select line,textfrom dba_sourcewhere text like '%WF_SYSTEM_GUID%';This was the result of the query: select text into l_result from wf_resources where name='WF_SYSTEM_GUID' andlanguage='US';
SQL> select text from wf_resources where name='WF_SYSTEM_GUID' and 2 language='US';
TEXT--------------------------------------------------------------------------------CAFECAFE-0013-0001-0029-ABCDEFABCDEF
That was weird as that is the GUID used by Oracle JRE plugin 1.3.1.29.
I checked the actual GUID:SQL> select guid from wf_systems;
GUID--------------------------------D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources 2 set text='D37180AE23A4479AE04400212846C6CE' 3 where name='WF_SYSTEM_GUID' and 4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
New error this time:
SQL> @afwebprf.sql apps appspassword r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_EVENT", line 3602
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217
Checked the code again:
3602
wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');
SQL> select text from wf_resources where name='WF_SYSTEM_STATUS' and 2 language='US';
TEXT--------------------------------------------------------------------------------CAFECAFE-0013-0001-0029-ABCDEFABCDEF
So I checked with query:
select count(*) from wf_resources where text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';1207 rows
select count(*) from wf_resources;1775 rows
So I inquired about an existing good instance. No R12 instance was available, so I copied from an 11i instance with sqlplus COPY command:
First put the tnsnames.ora entry for the good instance in the DB $TNS_ADMIN/tnsnames.ora file
Then used the sqlplus copy command, which is the fastest way to copy data between two oracle databases:
SQL> copy from apps/****@11iascp to apps/****@r12ascp create wf_resources_d1 using select * from wf_resources
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table WF_RESOURCES_11i created.
1717 rows selected from apps@usnapsd1.
1717 rows inserted into WF_RESOURCES_11i.
1717 rows committed into WF_RESOURCES_11i at apps@r12ascp.
Since there was a difference of 58 rows, I first copied those rows in a different table:
create table wf_resources_more as
( select * from wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Which created table wf_resources_more with 58 rows.
Then I delete those 58 rows from the wf_resources table:
delete wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);
Then I updated the table with 11i values, through a correlated update statement:
update wf_resources aset a.text=(select text from wf_resources_11i bwhere a.type||a.name = b.type||b.name)
That updated the 1717 common rows with correct values.
Then I inserted the extra 58 rows new in R12
insert into wf_resources (select * from wf_resources_more);commit;
I ran adwebprf.sql again and it gave a new error:
SQL> @afwebprf.sql apps appspass r12ascpConnected.declare*ERROR at line 1:ORA-01403: no data foundORA-06512: at "APPS.WF_EVENT", line 3604ORA-06512: at "APPS.FND_WF_ENGINE", line 1025ORA-06512: at "APPS.FND_PROFILE", line 3064ORA-06512: at "APPS.FND_PROFILE", line 3514ORA-06512: at "APPS.ADX_PRF_PKG", line 203ORA-06512: at line 167ORA-06512: at line 217
Again I checked code on line 3604 of WF_EVENT:
select name into wf_event.local_system_name
3605 from wf_systems
3606 where guid = wf_event.local_system_guid;Then I realized that after the correlated update, the wf_resources table now had the GUID of 11i system:
SQL> select name,text 2 from wf_resources 3 where name='WF_SYSTEM_GUID';
NAME------------------------------TEXT--------------------------------------------------------------------------------WF_SYSTEM_GUIDCA43363B52162863E04400212846C6CE
So I updated it again:
I checked the actual GUID:SQL> select guid from wf_systems;
GUID--------------------------------D37180AE23A4479AE04400212846C6CE
Updated the correct GUID:
SQL> update wf_resources 2 set text='D37180AE23A4479AE04400212846C6CE' 3 where name='WF_SYSTEM_GUID' and 4 language='US';
1 row updated.
SQL> commit;
Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID
SQL> @afwebprf.sql apps appsapss1 usnapss1Connected.[ APPS_WEB_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004/pls/usnapss1Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_SERVLET_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004/OA_HTMLLevel Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_JSP_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ APPS_FRAMEWORK_AGENT ]Application Id : 0Profile Value : http://usnapss1.tsg.ge.com:8004Level Name: SITEINFO : Updated/created profile option value..Deleted : 0[ WF_MAIL_WEB_AGENT ]Application Id : 0Profile Value :Level Name: SITEINFO : Updated/created profile option value....
It succeeded.
I told Akhilesh about the result and asked him to run autoconfig. We tailed the log at
$INST_TOP/admin/log/02042015/adconfig.log
Adconfig succeeded without errors this time:
AutoConfig is exiting with status 0
AutoConfig execution completed on Mon Feb 4 20:17:58 2013
Time taken for AutoConfig execution to complete : 2 mins 38 secs
I was overjoyed, and so was Akhilesh. We still had 53 rows which had the value of text as:CAFECAFE-0013-0001-0029-ABCDEFABCDEF
select count(*) from wf_resourceswhere text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';53 rows.
We would investigate further, how this had happened. I suspect human error. Human error makes troubleshooting more interesting, just like human error makes chess more interesting. If it was a computer playing chess, it would not make any mistakes and win the game. It is the human errors which make chess and troubleshooting, an interesting pursuit.
Categories: APPS Blogs
java.sql.SQLException: ORA-01017: invalid username/password; logon denied for glogdba after cloning OTM
After cloning OTM database and instance, when we started the services and tried accessing
Configuration and Administration > Cluster Management > Scalability Overview, we got this error in glog.exception.log
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
I was able to diagnose that this was coming for glogdba user by switching on auditing on the database.
The encrypted password was correct for glogdba in glog.properties.
After a lot of R&D we found out that this was occurring because the encrypted password of glogdba user in data_source table was incorrect.
It had the encrypted password of the glogdba user in production. We reset the glogdba password to default glogdba after clone.
Doing this update statement and bouncing apache and weblogic did the trick:
update data_source set oracle_password='Z2xvZ2RiYQ=='
where data_source_gid='UNPOOLED_DBA';
commit;
Configuration and Administration > Cluster Management > Scalability Overview, we got this error in glog.exception.log
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
I was able to diagnose that this was coming for glogdba user by switching on auditing on the database.
The encrypted password was correct for glogdba in glog.properties.
After a lot of R&D we found out that this was occurring because the encrypted password of glogdba user in data_source table was incorrect.
It had the encrypted password of the glogdba user in production. We reset the glogdba password to default glogdba after clone.
Doing this update statement and bouncing apache and weblogic did the trick:
update data_source set oracle_password='Z2xvZ2RiYQ=='
where data_source_gid='UNPOOLED_DBA';
commit;
Categories: APPS Blogs
Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512
Anand pinged me today. After cloning an 11i instance, the following error was coming when AppsLocalLogin.jsp was accessed:
PRE>Unable to create anonymous session. Your session is no longer valid. Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 2109 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 369 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 538 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 217 ORA-06512: at line 1 has been detected in createSession(int, String,Hashtable)(userid=6','D3F9A80801171DD4E043447920036C9924997405341204718936747621840314').
META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+SQL_PLSQL_ERROR.+Tokens%3A+ROUTINE+%3D+createSession(int%2C+String%2CHashtable)(userid%3D6'%2C'D3F9A80801171DD4E043447920036C9924997405341204718936747621840314')%3B+REASON+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+ERRNO+%3D+20001%3B+%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A974)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A891)%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A865)%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A980)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Ajava.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%0A%09at+oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java%3A134)%0A%09at+oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java%3A289)%0A%09at+oracle.jdbc.ttc7.Oall7.receive(Oall7.java%3A590)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java%3A1973)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java%3A1119)%0A%09at+oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java%3A2191)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java%3A2064)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java%3A2989)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java%3A658)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java%3A736)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2938)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2825)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2754)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createAnonymousSession(WebAppsContext.java%3A5111)%0A%09at+oracle.apps.fnd.common.WebRequestUtil.setGuestSession(WebRequestUtil.java%3A1510)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4760)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAExcept
JSP Error
--------------------------------------------------------------------------------
Exception:
java.lang.NullPointerException
I concentrated on the first part of the error message:
ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232
I tried calling this package and function by executing this query from sqlplus:
SQL> conn apps/apps
SQL> select mo_utils.get_multi_org_flag() from dual;
select mo_utils.get_multi_org_flag() from dual
*
ERROR at line 1:
ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit
being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.
ORA-06512: at "APPS.MO_UTILS", line 27
ORA-06512: at "APPS.MO_UTILS", line 232
I checked the code in the function mo_utils.get_multi_org_flag by:
select line,text
from dba_source
where name='MO_UTILS'
and line between 220 and 240;
LINE TEXT
--------------------------------------------------------------------------------
FUNCTION Get_Multi_Org_Flag
RETURN VARCHAR2
IS
BEGIN
227
228
RETURN mo_global.is_multi_org_enabled;
229
230
EXCEPTION
231
WHEN OTHERS THEN
232
Generic_Error( 'fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG'
233
, sqlcode
234
, sqlerrm);
235
236
END Get_Multi_Org_Flag;
So the only line of code in this function is on line 237:
RETURN mo_global.is_multi_org_enabled;
I tried to execute this from sqlplus:
SQL> select mo_global.is_multi_org_enabled() from dual;
select mo_global.is_multi_org_enabled() from dual
*
ERROR at line 1:
ORA-04063: package body "APPS.MO_GLOBAL" has errors
Then I tried compiling the package body:
SQL> alter package APPS.MO_GLOBAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show errors
Errors for PACKAGE BODY APPS.MO_GLOBAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
167/27 PLS-00201: identifier 'V$SQLAREA' must be declared
167/27 PL/SQL: Item ignored
302/4 PL/SQL: Statement ignored
302/53 PLS-00320: the declaration of the type of this expression is incomplete or malformed
I checked line 167:
LINE
----------
TEXT
--------------------------------------------------------------------------------
167
l_action_name v$sqlarea.action%TYPE;
That means, it is unable to query v$sqlarea.
So I logged in as sys and
SQL> grant select on v$sqlarea to apps;
grant select on v$sqlarea to apps
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Ok, that was a mistake, the correct grant is:
SQL> grant select on v_$sqlarea to apps;
Grant succeeded.
Compiled the package MO_GLOBAL:
SQL> alter package APPS.MO_GLOBAL compile body;
Package body altered.
Tested both the sqls which were previously erroring out:
SQL> conn apps/apps
Connected.
SQL> select mo_global.is_multi_org_enabled() from dual;
MO_GLOBAL.IS_MULTI_ORG_ENABLED()
--------------------------------------------------------------------------------
Y
SQL> select mo_utils.get_multi_org_flag() from dual;
MO_UTILS.GET_MULTI_ORG_FLAG()
--------------------------------------------------------------------------------
Y
We tried with IE and the same error still came.
So we tried with a new Firefox session, and we were able to login without issues. IE also worked after clearing Temporary Internet Files.
That was an interesting troubleshoot. I wonder, how the grant was missing in the first place.
PRE>Unable to create anonymous session. Your session is no longer valid. Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 2109 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 369 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 538 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 217 ORA-06512: at line 1 has been detected in createSession(int, String,Hashtable)(userid=6','D3F9A80801171DD4E043447920036C9924997405341204718936747621840314').
META name="fwk-error-detail" content="oracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+SQL_PLSQL_ERROR.+Tokens%3A+ROUTINE+%3D+createSession(int%2C+String%2CHashtable)(userid%3D6'%2C'D3F9A80801171DD4E043447920036C9924997405341204718936747621840314')%3B+REASON+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+ERRNO+%3D+20001%3B+%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A974)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A891)%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAException.java%3A865)%0A%09at+oracle.apps.fnd.framework.server.OAExceptionUtils.processAOLJErrorStack(OAExceptionUtils.java%3A980)%0A%09at+oracle.apps.fnd.framework.OACommonUtils.processAOLJErrorStack(OACommonUtils.java%3A867)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4762)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0A%23%23+Detail+0+%23%23%0Ajava.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%0A%09at+oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java%3A134)%0A%09at+oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java%3A289)%0A%09at+oracle.jdbc.ttc7.Oall7.receive(Oall7.java%3A590)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java%3A1973)%0A%09at+oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java%3A1119)%0A%09at+oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java%3A2191)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java%3A2064)%0A%09at+oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java%3A2989)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java%3A658)%0A%09at+oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java%3A736)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2938)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2825)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createSession(WebAppsContext.java%3A2754)%0A%09at+oracle.apps.fnd.common.WebAppsContext.createAnonymousSession(WebAppsContext.java%3A5111)%0A%09at+oracle.apps.fnd.common.WebRequestUtil.setGuestSession(WebRequestUtil.java%3A1510)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.validateUser(OAPageBean.java%3A4760)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A713)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A515)%0A%09at+oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java%3A436)%0A%09at+_oa__html._OA._jspService(_OA.java%3A84)%0A%09at+oracle.jsp.runtime.HttpJsp.service(HttpJsp.java%3A119)%0A%09at+oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java%3A417)%0A%09at+oracle.jsp.JspServlet.doDispatch(JspServlet.java%3A267)%0A%09at+oracle.jsp.JspServlet.internalService(JspServlet.java%3A186)%0A%09at+oracle.jsp.JspServlet.service(JspServlet.java%3A156)%0A%09at+javax.servlet.http.HttpServlet.service(HttpServlet.java%3A588)%0A%09at+org.apache.jserv.JServConnection.processRequest(JServConnection.java%3A456)%0A%09at+org.apache.jserv.JServConnection.run(JServConnection.java%3A294)%0A%09at+java.lang.Thread.run(Thread.java%3A662)%0Aoracle.apps.fnd.framework.OAException%3A+Application%3A+FND%2C+Message+Name%3A+FND_GENERIC_MESSAGE.+Tokens%3A+MESSAGE+%3D+java.sql.SQLException%3A+ORA-20001%3A+Oracle+error+-6508%3A+ORA-06508%3A+PL%2FSQL%3A+could+not+find+program+unit+being+called+has+been+detected+in+fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+27%0AORA-06512%3A+at+%22APPS.MO_UTILS%22%2C+line+232%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+2109%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+369%0AORA-06512%3A+at+%22APPS.FND_SESSION_MANAGEMENT%22%2C+line+538%0AORA-06512%3A+at+%22APPS.FND_AOLJ_UTIL%22%2C+line+217%0AORA-06512%3A+at+line+1%0A%3B+%0A%09at+oracle.apps.fnd.framework.OAException.wrapperException(OAExcept
JSP Error
--------------------------------------------------------------------------------
Exception:
java.lang.NullPointerException
I concentrated on the first part of the error message:
ORA-06508: PL/SQL: could not find program unit being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG. ORA-06512: at "APPS.MO_UTILS", line 27 ORA-06512: at "APPS.MO_UTILS", line 232
I tried calling this package and function by executing this query from sqlplus:
SQL> conn apps/apps
SQL> select mo_utils.get_multi_org_flag() from dual;
select mo_utils.get_multi_org_flag() from dual
*
ERROR at line 1:
ORA-20001: Oracle error -6508: ORA-06508: PL/SQL: could not find program unit
being called has been detected in fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG.
ORA-06512: at "APPS.MO_UTILS", line 27
ORA-06512: at "APPS.MO_UTILS", line 232
I checked the code in the function mo_utils.get_multi_org_flag by:
select line,text
from dba_source
where name='MO_UTILS'
and line between 220 and 240;
LINE TEXT
--------------------------------------------------------------------------------
FUNCTION Get_Multi_Org_Flag
RETURN VARCHAR2
IS
BEGIN
227
228
RETURN mo_global.is_multi_org_enabled;
229
230
EXCEPTION
231
WHEN OTHERS THEN
232
Generic_Error( 'fnd.plsql.MO_UTILS.GET_MULTI_ORG_FLAG'
233
, sqlcode
234
, sqlerrm);
235
236
END Get_Multi_Org_Flag;
So the only line of code in this function is on line 237:
RETURN mo_global.is_multi_org_enabled;
I tried to execute this from sqlplus:
SQL> select mo_global.is_multi_org_enabled() from dual;
select mo_global.is_multi_org_enabled() from dual
*
ERROR at line 1:
ORA-04063: package body "APPS.MO_GLOBAL" has errors
Then I tried compiling the package body:
SQL> alter package APPS.MO_GLOBAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show errors
Errors for PACKAGE BODY APPS.MO_GLOBAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
167/27 PLS-00201: identifier 'V$SQLAREA' must be declared
167/27 PL/SQL: Item ignored
302/4 PL/SQL: Statement ignored
302/53 PLS-00320: the declaration of the type of this expression is incomplete or malformed
I checked line 167:
LINE
----------
TEXT
--------------------------------------------------------------------------------
167
l_action_name v$sqlarea.action%TYPE;
That means, it is unable to query v$sqlarea.
So I logged in as sys and
SQL> grant select on v$sqlarea to apps;
grant select on v$sqlarea to apps
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Ok, that was a mistake, the correct grant is:
SQL> grant select on v_$sqlarea to apps;
Grant succeeded.
Compiled the package MO_GLOBAL:
SQL> alter package APPS.MO_GLOBAL compile body;
Package body altered.
Tested both the sqls which were previously erroring out:
SQL> conn apps/apps
Connected.
SQL> select mo_global.is_multi_org_enabled() from dual;
MO_GLOBAL.IS_MULTI_ORG_ENABLED()
--------------------------------------------------------------------------------
Y
SQL> select mo_utils.get_multi_org_flag() from dual;
MO_UTILS.GET_MULTI_ORG_FLAG()
--------------------------------------------------------------------------------
Y
We tried with IE and the same error still came.
So we tried with a new Firefox session, and we were able to login without issues. IE also worked after clearing Temporary Internet Files.
That was an interesting troubleshoot. I wonder, how the grant was missing in the first place.
Categories: APPS Blogs