Oracle FOR ALL Insert Performance Testing Using Sample code

/* Timer utility */

CREATE OR REPLACE PACKAGE sf_timer
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL);
END sf_timer;
/

CREATE OR REPLACE PACKAGE BODY sf_timer
IS
   /* Package variable which stores the last timing made */
   last_timing   NUMBER := NULL;

   PROCEDURE start_timer
   IS
   BEGIN
      last_timing := DBMS_UTILITY.get_cpu_time;
   END;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            '"'
         || message_in
         || '" completed in: '
         || (DBMS_UTILITY.get_cpu_time - last_timing) / 100
         || ' seconds');

      start_timer;
   END;
END sf_timer;
/

CREATE TABLE parts
(
   partnum    NUMBER,
   partname   VARCHAR2 (15)
)
/

CREATE TABLE parts2
(
   partnum    NUMBER,
   partname   VARCHAR2 (15)
)
/

DROP TYPE parts_ot FORCE
/

CREATE OR REPLACE TYPE parts_ot IS OBJECT
(
   partnum NUMBER,
   partname VARCHAR2 (15)
)
/

CREATE OR REPLACE TYPE partstab IS TABLE OF parts_ot;
/

DECLARE
   PROCEDURE compare_inserting (num IN INTEGER)
   IS
      TYPE numtab IS TABLE OF parts.partnum%TYPE;

      TYPE nametab IS TABLE OF parts.partname%TYPE;

      TYPE parts_t IS TABLE OF parts%ROWTYPE
         INDEX BY PLS_INTEGER;

      parts_tab   parts_t;

      pnums       numtab := numtab ();
      pnames      nametab := nametab ();
      parts_nt    partstab := partstab ();
   BEGIN
      pnums.EXTEND (num);
      pnames.EXTEND (num);
      parts_nt.EXTEND (num);

      FOR indx IN 1 .. num
      LOOP
         pnums (indx) := indx;
         pnames (indx) := 'Part ' || TO_CHAR (indx);
         parts_nt (indx) := parts_ot (NULL, NULL);
         parts_nt (indx).partnum := indx;
         parts_nt (indx).partname := pnames (indx);
      END LOOP;

      sf_timer.start_timer;

      FOR indx IN 1 .. num
      LOOP
         INSERT INTO parts
              VALUES (pnums (indx), pnames (indx));
      END LOOP;

      sf_timer.show_elapsed_time (
         'FOR loop (row by row)' || num);

      ROLLBACK;

      sf_timer.start_timer;

      FORALL indx IN 1 .. num
         INSERT INTO parts
              VALUES (pnums (indx), pnames (indx));

      sf_timer.show_elapsed_time ('FORALL (bulk)' || num);

      ROLLBACK;

      sf_timer.start_timer;

      INSERT INTO parts
         SELECT * FROM TABLE (parts_nt);

      sf_timer.show_elapsed_time (
         'Insert Select from nested table ' || num);

      ROLLBACK;

      sf_timer.start_timer;

      INSERT /*+ APPEND */
            INTO  parts
         SELECT * FROM TABLE (parts_nt);

      sf_timer.show_elapsed_time (
         'Insert Select WITH DIRECT PATH ' || num);

      ROLLBACK;

      EXECUTE IMMEDIATE 'TRUNCATE TABLE parts';

      /* Load up the table. */
      FOR indx IN 1 .. num
      LOOP
         INSERT INTO parts
              VALUES (indx, 'Part ' || TO_CHAR (indx));
      END LOOP;

      COMMIT;

      DBMS_SESSION.free_unused_user_memory;

      sf_timer.start_timer;

      INSERT INTO parts2
         SELECT * FROM parts;

      sf_timer.show_elapsed_time ('Insert Select 100% SQL');

      EXECUTE IMMEDIATE 'TRUNCATE TABLE parts2';

      DBMS_SESSION.free_unused_user_memory;

      sf_timer.start_timer;

      SELECT *
        BULK COLLECT INTO parts_tab
        FROM parts;

      FORALL indx IN parts_tab.FIRST .. parts_tab.LAST
         INSERT INTO parts2
              VALUES parts_tab (indx);

      sf_timer.show_elapsed_time ('BULK COLLECT - FORALL');
   END;
