Link to home
Start Free TrialLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on

oracle collections

Hi Team,

Below is the code I have written to check whether a column value is null or not . What Iam trying to do is to avoid multiple if conditions in the code , put that logic into procedure and call it in the code. Iam trying to use the collections to achieve this functionality.


Declare
type t_fieldlist is record
(
fieldname varchar2(40),
fieldValue varchar2(1000)
);

type t_filelist is table of t_fieldlist;
v_fieldlist t_filelist;

v_out varchar2(30);

PROCEDURE P_VALIDATEFIELDS(p_fieldlist t_filelist  , p_error_fields out varchar2)
is
begin
p_error_Fields:='Required field  :';
for i in p_fieldlist.first..p_fieldlist.last
loop
     If p_fieldlist(i).fieldvalue is null then
            p_error_fields:=p_error_fields || p_fieldlist(i).fieldname|| ',';
     end if;
end loop;
       p_error_fields:=rtrim(',' , p_error_fields) || ' not present in incoming file';
       end P_VALIDATEFIELDS;
begin
v_fieldlist:= t_filelist();
v_fieldlist.extend;
v_fieldlist.fieldname:='PROFILE_ID';
v_fieldlist.fieldValue:=101;
v_fieldlist.extend;
v_fieldlist.fieldname:='ALLTURNAKEY';
v_fieldlist.fieldValue:=NULL;
p_validatefields(p_fieldlist=>v_fieldlist, p_error_fields=>v_out);
DBMS_OUTPUT.PUT_LINE(v_out);
End;
/

Iam getting the below error while executing the code .

RA-06550: line 28, column 13:
PLS-00302: component 'FIELDNAME' must be declared
ORA-06550: line 28, column 1:
PL/SQL: Statement ignored
ORA-06550: line 29, column 13:
PLS-00302: component 'FIELDVALUE' must be declared
ORA-06550: line 29, column 1:
PL/SQL: Statement ignored
ORA-06550: line 31, column 13:
PLS-00302: component 'FIELDNAME' must be declared
ORA-06550: line 31, column 1:
PL/SQL: Statement ignored
ORA-06550: line 32, column 13:
PLS-00302: component 'FIELDVALUE' must be declared
ORA-06550: line 32, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Swaminathan K

ASKER

awesome