Category Archives: AOL SQL Scripts

PL/SQL Script to Delete Lookup or Lookup Code

/******************************************** *PURPOSE: To Delete Lookup or Lookup Code * *AUTHOR: Shailender Thallam * *********************************************/ SET serveroutput ON; —   DECLARE CURSOR cur_lkp_values IS SELECT lookup_type, lookup_code, security_group_id, view_application_id FROM fnd_lookup_values WHERE lookup_type = ‘XX_LKP_NAME’ AND lookup_code LIKE ‘XX_LKP_CODE’; BEGIN FOR i IN cur_lkp_values LOOP BEGIN fnd_lookup_values_pkg.delete_row (x_lookup_type => i.lookup_type, x_security_group_id => i.security_group_id, x_view_application_id => i.view_application_id,… Read More »

PL/SQL Script to Reset User Password from Backend

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 /************************************************************ *PURPOSE: To change/reset password of a user from backend * *AUTHOR: Shailender Thallam * *************************************************************/ SET serveroutput ON; DECLARE v_user_name VARCHAR2(30):= UPPER(’SYSADMIN’); v_new_password VARCHAR2(30):= ‘welcome1’; v_status BOOLEAN; BEGIN v_status := fnd_user_pkg.ChangePassword ( username… Read More »

Script to Delete Concurrent Program and Executable from backend

/******************************************************************************** *PURPOSE: To delete concurrent program definition and executable from back-end * *AUTHOR: Shailender Thallam * *********************************************************************************/ SET SERVEROUTPUT ON;   DECLARE l_prog_short_name VARCHAR2 (240); l_exec_short_name VARCHAR2 (240); l_appl_full_name VARCHAR2 (240); l_appl_short_name VARCHAR2 (240); l_del_prog_flag VARCHAR2 (1) := ‘Y’; –Set flag whether to delete Concurrent program or not l_del_exec_flag VARCHAR2 (1) := ‘Y’; –Set flag… Read More »

SQL Query to list all DB Links in a database

/********************************************************* *PURPOSE: Query to list all DB Links in a database * *AUTHOR: Shailender Thallam * *********************************************************/ SELECT obj.object_type "Object Type", obj.owner "Object Owner", obj.object_name "Object Name", obj.status "Object Status", dbl.db_link "DB Link", dbl.username "DB User Name", dbl.host "DB Host" FROM dba_objects obj, dba_db_links dbl WHERE obj.object_name = dbl.db_link AND obj.object_type = ‘DATABASE LINK’;/********************************************************* *PURPOSE:… Read More »

How to make a Concurrent Program WAIT untill completion of another Concurrent Program execution?

While working on interfaces we face some requirements like submitting a concurrent program sequentially one after the other. In some scenarios we need to submit 2nd concurrent program only when 1st current program is successfully completed. In such scenarios we need to wait for completion of the 1st program execution and then decide whether we… Read More »

SQL Query to findout Profile Option Values

/********************************************************* *PURPOSE: To find out profile option Values * *AUTHOR: Shailender Thallam * **********************************************************/ SELECT DISTINCT POT.PROFILE_OPTION_NAME "PROFILE_CODE" , POT.USER_PROFILE_OPTION_NAME "PROFILE_NAME" , DECODE (a.profile_option_value , ‘1’, ‘1 (may be "Yes")’ , ‘2’, ‘2 (may be "No")’ , a.profile_option_value ) "PF_VALUE" , DECODE (a.level_id , 10001, ‘Site’ , 10002, ‘Application’ , 10003, ‘Responsibility’ , 10004, ‘User’… Read More »

PL/SQL script to find and Kill Blocking Sessions/Objects

/********************************************************* *PURPOSE: To findout blocking sessions and kill them * *AUTHOR: Shailender Thallam * **********************************************************/ — DECLARE CURSOR c IS SELECT c.owner, c.object_name, c.object_type, b.SID , b.serial# , b.status , b.osuser , b.machine FROM v$locked_object a, v$session b , dba_objects c WHERE b.SID = a.session_id AND a.object_id = c.object_id –AND b.osuser = ‘shailender.thallam’ –and c.object_name… Read More »