Control sort in union all

If you have a union all select statement and you want to have the results from the first select statement first and second statement second, etc..... here what you can do. let's say we are getting the employee names from different tables select employee_name from company1 union all select employee_name from company2 union all select employee_name from company3 union all select employee_name from company4 union all select employee_name from company5 order by 1; In this select statement all employee will be mixed from all companies sorted by name, but what if you want to have the name of the employee from company1 first and then from company2 and then from company3 etc..... Here what you can do select 'A',employee_name from company1 union all select 'B',employee_name from company2 union all select 'C',employee_name from company3 union all select 'D', employee_name from company4 union all select 'E', employee_name from company5 order by 1,2; This way the employee will be sorted by name from company1 first and then second, etc.... If you have the select statement in SQL*Plus and you don't want to show this extra column here what you can do: column sort1 noprint --- this will hide column sort1 select 'A' sort1,employee_name from company1 union all select 'B' sort1,employee_name from company2 union all select 'C' sort1,employee_name from company3 union all select 'D' sort1, employee_name from company4 union all select 'E' sort1, employee_name from company5 order by 1,2;

"The WIP work order associated with this transaction is currently locked and being updated by another user"

One of the thing that causes this Lock is because "Inventory Manager" and "WIP Mass Load" are updating the same job. If you are submitting online IM (mtl_online_transaction_pub.process_online) to process material transactions inside your code and you got the lock message, you can solve it by checking the running concurrent request, if WIP Mass Load is one of them you keep looping till it's complete then you can run mtl_online_transaction_pub.process_online declare v_program varchar2(2000); -------------get all running concurrent requests cursor c4 is select substr(fcrv.program,1,40) program from apps.fnd_conc_req_summary_v fcrv where fcrv.phase_code = 'R' and fcrv.program like 'WIP Mass Load (WIP Mass Load)'; begin while 1 = 1 loop v_program := null; open c4; fetch c4 into v_program; close c4; if(v_program is null) then exit; end if; end loop; end; ------------------after that you can call mtl_online_transaction_pub.process_online

Ask your code to wait

If for some reason you want for your code to wait for certain period of time, you can use dbms_lock.sleep(100); , in order to test this you can use the code below, from the test table you can see how much the code waited between the two insert statements. create table test (date1 date); begin insert into test values (sysdate); dbms_lock.sleep(100); insert into test values (sysdate); commit; end; select * from test;

Sorting dates using only month and year

In PL/SQL in order to show only the month and year of a date, you need to convert the date to char, once you do that the order by get messed up because it won't recognize the field as a date anymore so SEP-2006 will come before OCT-2016 if you are using order by desc. To solve this issue you can do the following select DISTINCT to_char(x_date, 'MON-YYYY') DISPLAY, to_char(x_date, 'YYYY-MM') HIDDEN order by to_char(x_date, 'YYYY-MM') DESC;