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:
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you need to specify which record you are dereferencing

Note the (1) and (2)  to specify the 1st and 2nd records of your collection


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(1).fieldname := 'PROFILE_ID';
    v_fieldlist(1).fieldvalue := 101;
    v_fieldlist.EXTEND;
    v_fieldlist(2).fieldname := 'ALLTURNAKEY';
    v_fieldlist(2).fieldvalue := NULL;
    p_validatefields(p_fieldlist => v_fieldlist, p_error_fields => v_out);
    DBMS_OUTPUT.put_line(v_out);
END;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
awesome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.