Unanswered questions

why packages? global variables loads to buffer memory thereby performance Dimensional databses - star and snowflake schema Fact and dimensional tables Index organized tables pipelined functions local and global indexes

FOR ALL Bulk Collect

create table test_ins (table_name varchar2(3000));
truncate table test_ins;
select * from test_ins;

declare

type nes_tab is table of user_tables.table_name%TYPE index by pls_integer;
nest_obj nes_tab;

cursor c1 is
select table_name from user_tables ;

begin

open c1;

loop

    fetch c1 bulk collect into nest_obj limit 5;

    forall i in 1..nest_obj.count save exceptions         
        insert into test_ins values(nest_obj(i));
        commit;
        exit when c1%notfound;   
end loop;

close c1;
exception when others then
dbms_output.put_line('Exception occurred :'||SQLERRM || SQLCODE);
end;
/

UNIX

awk command :
https://www.geeksforgeeks.org/awk-command-unixlinux-examples/

awk options 'selection _criteria {action }' input-file > output-file

Cursor Examples

set serveroutput on;

declare
v_tab user_tables.table_name%TYPE;
begin
    select table_name
    into v_tab
    from user_tables
    where tablespace_name = 'EXAMPLE';

    dbms_output.put_line('Table Names are : ' || v_tab);
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

--=============================================================================================
--simple for cursor
set serveroutput on;
declare
begin

for rec in  (
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE')

loop
    dbms_output.put_line(rec.table_name);
end loop;
end;
/
--===============================================================================================

--assigning data retrived from  for cursor into collection 
--1. Associate Arrays

set serveroutput on;
declare

--Associate arrays
TYPE ass_arr is table of user_tables.table_name %TYPE index by pls_integer;
ass_arr_obj ass_arr;

i number :=0;
begin

for rec in  (
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE')

loop
    ass_arr_obj(i):= rec.table_name;
    i:=i+1;
end loop;

dbms_output.put_line('Table Names are :');

for j in ass_arr_obj.first.. ass_arr_obj.last
loop
    dbms_output.put_line(ass_arr_obj(j));
end loop;
end;
/

2. Nested Tables

--Persistent collection - Can be stored in the databse and reused

CREATE OR REPLACE TYPE nes_tab is table of VARCHAR2(100);


declare

TYPE nes_tab is table of VARCHAR2(100);
nes_tab_val nes_tab := nes_tab(
                                'LOCATIONS',
                                'JOB_HISTORY',
                                'EMPLOYEES',
                                'JOBS',
                                'DEPARTMENTS',
                                'REGIONS');
                               
begin
dbms_output.put_line('The table names are :');

for i in 1..nes_tab_val.count
loop
    dbms_output.put_line(nes_tab_val(i));
end loop;
end;
/

3. VARRAYS


--Using cursors to fetch records by looping into a variable

set serveroutput on;
declare

v_tab user_tables.table_name%TYPE;

cursor tab_cur is
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE';
   
begin

open tab_cur;
   
    loop
        fetch tab_cur into v_tab;
            exit when tab_cur%notfound;
            dbms_output.put_line('Table Names are : ' || v_tab);   
    end loop;
close tab_cur;
   
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

--==================================================================================
--Using cursors with bulkcollect and collection

set serveroutput on;
declare

TYPE v_col_tab IS TABLE OF user_tables.table_name%TYPE index by PLS_INTEGER;
v_tab v_col_tab;

cursor tab_cur is
    select table_name   
    from user_tables
    where tablespace_name = 'EXAMPLE';
   
begin

open tab_cur;
    fetch tab_cur bulk collect into v_tab;
    dbms_output.put_line('Table Names are :');
    for i in 1..v_tab.count   
    loop
        dbms_output.put_line( v_tab(i));   
    end loop;
close tab_cur;
   
   
    exception
        when too_many_rows then
        dbms_output.put_line('More than  1 rows are fetched');
       
        when others then
        dbms_output.put_line('Other Exception occurred');   
end;
/

Exception Handling

Examples :

--User defined exception

--1. Variable -- By using EXCEPTION datatype
set serveroutput on;
declare
a number := 1;
 new_exp exception;
begin
if (a = 1) then
raise new_exp;
end if;
exception
when new_exp then
dbms_output.put_line('New_exp occured');
when others then
dbms_output.put_line('Unknown Exception Occurred');
end;
/

--2. Function - raise_application_error()

declare
a number :=1;
begin
if ( a=1) then
raise_application_error(-20000,'Error Message');
end if;
exception when others then
dbms_output.put_line('Exception Occurred:' || SQLERRM || SQLCODE);
end;
/

3. PRAGMA EXCEPTION  INIT -- To assign exception name with error number
--used when more than 1 user defined exception

set serveroutput on;
declare

my_exp EXCEPTION ;
PRAGMA EXCEPTION_INIT(my_exp,-20009);
a number :=1;
begin
if (a=1) then
--raise my_exp;
raise_application_error(-20009,'User defined excecption text');
end if;
exception when my_exp then
dbms_output.put_line('Excecption Occurred'||SQLCODE||SQLERRM);
end;
/