Package Getting Hung While Compiling

get the locked objects:

SELECT s.sid,

      s.serial#,

       l.lock_type,

       l.mode_held,

       l.mode_requested,

       l.lock_id1

    FROM   dba_lock_internal l,

       v$session s

    WHERE s.sid = l.session_id

    AND UPPER(l.lock_id1) LIKE '%PKG_NAME%'

    AND l.lock_type = 'Body Definition Lock';

Kill the sessions:

ALTER SYSTEM KILL SESSION 'sid,serial#' ;

Oracle Objects Being Accessed by Programs/Users

Objects Being Accessed:

select client_identifier,program,state,object,module, action,s.* 

from gv$session s , gv$access a 

where status='ACTIVE'

and a.sid = s.sid and a.inst_id = s.inst_id
and object in ('Object_name');

Objects are Locked by DDL:

select * from
DBA_DDL_LOCKS
where name = :name_object;

DB Lock in the Oracle Objects

 SELECT object_name, machine, s.sid, s.serial#, s.*

FROM gv$locked_object l, dba_objects o, gv$session s  

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;

Release SESSION SQL:  
--alter system kill session 'sid, serial#';  
ALTER system kill session '23, 1647';

Flexfields server-side validation package report error. virtual_value_set() exception : ORA-06502: PLSQL numeric or value error: number precision too large column#x

First of all we need to understand that this issue is coming from a seeded package. i.e. fnd_flex_server1

In this package you can search with the procedure name you are seeing in the error message to confirm where the error is coming from.

The issue might be due to either KFF or DFF as the same package is being used for both the flex fields.

In my case the below query was returning the record and it was failing while iterating the data from the cursor.

You need to pass table_id, application id, flex field name, context to get the records using the cursor.

Query:

SELECT g.end_user_column_name, g.application_column_name,

              c.column_type, c.width, g.required_flag,

              g.security_enabled_flag, g.concatenation_description_len,

              g.default_type, g.default_value, g.flex_value_set_id,

              g.runtime_property_function,

              NULL

       FROM fnd_descr_flex_column_usages g, fnd_columns c

      WHERE g.application_id = descstruct.application_id

        AND g.descriptive_flexfield_name = descstruct.desc_flex_name

        AND g.descriptive_flex_context_code = descstruct.desc_flex_context

        AND g.enabled_flag = 'Y'

        AND c.application_id = t_apid

        AND c.table_id = t_id

        AND c.column_name = g.application_column_name

      ORDER BY g.column_seq_num;

 

Check Rollback Blocks Post Concurrent Request is Cancelled

  SELECT inst_id,

         usn,

         state,

         undoblockstotal                     "Total",

         undoblocksdone                      "Done",

         undoblockstotal - undoblocksdone    "ToDo",

         inst_id,

         xid,

         rcvservers,

         DECODE (

             cputime,

             0, 'unknown',

               SYSDATE

             + (  (  (undoblockstotal - undoblocksdone)

                   / (undoblocksdone / DECODE (cputime, 0, 1)))

                / 86400))                    "Estimated time to complete"

    FROM gv$fast_start_transactions

   WHERE state != 'RECOVERED'

ORDER BY 1, 2;

End Date a Responsibility For an FND User(Backend Script)

 DECLARE

    v_user_name             VARCHAR2 (100) := 'ROHAN_RAJPUT';

    v_responsibility_name   VARCHAR2 (100) := 'AR XX Superuser';  -- Resp name

    v_application_name      VARCHAR2 (100) := NULL;

    v_responsibility_key    VARCHAR2 (100) := NULL;

    v_security_group        VARCHAR2 (100) := NULL;

BEGIN

    SELECT fa.application_short_name,

           fr.responsibility_key,

           frg.security_group_key

      INTO v_application_name, v_responsibility_key, v_security_group

      FROM fnd_responsibility     fr,

           fnd_application        fa,

           fnd_security_groups    frg,

           fnd_responsibility_tl  frt

     WHERE     fr.application_id = fa.application_id

           AND fr.data_group_id = frg.security_group_id

           AND fr.responsibility_id = frt.responsibility_id

           AND frt.LANGUAGE = USERENV ('LANG')

           AND frt.responsibility_name = v_responsibility_name;

    fnd_user_pkg.delresp (username         => v_user_name,

                          resp_app         => v_application_name,

                          resp_key         => v_responsibility_key,

                          security_group   => v_security_group);

    COMMIT;

    DBMS_OUTPUT.put_line (

           'Responsiblity '

        || v_responsibility_name

        || ' is removed from the user '

        || v_user_name

        || ' Successfully');

EXCEPTION

    WHEN OTHERS

    THEN

        DBMS_OUTPUT.put_line (

               'Error encountered while deleting responsibilty from the user and the error is '

            || SQLERRM);

END;

/

ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "package name" has been invalidated

Ideally this error comes when the package in question is Invalid but sometime we see this error even if our package is Valid.

Instead of getting into too much details and debugging just request your DBAs to recompile the package or do it yourself if you are a confident developer with apps access ;) 

To compile both of sepc and body :-

                                Alter package pkg_name compile;

To compile only Body :-

                                Alter package pkg_name compile body;

If its recompiled without any error then just retest.

That's it.