Tuesday, March 27, 2012

Start Configuration from Jdeveloper

I have seen everyone working on Configurator Extensions wondering if we can start Configuration session from local machine Jdeveloper. Well, the answer is Yes. You can! I have done it in past.

Follow these steps:
  • Install Jdeveloper.
  • You need is DBC file and put it in local machine directory. Refer DBC file path in your java program in System properties - DBCFILE and JTFDBCFILE.
  • Refer Configurator Extension Guide to create configuration using CIO from Java program.
  • Get all the classes from middletier required by Configurator (fnd classes). You can start with none and as you start getting error, you can create jar for specific packages like oracle.apps.fnd and put it in your jdev. If you want to know class location on server, you can use CZInfo.jsp file. More info on CZInfo.jsp file is here.

Its very useful for debugging purpose, but its very very slow is what I experienced - depending on your model size and how your db responds to your machine. It actually executes plenty of queries (to get model/rule details from database) to create configuration on local machine and load all those classes which makes it very slow.

Using this you can test/debug-step through your Extension code.

Saturday, March 24, 2012

Oracle: Find locking session and Kill

Usually you will see some query taking a lot of time and sometime never returns. Well, if it returns ever, you nee d to improve the explain plan of the query.
But if it never returns (usually update query) then it could be a deadlock in the system somewhere. To find out that particular session and query, following query will give more fruitful information for it.

I found if useful for debugging few issues in Oracle Configurator when some page does not return on Apply button click. Well, but in general following queries are not specific to oracle applications, you can use it anywhere to find locking sessions and eventually kill them.

SELECT sq.*, ses.*
FROM   v$sql sq, v$session ses
WHERE  sq.sql_id = ses.sql_id
 and ses.sid IN (SELECT sid
            FROM   v$lock
            WHERE  (id1,id2,TYPE) IN (SELECT id1,
                                             id2,
                                             TYPE
                                         FROM   v$lock
                                         WHERE  request > 0))
;

Find "BLOCKING_SESSION", analyze and kill if required.

select * from v$session where sid=310;


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


ALTER SYSTEM KILL SESSION '276,2323' IMMEDIATE;

Wednesday, March 7, 2012

Assign different property to node and keep the value as it is


Although same can be done by updating property name directly if the old property is not required at all.


Backup table created as
CREATE TABLE CZ_PS_PROP_VALS_BKUP_6MAR12 AS SELECT * FROM CZ_PS_PROP_vALS;


Current count of this property usage of our models in RMT%In%Process folder

SELECT count(*), P.NAME
FROM cz_ps_prop_vals V, CZ_PROPERTIES P
WHERE P.deleted_flag=0
AND V.DELETED_FLAG=0
AND V.PROPERTY_ID = P.PROPERTY_ID
AND P.name like '%OFFERING%'
AND V.ps_node_id   IN
  (SELECT ps_node_id
  FROM cz_ps_nodes
  WHERE deleted_flag=0
  and devl_project_id IN
    (SELECT object_id
    FROM cz_rp_entries
    WHERE enclosing_folder = 136906
    AND deleted_flag       =0
    )
    )
  GROUP BY P.NAME;



Update query executed to update 394 rows
UPDATE CZ_PS_PROP_VALS
SET PROPERTY_ID = 5302 -- MYOFFERING
WHERE PROPERTY_ID = 5522 --YOUROFFERING
AND DELETED_FLAG=0
AND PS_NODE_ID IN (SELECT ps_node_id
  FROM cz_ps_nodes
  WHERE deleted_flag=0
  and devl_project_id IN
    (SELECT object_id
    FROM cz_rp_entries
    WHERE enclosing_folder = 136906
    AND deleted_flag       =0
    )
    );