ORACLE ERP & APPS TECH

As part of performance and run time of a query or a function we might need to capture time in milli/micro/ nano seconds. Below snippets would help in such scenarios: By default  localtimestamp will give upto 6 decimal places of a sec. to get nano sec upto 9 decimal use  localtimestamp(9) declare l_starttime timestamp(9); l_endtime timestamp(9); l_count number:=0; l_Diff number:=0; begin select localtimestamp(9) into l_starttime from dual;  dbms_output.put_line('Start Time: ' || l_starttime);   select count(*) into l_count from PO_line_locations_all pll, po_headers_all PHA where pll.po_header_id = pha.po_header_id and exists (select 1 from po_requisition_lines_all prl,po_requisition_headers_all prh               where prl.requisition_header_id = prh.requisition_header_id                 and prl.blanket_po_header_id = pha.po_header_id) and pll.po_header_id = <enter header id>; se...

Query to get comma separated values into each row SELECT TRIM(regexp_substr(:l_comma_code,'[^,]+', 1, level)) Comma_col_value                            FROM dual                      CONNECT BY regexp_substr(:l_comma_code,'[^,]+', 1, level) is not null; =>Query to get all dates in a month for a given date:     SELECT  TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 AS day_in_month     FROM  dual CONNECT BY      TRUNC(TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'MM') + LEVEL - 1 <= LAST_DAY(TO_DATE('2024-01-01', 'YYYY-MM-DD')); =>SQL to get ISO week and month for date: select TO_CHAR(SYSDATE, 'IW') AS wk_Bucket, TO_CHAR(SYSDATE, 'MM') AS MN_Bucket  FROM DUAL; =>SQL query to get last ISO week for a given month and year SELECT TO_CHAR(LAST_DAY(TO_DATE(:month_num || '-' || :year_num, 'MM-YYYY')), 'I...