0

I am a beginner in the ORACLE PL/SQL Programming arena, and this is the first time i am working with collections. This is my requirement. I have defined two objects with required columns from two tables, now while looping through picking record by record, i have to check many conditions , once i am done verifying all the scenarios i have to update the first table records with the closest match from the second table. This is how it looks

DECLARE

TYPE OBJ1 AS OBJECT(
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COLUMN6
);

TYPE OBJ2 AS OBJECT(
COLUMNA,
COLUMNB,
COLUMNC,
COLUMND,
COLUMNE,
COLUMNF);

TYPE TAB1 AS TABLE(OBJ1);
TYPE TAB2 AS TABLE(OBJ2);

X TAB1;
Y TAB2;

VARRAY1 AS VARRAY(10) OF NUMBER;
VARRAY2 AS VARRAY(10) OF VARCHAR2(30);
VARRAY3 AS VARRAY(10) OF VARCHAR2(30);
VARRAY4 AS VARRAY(10) OF NUMBER;

CPTY VARRAY1=NEW VARRAY1();
LGL VARRAY2=NEW VARRAY2();
BUS VARRAY3=NEW VARRAY3();
MSTR VARRAY4=NEW VARRAY4();

FOR I IN  TAB1.FIRST..TAB1.LAST
LOOP
FOR J IN TAB2.FIRST..TAB2.LAST
LOOP

IF (TAB1(I).COLUMN1=TAB2(J).COLUMN1 AND TAB1(I).COLUMN2=TAB2(J).COLUMN AND 
TAB1(I).COLUMN3=TAB2(J).COLUMN3) THEN
        CPTY(0) :=TAB2(J).COLUMN1;
        LGL(0) :=TAB2(J).COLUMN2;
        BUS(0) :=TAB2(J).COLUMN3;
        MSTR(0) :=TAB2(J).COLUMN4;
 ELSIF (TAB1(I).COLUMN1=TAB2(J).COLUMN1 AND TAB1(I).COLUMN2=TAB2(J).COLUMN2
 AND TAB1(I).COLUMN3!=TAB2(J).COLUMN3) THEN
        CPTY(1) :=TAB2(J).COLUMN1;
        LGL(1) :=TAB2(J).COLUMN2;
        BUS(1) :=TAB2(J).COLUMN3;
        MSTR(1) :=TAB2(J).COLUMN4;
        MSTR(4) :=TAB2(J).COLUMN4;

ELSIF

        -------------
        I wnt to set values for other indexes
END IF;

END LOOP;
IF (CPTY(0) IS NOT NULL AND LGL(0) IS NOT NUL AND BUS(0) IS NOT NULL AND
    MSTR(0) IS NOT NULL) THEN

        TAB1(I).COLUMN1 :=CPTY(0);
        TAB1(I).COLUMN2 :=LGL(0);
        TAB1(I).COLUMN3 :=BUS(0);
        TAB1(I).COLUMN4 :=MSTR(0);

ELSIF (CPTY(0) IS NOT NULL AND LGL(0) IS NOT NUL AND BUS(0) IS NOT NULL AND  
MSTR(0) IS NOT NULL) THEN

        TAB1(I).COLUMN1 :=CPTY(1);
        TAB1(I).COLUMN2 :=LGL(1);
        TAB1(I).COLUMN3 :=BUS(1);
        TAB1(I).COLUMN4 :=MSTR(1);

 --AND SO ON, I NEED TO SET PRIORITY THIS WAY

END IF;
END LOOP;

I wanted to initialize using index and retrieve using index just like how we do in java,but that is not happening, i am not able to set value using index,I have searched, but i couldn't find any example of this sort. Please do let me know if this is possible, else anyother way how this can be achieved. If this is a repitive question,please excuse me. I couldnt find an exact match.

Thanks

jiterika
  • 21
  • 1
  • 4
  • 2
    And your error is? It would appear that your block should throw a compilation error when you attempt to define an object in the declaration section. If you want an object, you'd need to define it outside the block. If you want to define something in the block, it would need to be a record. Neither of these, though, appear to relate to using an index so I'm unsure what you're asking. – Justin Cave Mar 01 '15 at 20:18
  • Cave Thanks for your reply, it is ot about the object part,i did create it globally, it is about varray. i wanted to initialise it this way cpty(1) :=1234 based on conditions, i want to insert data into different positions of varray depending onthe scenario, but it is not allowing me to do that way. – jiterika Mar 02 '15 at 02:32
  • So you're using code that is different than what you posted and getting an error you don't specify at a line that you don't specify. Given that, it's going to be very difficult to guess what the problem is. Posting an actual test case with actual code and the actual error would be most helpful. – Justin Cave Mar 02 '15 at 02:34
  • No,no this is the actual code its just that column names are all different,i am struggling with this part of the code. CPTY(0) :=TAB2(J).COLUMN1; LGL(0) :=TAB2(J).COLUMN2; BUS(0) :=TAB2(J).COLUMN3; MSTR(0) :=TAB2(J).COLUMN4; This assignment is throwing an error like Invalid syntax – jiterika Mar 02 '15 at 02:36
  • So your actual code tries to declare an object in the PL/SQL block and gets an error that you can't declare an object there. So it never attempts to do anything with a collection? That's not what you said earlier. – Justin Cave Mar 02 '15 at 02:39
  • no, no there isno problem with objects, i am able to deal with them. My problem is with varrays. i am not able to assign values to it like cpty(0) :=1234 is throwing an error. – jiterika Mar 02 '15 at 03:11
  • Then we're back to square 1. You've posted code that is substantially different than the code you're actually using. You're getting an error that you don't specify. The error occurs at a line that you don't specify. Given that, no one is likely to be able to correctly guess the code you're actually using, the error you're actually getting, and what you actually want the code to do. If you edit your post to provide an actual test case and an actual error stack, someone might be able to help you. – Justin Cave Mar 02 '15 at 03:13

0 Answers0