regexp_substr

 SELECT level,regexp_substr('Apple,Orange,Mango,Grapes','[^,]+',1,level) regexp_substr

FROM dual

  CONNECT BY level<=regexp_count('Apple,Orange,Mango,Grapes',',')+1;

SELECT ltrim(substr(text,1,15),'Order No'),length(('Order No 11069 RE Instalment ID 27')),

case 

     when length(text)=30 then 

     ltrim(substr(text,1,14),'Order No')

     when length(text) = 31 then

     ltrim(substr(text,1,15),'Order No')

     when length(text) =32 then

     ltrim(substr(text,1,16),'Order No')

     when length(text) = 33 then

     ltrim(substr(text,1,17),'Order No')

     when length(text) = 34 then

     ltrim(substr(text,1,18),'Order No')

     END ORDER_NO

from GL_AND_HOLD_VOU_ROW_QRY

where ACCOUNT='1221100'

and voucher_type='M'

select sum(CREDIT_AMOUNT) realized_Int, 0 unearned_Int,

       (select i.CREATORS_REFERENCE 

            from IAL_C_ORDER_BASE_INVOICE_VIEW i 

            left join IFSAPP.IAL_C_ORDER_BASE_INVOICE_VIEW b

            ON i.SERIES_ID||''||i.INVOICE_NO=b.SERIES_ID||''||b.INVOICE_NO

            WHERE (gl.REFERENCE_SERIE||''||gl.REFERENCE_NUMBER=i.SERIES_ID||''||i.INVOICE_NO))

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='F'

UNION ALL 

select 0 realized_Int, sum(DEBET_AMOUNT)  unearned_Int,

   regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '')

/*  CASE 

     WHEN LENGTH(TEXT)=30 THEN 

        LTRIM(SUBSTR(TEXT,1,14),'Order No')

     WHEN LENGTH(TEXT) = 31 THEN

        LTRIM(SUBSTR(TEXT,1,15),'Order No')

     WHEN LENGTH(TEXT) =32 THEN

        LTRIM(SUBSTR(TEXT,1,16),'Order No')

     WHEN LENGTH(TEXT) = 33 THEN

        LTRIM(SUBSTR(TEXT,1,17),'Order No')

     WHEN LENGTH(TEXT) = 34 THEN

        LTRIM(SUBSTR(TEXT,1,18),'Order No')

     END ORDER_NO

  */

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 

select regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '') xaa --regexp_substr(text, '(.*?)( |$)', 1, 3, NULL, 1) col3,text,length(text)

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 

and length(text)>32

Order No 16072 R Instalment ID 51 16072 R

Order No 16072 R Instalment ID 52 16072 R

Order No 40735 Instalment ID 8 40735

Order No 40735 Instalment ID 9 40735

boot menu access by

 

Oracle PL SQL : Learn Bulk Collect and FOR ALL | Bulk Collect and LIMIT With Example

 1. Looping concept            -- .15seconds...
2. Bulk Collect               -- 0seconds..
3. Bulk Collect using LIMIT   --0seconds..
4. Direct Insert              --01seconds..

https://www.youtube.com/watch?v=PM6fLDgSl7I
alter session set current_schema=hr;
SET SERVEROUTPUT ON
select count(*) from EMPLOYEES ;
create table EMPLOYEES2 as select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES where 1=2;
select count(*) from employees2;
--(1)
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  l_err_count number;
  l_ename varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 loop 
  insert into EMPLOYEES2(EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID) values(i.EMPLOYEE_ID,i.LAST_NAME, i.DEPARTMENT_ID);
  end loop;
  commit;
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error:'||sqlerrm);
END;

--(2)
--Bulk Collect
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  for i in c1 
  loop 
  l_tp.extend;
  l_tp(l_tp.last).EMPLOYEE_ID   :=i.EMPLOYEE_ID;
  l_tp(l_tp.last).LAST_NAME     :=i.LAST_NAME;
  l_tp(l_tp.last).DEPARTMENT_ID :=i.DEPARTMENT_ID;
  end loop; 
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
  insert into EMPLOYEES2 values l_tp(i);
  commit;
  --
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;





--(3)
--Bulk Collect using LIMIT 
DECLARE 
cursor c1 is 
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
  type tp is table of employees2%rowtype index by binary_integer;
  l_tp tp:=tp();
  l_err_count number;
  l_last_name varchar2(100);
  l_start number default dbms_utility.get_time;
  BEGIN
  open c1;
  loop 
      fetch c1 bulk collect into l_tp limit 50000;
  exit when l_tp.COUNT=0;
  --forall insert 
  forall i in 1..l_tp.COUNT save exceptions 
      insert into EMPLOYEES2 values l_tp(i);
      commit;
  --
  end loop;
  close c1;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
