Avatar of Swaminathan K
Swaminathan K
Flag 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:
Oracle Database

Avatar of undefined
Last Comment
Swaminathan K

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Swaminathan K

ASKER
awesome
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes