oracle collections

sam_2012
sam_2012 used Ask the Experts™
on
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:
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

sam_2012

Author

Commented:
awesome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial