Category Archives: AOL SQL Scripts

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 »

PL/SQL Script to Add a Concurrent Program to a Request Group from backend

We can not only create a concurrent program from backend but also assign the concurrent program to a Request Group by FND_PROGRAM.ADD_TO_GROUP api, below is the sample script. Adding a Concurrent Program to Request Group from backend: /********************************************************* *PURPOSE: To Add a Concurrent Program to a Request Group * * from backend * *AUTHOR: Shailender… Read More »