BULK COLLECTION IN PL/SQL

BULK COLLECT

DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;

Bulk Fetch with LIMIT - limits fetching rows to a specified number.
This number can be a variable or an expression. But it can't be negative.
You can use limit as described below:

open cursor;
loop
fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;

for i in 1 .. l_c1.count
loop
process....
end loop;

forall i in 1 .. l_c1.count
insert into ..... values ( L_c1(i), .... );


forall i in 1 .. l_c1.count
update emp set sal = sal * 1.2 where dept_no = l_c1(i);

exit when c%notfound;
end loop;
close cursor

1000 is already a big number.

With bigger number, we could meet problems with OS limits.

Email string validation code in PL/SQL

Form validations for PL/SQL
--***PL/SQL code for Email validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;

mail_ch VARCHAR2(1);

result number; --:GR

BEGIN

t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;

-------------------------------------------------------------------------------------

IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;

WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;

IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN t_valid := 0; EXIT; END IF; END LOOP; --------------------------------------------------------------------------------------- --This is to check more than one '@' character present or not t_atpos := instr(:text_item_email,'@',1,2) ; IF t_atpos > 1 then
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present

t_atpos := instr(:text_item_email,'@',1) ;

IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not

t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

--This is to check at least one character is present between @ and '.' :GR

t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos-t_atpos<=1 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- --This is to check at least one character is present after '.' :GR t_pointpos := instr(:text_item_email,'.',1) ; IF t_totallen-t_pointpos<=0 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- END IF; if(t_valid=0) then message('Invalid Email'); result:=show_alert('alert_email'); raise form_trigger_failure; end if; END; ---***PL/SQL code for Name Validation***--- DECLARE t_valid NUMBER(1); t_totallen NUMBER(2); t_counter NUMBER(2):=0; i NUMBER(2) := 1; name_ch VARCHAR2(1); --name_ch_ascii NUMBER; result number; --:GR BEGIN t_totallen := LENGTH(:text_item_first_name); t_counter := t_totallen; i := 1; t_valid := 1; ------------------------------------------------------------------------------------- IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN t_valid := 0; ELSE --------------------------------------------------------------------------------------- --This is to check if each character in name lies in the valid ascii range t_counter := t_totallen; WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;

--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;

END LOOP;
---------------------------------------------------------------------------------------
END IF;

if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;