How to null check the Record type?
Hi all,
I am having a problem with checking whether a row in a plsql table is null or not.
I have simulated the similar event here.
------------- Code ------------------------
------------------
CREATE
TABLE "TABLEA"
(
"A" NUMBER NOT NULL ENABLE,
"B" NUMBER,
"C" VARCHAR2(10 BYTE),
CONSTRAINT "TABLEA_PK" PRIMARY KEY ("A")
);
set serveroutput on;
Declare
Type Type_Tablea Is Table Of Tablea%Rowtype;
Pl_A Type_Tablea := Type_Tablea();
r_A Tablea%Rowtype;
BEGIN
Null;
R_A.A := 1;
R_A.B := 1;
r_A.C := 'Ash';
Pl_A.Extend(1);
Pl_A(1) := R_A;
R_A.A := 2;
R_A.B := 2;
r_A.C := 'Ish';
Pl_A.Extend(1);
Pl_A(2) := R_A;
R_A.A := 2;
R_A.B := 2;
r_A.C := 'Kum';
Pl_A.Extend(1);
Pl_A(3) := R_A;
Pl_A.Extend(1);
if Pl_A is not null or Pl_A is not empty then
For X In Pl_A.First..Pl_A.Count Loop
Begin
--IF Pl_A(X) is empty then
Insert Into Tablea Values Pl_A(X);
--END IF;
Exception
When Dup_Val_On_Index Then
Update Tablea Set Row = Pl_A(X) Where A=Pl_A(X).A;
EnD;
End Loop;
end if;
Commit;
Dbms_Output.Put_Line('Ashish'||Pl_A(Pl_A.Last).C);
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line(Sqlerrm);
END;
------------- Code ------------------------
shows...
ORA-01400: cannot insert NULL into ("schemaa"."TABLEA"."A")
I will be getting a plsql table, and some times it will have only null rows.. That means it might get extended, but not necessary that these extended rows wil have some rowtype object assigned to it..
Is there any possible solution other than getting the object from the plsql table type and checking the primary keys for null ?
Thanks in advance