Basic PLSQL Reference

Submitted by code_admin on Wed, 07/11/2018 - 18:36

Combining Text Rows together

  1. CREATE OR REPLACE TYPE Robert AS TABLE OF VARCHAR2(4000);
  2.    SELECT       last_name,
  3.                         CAST(COLLECT(employee_number) as Robert) as Emp_Num
  4.          FROM    per_all_people_f
  5.          where   business_group_id = 101
  6.            and     sysdate between effective_start_date and effective_end_date
  7.            and     employee_number is not null
  8.           and      last_name = 'Abdi'

Another Alternative

  1. SELECT last_name,
  2.        count(*),
  3.        LTRIM(MAX(SYS_CONNECT_BY_PATH(employee_number,','))
  4.        KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
  5. FROM   (SELECT last_name,
  6.                employee_number,
  7.                ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY employee_number) AS curr,
  8.                ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY employee_number) -1 AS prev
  9.          FROM  per_all_people_f
  10.          where business_group_id = 101
  11.            and sysdate between effective_start_date and effective_end_date
  12.            and employee_number is not null
  13.          --AND LAST_NAME = 'Abdi'
  14.          )
  15. GROUP BY last_name
  16. CONNECT BY prev = PRIOR curr AND last_name = PRIOR last_name
  17. START WITH curr = 1

Find if string is numeric

  1. if nvl(LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))),-1)=-1 then
  2.    c := to_number(string1);  
  3. end if;

Commit every X

The following code will execute a commit every 500 rows. (if l_num_items is the row counter)

  1. --commit every x
  2. if mod(l_num_items,500)=0 then
  3.      commit;
  4. end if;

While Loop which times out

  1. set serveroutput on
  2. declare
  3.   l_startTime TIMESTAMP;
  4.   l_timeElapsed INTERVAL DAY(3) TO SECOND(3);
  5.   l_intervalToWait INTERVAL DAY(3) TO SECOND(3) := INTERVAL '6' SECOND;
  6. begin
  7.   dbms_output.put_line('Start');
  8.  
  9.   l_startTime := SYSTIMESTAMP;
  10.   dbms_output.put_line('Set start time to: ' || to_char(l_startTime));
  11.  
  12.   while true
  13.   LOOP
  14.     l_timeElapsed := SYSTIMESTAMP - l_startTime;
  15.    
  16.     if l_timeElapsed>l_intervalToWait then
  17.       exit;
  18.     end if;
  19.   END LOOP;
  20.     dbmS_output.put_line(l_timeElapsed);
  21.    
  22.    
  23.     dbms_output.put_line(EXTRACT(DAY FROM l_timeElapsed) || ' days');
  24.     dbms_output.put_line(EXTRACT(HOUR FROM l_timeElapsed) || ' hours');
  25.     dbms_output.put_line(EXTRACT(MINUTE FROM l_timeElapsed) || ' minutes');
  26.     dbms_output.put_line(EXTRACT(SECOND FROM l_timeElapsed) || ' seconds');
  27.  
  28.  
  29.   dbms_output.put_line('End');
  30. end;

Record Type Syntax

Record structure

  1. TYPE Pack_Item IS Record (
  2.     xMode varchar2(255),     final_file_name varchar2(2048),
  3.     file_handle UTL_FILE.FILE_TYPE,     d Pack_Item_Data
  4. );
  5. Type Pack_Item_tab IS TABLE OF Pack_Item INDEX BY PLS_INTEGER;
  1. set serveroutput on;
  2. declare
  3.   TYPE xref_Item IS RECORD (
  4.        xref_table_name VARCHAR2(255),    
  5.        ebiz_seq VARCHAR2(255),
  6.        ebiz_table_name VARCHAR2(255)
  7.    );
  8.    TYPE xref_Item_tab IS TABLE OF xref_Item INDEX BY PLS_INTEGER;
  9.    dat xref_Item_tab;
  10.    l_c pls_integer;
  11. begin
  12.   dbms_output.put_line('--Start');
  13.  
  14.   dat(0).xref_table_name := 'XREF_CUSTOMERPARTY_ACCTID';
  15.   dat(0).ebiz_seq := 'ff';
  16.   dat(0).ebiz_table_name := 'ff';
  17.  
  18.   l_c := dat.FIRST;
  19.   while (l_c is not null)
  20.   loop
  21.     dbms_output.put_line(dat(l_c).xref_table_name);
  22.     l_c := dat.NEXT(l_c);
  23.   END LOOP;

Tags

RJM Article Type
Quick Reference