troubleshooting Question

oracle collections

Avatar of sam_2012
sam_2012 asked on
Oracle Database
2 Comments1 Solution152 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros