ORA-06512: at "CLARITY.CMN_SEC_USER_INST_PRIVS_FCT", line 19

[Terry Becker]

Hi, had something appear yesterday and I can't figure out what caused it to state throwing the error.

Looking in my app-niku.log file, I see the following message.

ERROR 2010-07-21 09:07:27,657 [[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'] niku.security (GVJ6965:7765577__-1abc9bc6:revmgr.costplanList) SecurityControllerImpl.loadInstancePermissions:PMD error
com.niku.union.persistence.PersistenceException:
SQL error code: 6502
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CLARITY.CMN_SEC_USER_INST_PRIVS_FCT", line 19

Executed:
select
object_id, permission_code
from

table(cmn_sec_user_inst_privs_fct( ?, ? )) s
Derived from statement:
<?xml version="1.0" encoding="UTF-8"?>
<statement location="securityPMD.xml" id="security.getUserInstancePerms_stmt" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="none" xmlns="http://schemas.niku.com/2002/pmd">
<sql dbVendor="oracle">
<text> select object_id, permission_code from table(cmn_sec_user_inst_privs_fct( ?, ? )) s </text>
<param name="userId" type="long" direction="IN" expressionListDelimiter=","/>
<param name="objectInstanceId" type="long" direction="IN" expressionListDelimiter=","/>
</sql>
<sql dbVendor="mssql">
<text> select object_id, permission_code from @DBUSER@.cmn_sec_user_inst_privs_fct( ?, ? ) </text>
<param name="userId" type="long" direction="IN" expressionListDelimiter=","/>
<param name="objectInstanceId" type="long" direction="IN" expressionListDelimiter=","/>
</sql>
</statement>

Referenced by:
<?xml version="1.0" encoding="UTF-8"?>
<statementRef id="security.getUserInstancePerms_stmt" inputSource="map" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" xmlns="http://schemas.niku.com/2002/pmd"/>

Using input:
{objectInstanceId=5000291, userId=5005667}


When I research this, I find the error is happening in the function cmn_sec_user_inst_privs_fct . Line 19 is ponting to the permission_code. This value is defined as Char(30) package cmn_security_pkg. The values being returned in the page are 39 and 35 characters.

PropertyViewAccessEditproject.hum_track is 39 Characters....
PropertyViewAccessproject.hum_track is 35 Characters

What is odd, it seems like the only thing we control is the subpage names (project.hum_track) which aren't that long. Is this something that anyone has come across?

[Anon Anon]

Hi

Can you try and increase the permission_code value from Char(30) to Char(110), and then check if you get the same issue?

Regards
NJ

[Terry Becker]

Hi NJ, unfortunately, this is in our production environment and I am not able to update database objects without approval. You are on the right track, so I might give it a try in our DEV environment. I'll let you know.

Thanks for the suggestion.

Terry

[Anon Anon]

Dragging-up an old thread, but I have just noticed I have these errors too in my production system (8.1 fp3).

I found the "fix" TEC article too though, here; TEC478269

(the fix being what NJ suggests apart from in a 'supported' manner :tongue )

In my instance the problem appears to have only started occurring when I granted a load of secure-subpage rights in my system (based on the logic that the permission_codes that I appear to have that are >30 characters in length are to do with secure subpages)