Oracle PLSQL Array will not allow me to Index or Extend

Question

create or replace TYPE csv_rec IS OBJECT (client_code varchar2(500),
                                          debtor_code varchar2(500));

--

create or replace TYPE csv_table IS TABLE OF csv_rec;
--
PROCEDURE Create_Detail
(xClientCode IN NUMBER 
,xStatus IN OUT VARCHAR2
,xWebCursor IN OUT SYS_REFCURSOR
) IS
--
    l_client_code        NUMBER;
    l_array              csv_table;
--
    CURSOR c1 IS
    SELECT t1.field1,
           t2.field2
    FROM table1 t1,
         table2 t2
    WHERE t1.client_code = l_client_code
    AND t1.client_code = t2.client_code;
--
    c_rec c1%ROWTYPE;
--
BEGIN
    --
    l_client_code := xClientCode;
    --
    l_array := csv_table();
    --
    FOR c_rec IN c1
    --
    LOOP
        --
        l_array.extend;
        l_array := csv_table(csv_rec(c_rec.field1,c_rec.field2));
        --
    END LOOP;   
    --
    OPEN xWebCursor FOR 
    SELECT * FROM TABLE(l_array);
    --
    xStatus := 'TRUE';
    --
EXCEPTION
      WHEN OTHERS THEN
         xStatus := 'FALSE';
END Create_Detail;

I am having trouble that the above code will always populate the array with the final row in the cursor. I don't know how to get the 'extend' to work, or move to 'next' row in the array. It will not allow me to use l_array(index) at all so I am stumped? Can anyone help?

Answer 1

Try using bulk collect into instead of loop,

Note:- In case you are dealing with huge data set try suing limit with bulk collect into.

CREATE OR REPLACE PROCEDURE create_detail_constructor
(xclientcode IN NUMBER,xstatus     IN OUT VARCHAR2,xwebcursor  IN OUT SYS_REFCURSOR
) IS
   l_client_code NUMBER;
   l_array       csv_table := csv_table();
   CURSOR c1 IS
      SELECT csv_rec(t1.field1,t2.field2)
      FROM   (SELECT 1 client_code
                    ,'1 F1' field1
              FROM   dual
              UNION ALL
              SELECT 2 client_code
                    ,'2 F2' field1
              FROM   dual) t1
            ,(SELECT 1 client_code
                    ,'1 F1 T2' field2
              FROM   dual
              UNION ALL
              SELECT 1 client_code
                    ,'1 F2 T2' field2
              FROM   dual) t2
      WHERE  t1.client_code = l_client_code
      AND    t1.client_code = t2.client_code;
BEGIN
   --
   l_client_code := xclientcode;
   --
   OPEN c1;
   LOOP
     FETCH c1 BULK COLLECT INTO l_array ;
     EXIT WHEN c1%NOTFOUND;
   END LOOP;
   CLOSE c1;
   --
   OPEN xwebcursor FOR
      SELECT * FROM TABLE(l_array);
   --
   xstatus := 'TRUE';
   --
EXCEPTION
   WHEN OTHERS THEN
      xstatus := 'FALSE';
END create_detail_constructor;

--Test

DECLARE
   xwebcursor  SYS_REFCURSOR;
   xstatus     VARCHAR2(100) := 'A';
   xclientcode NUMBER := 1;
   l_field1    VARCHAR2(100);
   l_field2    VARCHAR2(100);
BEGIN
   -- Call the procedure
   create_detail_constructor(xclientcode => xclientcode
                ,xstatus     => xstatus
                ,xwebcursor  => xwebcursor);
   LOOP
      FETCH xwebcursor
         INTO l_field1
             ,l_field2;
      EXIT WHEN xwebcursor%NOTFOUND;
      dbms_output.put_line(l_field1 || '-' || l_field2);
   END LOOP;
   CLOSE xwebcursor;
END;

If we want to do the same with loop then we need to declare another type and use multiset union to append the result (at least I know this way)

CREATE OR REPLACE PROCEDURE create_detail
(xclientcode IN NUMBER,xstatus     IN OUT VARCHAR2,xwebcursor  IN OUT SYS_REFCURSOR
) IS
   l_client_code NUMBER;
   l_array       csv_table;
   l_array_final csv_table := csv_table();
   CURSOR c1 IS
      SELECT t1.field1
            ,t2.field2
      FROM   (SELECT 1 client_code
                    ,'1 F1' field1
              FROM   dual
              UNION ALL
              SELECT 2 client_code
                    ,'2 F2' field1
              FROM   dual) t1
            ,(SELECT 1 client_code
                    ,'1 F1 T2' field2
              FROM   dual
              UNION ALL
              SELECT 1 client_code
                    ,'1 F2 T2' field2
              FROM   dual) t2
      WHERE  t1.client_code = l_client_code
      AND    t1.client_code = t2.client_code;
   c_rec c1%ROWTYPE;