BEGIN
   compare_inserting (100000);
END;
/

DROP TABLE parts
/

DROP TABLE parts2
/

DROP PACKAGE sf_timer
/

Query to find suppliers with zip codes other than numeric and dash characters

  SELECT pv.vendor_name,
         pv.segment1 vendor_number,
         pvs.vendor_site_code,
         pvs.ADDRESS_LINE1,
         pvs.ADDRESS_LINE2,
         pvs.ADDRESS_LINE3,
         pvs.state,
         pvs.City,
         pvs.zip,
         pvs.country,
         hou.NAME OU
    FROM apps.po_vendors pv,
         apps.po_vendor_sites_all pvs,
         apps.hr_operating_units hou
   WHERE     pv.vendor_id = pvs.vendor_id
         AND pvs.org_id = hou.organization_id
         AND pv.ENABLED_FLAG = 'Y'
         AND NVL (pv.END_DATE_ACTIVE, SYSDATE + 1) >= SYSDATE
         AND NVL (pvs.INACTIVE_DATE, SYSDATE + 1) >= SYSDATE
         AND pvs.country = 'US'
         AND TRANSLATE (zip,
                        CHR (0) || '0123456789-' || CHR (9),
                        CHR (0))
                IS NOT NULL
ORDER BY pvs.org_id, pv.vendor_name

Discoverer Report Last Run By Stats Query

SELECT qs_doc_name disc_rpt_name,
         qs_doc_owner disc_rpt_owner,
         (SELECT user_name
            FROM fnd_user
           WHERE '#' || user_id = qs_created_by)
            disc_rpt_run_by,
         qs_created_date disc_rpt_run_date
    FROM disprd.eul5_qpp_stats
   WHERE qs_doc_name = 'XX Disc Report Name'
ORDER BY qs_created_date DESC

Validated Unpaid Invoices in Oracle AP

  SELECT hou.name OU_Name,

         i.invoice_num,

         v.vendor_name supplier_name,

         i.invoice_date,

         ps.due_date,

         i.amount_paid,

         --i.invoice_amount,ps.amount_remaining,

         SUM (i.invoice_amount) invoice_amount,

         SUM (ps.amount_remaining) amount_remaining

    FROM apps.ap_payment_schedules_all ps,

         apps.ap_invoices_all i,

         apps.po_vendors v,

         apps.po_vendor_sites_all vs,

         apps.hr_operating_units hou

   WHERE     i.invoice_id = ps.invoice_id

         AND i.vendor_id = v.vendor_id

         AND i.vendor_site_id = vs.vendor_site_id

         AND i.payment_status_flag = 'N'

         AND (NVL (ps.amount_remaining, 0) * NVL (i.exchange_rate, 1)) != 0

         AND DECODE (APPS.Ap_Invoices_Pkg.GET_APPROVAL_STATUS (

                        i.INVOICE_ID,

                        i.INVOICE_AMOUNT,

                        i.PAYMENT_STATUS_FLAG,

                        i.INVOICE_TYPE_LOOKUP_CODE),

                     'NEVER APPROVED', 'Never Validated',

                     'NEEDS REAPPROVAL', 'Needs Revalidation',

                     'Validated') = 'Validated'

         AND i.org_id = hou.organization_id

         AND ps.due_date > SYSDATE

GROUP BY hou.name,

         v.vendor_name,

         i.invoice_num,

         i.invoice_date,

         ps.due_date,

         i.invoice_amount,

         i.amount_paid,

         ps.amount_remaining

ORDER BY v.vendor_name, i.invoice_num;

Oracle Query to Replace Special Characters from a string

SELECT REGEXP_REPLACE('##$!%*~``$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual

Oracle Queries/Commands to Kill Session

select * from v$access 

where object = 'XX_PACKAGE_NAME';

/* Pick the sids from above query and pass to the below query */

select * from v$session

where sid in (322,368);

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

ALTER SYSTEM KILL SESSION '322,48848';

Query to Find Oracle Concurrent Request Trace File Location

/* In the below query pass request id value to bind variable &request_id*/
SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request_id
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id