l_err_count:=SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of failures:' ||l_err_count);
FOR i IN 1..l_err_count LOOP
l_last_name := l_tp(SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).last_name;
dbms_output.put_line('Error:'|| i ||'Array Index: '
|| SQL%BULK_EXCEPTIONS(i).error_index||'last_name :'
||l_last_name||''|| 'Message:'|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;




--(4)
--Direct Insert
DECLARE 
  l_start number default dbms_utility.get_time;
  BEGIN
      insert into EMPLOYEES2 (EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID)
  select EMPLOYEE_ID,LAST_NAME, DEPARTMENT_ID from EMPLOYEES;
      commit;
  
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds...');
EXCEPTION WHEN OTHERS THEN 
dbms_output.put_line('Error :' ||sqlerrm);
END;

Employee, manager, senior manger in sql

 SELECT 

  A.DP_CODE Depot_Name,

  a.empcode rmcode,

         a.e_name rmname,

         b.empcode fmcode,

         b.e_name fmname,

         b.empcodehr hrcodefe,

         c.empcode mrcode,

         c.e_name mrname,

         c.teri_code fopin,

         c.empcode mrcode,

         c.empcodehr hrcodempo,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'JAN', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            JAN,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'FEB', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            FEB,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'MAR', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            MAR,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'APR', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            APR,

         SUM (

            DECODE (TO_CHAR (s.vdate, 'MON'),

                    'MAY', NVL (v_disptp, 0) - NVL (v_rettp, 0)

                    ))

            MAY

    FROM emp a,

         emp b,

         emp c,

         v$salesreport_f2 s

   WHERE     a.empcode = b.mgr

         AND b.empcode = c.mgr

         AND s.empcode = c.empcode

         AND s.vdate BETWEEN :dt1 AND :dt2

         AND c.empcode = NVL (:p_empcode, c.empcode)

         AND s.dp_code = NVL(:dcode,s.dp_code)

GROUP BY 

A.DP_CODE,

a.empcode,

         a.e_name,

         b.empcode,

         b.e_name,

         c.empcode,

         c.e_name,

         c.teri_code,

         c.empcodehr,

         b.empcodehr

ORDER BY c.e_name

Bulk Inserts with Oracle

 CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/

exec test_proc;

cursor with procedure

 CREATE OR REPLACE Procedure REPORTDB.IAL_C_LEASING_DET_UNORD_IFS_PROC

IS

   CURSOR cur

   IS

      (

--last create and modify by Md. Quium Hossain

--modify date 11-09-2022 9:20 AM

--this procedure is use (1)IAL_C_LEASING_CUSTOMER_ORDER_IFS_PROC

--this procedure is use (2)IAL_C_LEASING_DET_UNORD_IFS_PROC

--this procedure is use (3)IAL_C_CO_COLLECTION_DETAIL_FULL_PROC

SELECT t.company, 

       t.order_no, 

       t.identity,

       t.invoice_id,

       t.installment_id,

       t.due_date,    

       t.open_amount,

       t.curr_amount,             

       t.state     ,

       (SELECT MAX(due_date) last_date FROM ifsapp.ial_leasing_det_view@prod l

        WHERE t.order_no=l.order_no) last_instal_date,

       t.c_interest_amount       

FROM  ifsapp.IAL_C_LEASING_DET_UNORD_VIEW@prod t

);

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE reportdb.IAL_C_LEASING_DET_UNORD_TAB_IFS';

commit;

   FOR rec IN cur

   LOOP

INSERT INTO reportdb.IAL_C_LEASING_DET_UNORD_TAB_IFS(

         company                   ,

         order_no                  ,

         identity                  ,

         invoice_id                ,

         installment_id            ,

         due_date                  ,

         open_amount               ,

         curr_amount               ,

         state                     ,

         last_instal_date          ,

         c_interest_amount         

     )

VALUES(

         rec.company               ,

         rec.order_no              ,

         rec.identity              ,

         rec.invoice_id            ,

         rec.installment_id        ,

         rec.due_date              ,

         rec.open_amount           ,

         rec.curr_amount           ,

         rec.state                 ,

         rec.last_instal_date      ,

         rec.c_interest_amount     

     );

   END LOOP;

COMMIT;

END;

/

Truncate Table in Oracle Procedure

CREATE OR REPLACE PROCEDURE trnct_table (i_table_name IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i_table_name;
   DBMS_OUTPUT.put_line (
      'Table ' || i_table_name || ' truncated successfully.');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Truncate table failed.');
END; 

SET SERVEROUTPUT ON;

BEGIN

   trnct_table ('emp');

END;

/