BEGIN
   --
   l_client_code := xclientcode;
   --
   l_array := csv_table();
   --
   FOR c_rec IN c1
   --
   LOOP
      --
      l_array.extend;
      l_array := csv_table(csv_rec(c_rec.field1
                                  ,c_rec.field2));
      l_array_final := l_array_final MULTISET UNION l_array;
   END LOOP;
   --
   OPEN xwebcursor FOR
      SELECT * FROM TABLE(l_array_final);
   --
   xstatus := 'TRUE';
   --
EXCEPTION
   WHEN OTHERS THEN
      xstatus := 'FALSE';
END create_detail;

--Test

DECLARE
   xwebcursor  SYS_REFCURSOR;
   xstatus     VARCHAR2(100) := 'A';
   xclientcode NUMBER := 1;
   l_field1    VARCHAR2(100);
   l_field2    VARCHAR2(100);
BEGIN
   -- Call the procedure
   create_detail(xclientcode => xclientcode
                ,xstatus     => xstatus
                ,xwebcursor  => xwebcursor);
   LOOP
      FETCH xwebcursor
         INTO l_field1
             ,l_field2;
      EXIT WHEN xwebcursor%NOTFOUND;
      dbms_output.put_line(l_field1 || '-' || l_field2);
   END LOOP;
   CLOSE xwebcursor;
END;

Answer 2

PROCEDURE Create_Detail
(xClientCode IN NUMBER 
,xStatus IN OUT VARCHAR2
,xWebCursor IN OUT SYS_REFCURSOR
) IS
--
    l_client_code        NUMBER;
    l_loop_var           NUMBER := 1;
    l_array_rec          csv_rec := csv_rec(null,null);
    l_array              csv_table : csv_table();
--
    CURSOR c1 IS
    SELECT t1.field1,
           t2.field2
    FROM table1 t1,
         table2 t2
    WHERE t1.client_code = l_client_code
    AND t1.client_code = t2.client_code;
--
    c_rec c1%ROWTYPE;
--
BEGIN
    --
    l_client_code := xClientCode;
    --
    FOR c_rec IN c1
    --
    LOOP
        --
        l_array_rec.client_code := c1.field1;
        l_array_rec.debtor_code := c2.field2;
        --
        l_array.extend;
        l_array(l_loop_var) := l_array_rec;
        --
        l_loop_var := l_loop_var + 1;
        --
    END LOOP;   
    --
    OPEN xWebCursor FOR 
    SELECT * FROM TABLE(l_array);
    --
    xStatus := 'TRUE';
    --
EXCEPTION
      WHEN OTHERS THEN
         xStatus := 'FALSE';
END Create_Detail;

Found the fix was to include the additional declaration of the Object Type and then could add the index. The Extend then worked.

Answer 3

If you construct the csv_rec object directly in the cursor, you can save a lot of processing steps.

create or replace procedure create_detail
    ( xClientCode in number
    , xStatus     in out varchar2
    , xWebCursor  in out sys_refcursor )
as
    l_client_code number := xClientCode;
    l_array       csv_table := csv_table();
begin
    for r in (
        select csv_rec(t1.field1, t2.field2) as csv
        from   table1 t1
               join table2 t2 on t2.client_code = t1.client_code
        where  t1.client_code = l_client_code;
    )
    loop
        l_array.extend;
        l_array(l_array.count) := r.csv;
    end loop;

    open xWebCursor for
        select * from table(l_array);

    xStatus := 'TRUE';

exception
    when others then
        xStatus := 'FALSE';
end create_detail;

But you can do it without a loop:

create or replace procedure create_detail
    ( xClientCode in number
    , xStatus     in out varchar2
    , xWebCursor  in out sys_refcursor )
as
    l_client_code number := xClientCode;
    l_array       csv_table := csv_table();
begin
    select csv_rec(t1.field1, t2.field2) bulk collect into l_array
    from   table1 t1
           join table2 t2 on t2.client_code = t1.client_code
    where  t1.client_code = l_client_code;

    open xWebCursor for
        select * from table(l_array);

    xStatus := 'TRUE';

exception
    when others then
        xStatus := 'FALSE';
end create_detail;

You could do it without the object type and the array as well, by just returning the cursor, but presumably this is a simplified version and the real code needs the array for something.

The others exception handler is risky. Maybe it should be when no_data_found, and let anything else be a real exception and fail. What if it's something unexpected like ORA-01578: ORACLE data block corrupted?

(Edit: just noticed Sujitmohanty30 was first to suggest the inline object constructor.)

Just as a comment, objects and records are two different things, and normally CSV stands for Comma-Separated Values, so csv_rec is a slightly confusing name for an object type.

Also I've always found neat code makes it easier to take in the structure and spot errors or overcomplicated steps, so I removed all the empty comments, and also the uppercase because it isn't 1974 any more. I think the result is a lot easier to read.