Oracle PLSQL : Bulk Collect

 Oracle PLSQL : Bulk Collect Example

declare

Cursor cur_gl_stg is

Select  je_header_id,name,description from gl_je_headers where trunc(creation_date) >= '01-APR-2023';

 

TYPE cur_gl_stg_type IS TABLE OF cur_gl_stg%rowtype INDEX BY PLS_INTEGER;

v_gl_stg_type    cur_gl_stg_type;

l_index_cnt Number:= 0;

 

Begin

 OPEN cur_gl_stg; ---opening cursor

 loop --loop start

   l_index_cnt := l_index_cnt+1;

   FETCH cur_gl_stg

   BULK COLLECT INTO v_gl_stg_type LIMIT 30;

   EXIT WHEN v_gl_stg_type.COUNT = 0;

   dbms_output.put_line('l_index_cnt:'||l_index_cnt||','||v_gl_stg_type.count);

  

    FORALL indx IN 1 .. v_gl_stg_type.COUNT

        UPDATE gl_je_headers

      Set description = description||'BulkCollect'

      where je_header_id =v_gl_stg_type(indx).je_header_id ;

   

       dbms_output.put_line('Rowcount:'||SQL%ROWCOUNT||','||v_gl_stg_type.count);

     

 end loop;

 

    dbms_output.put_line('Total count:'||v_gl_stg_type.count);

 /*for i in 1..v_gl_stg_type.count loop

  dbms_output.put_line('Name :'||v_gl_stg_type(i).name);

 end loop;*/

 

exception when others then

   dbms_output.put_line('Error main :'||SQLERRM);

null;

End;      

ORACLE PLSQL : Nested Block Example

 PLSQL : Nested Block Example

declare

l_main_block varchar2(100) := 'MAIN_BLOCK';

begin

    dbms_output.put_line('Main Block:'||l_main_block);

    dbms_output.put_line('Nested Block inside min Block:'||l_nested_block);

 

  ----------------------------Nested block---------------

 

  declare

  l_nested_block varchar2(100) := 'NESTED_BLOCK';

  begin

      dbms_output.put_line('nested Block:'||l_nested_block); --to write another business logic

      dbms_output.put_line('Main Block inside nested Block:'||l_main_block);

  end;   

  ---------------------------end -Nested block---------------

 

exception when others then

 dbms_output.put_line('Error:'||SQLERRM);

end;

Oracle PLSQL : How to resolve package state in invalid state

How to resolve PLSQL package state in invalid state

Error in Workflow APINL/125-806353 ORA-04061: existing state of  has been invalidated

ORA-04061: existing state of package body "APPS.APINV_NO_WF_PKG" has been invalidated

ORA-04065: not executed, altered or dropped package body "    

Solution :-  Go to v$Session  and find your object and kill the session