PL/SQL Script to read CLOB(XML Data) which is more than 32kb size

Here in this article I will be writing how to read a CLOB file using DBMS_LOB which is more than 32kb size. The below example is related to dunning letters where a seeded concurrent program IEX: Bulk XML Delivery Manager generates dunning letters data in XML form and stored it in table IEX_XML_REQUEST_HISTORIES in CLOB… Read More »

Frequently used Oracle SQL Developer Short Cut Keys

Short Cut Description Ctrl+Space Invokes code insight on demand Ctrl+Up Replaces worksheet with Previous SQL from SQL History Ctrl+Down Replaces worksheet with Next SQL from SQL History Ctrl+Shift+Up Appends current worksheet with Previous SQL from SQL History Ctrl+Shift+Down Appends current worksheet with Next SQL from SQL History Dhift+F4 opens a Describe window for current object… Read More »

Oracle HRMS SQL Query to find out Employee and their Supervisor Hierarchy

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 SELECT LEVEL, e.* FROM (SELECT DISTINCT papf.person_id, papf.employee_number, papf.full_name "EMPLOYEE_FULL_NAME", paaf.supervisor_id, papf1.employee_number "SUPERVISOR_EMP_NUMBER", papf1.full_name "SUPERVISOR_FULL_NAME" FROM apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.per_all_people_f papf1, apps.per_person_types ppt WHERE papf.person_id = paaf.person_id AND papf1.person_id =… Read More »

Sales Orders not getting Invoiced

If Invoice not interfaced 1. Order Type workflow assignments to check if there is an invoicing node. 2. From Order –> Lines go to Tools –> Workflow Status, check if the Invoice Interface is complete as shown in the attachment. Lines stuck in Interface Trip Stop? Check in Shipping–> Interfaces –> Run and select ITS… Read More »

What is TKPROF? How to run TKPROF?

Trace file is not in a understandable format, but to make it easier to understand and readable Oracle has provided a utility TKPROF. Syntaxt to run TKPROF TKPROF <trace-file> <output-file> [options]TKPROF <trace-file> <output-file> [options] Note: Its mandatory to set environment before running TKPROF. TKPROF Example TKPROF PerfIssue.trc PerfIssue.prf — –Another way with options — TKPROF… 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 »