SQL Oracle object%rowtype dynamic-CodePudding

I have an object which is a ROWTYPE from a table and need to recover some of the columns to an array list.

For example:

The ROWTYPE contents Name1, Last_Name1, Id1, Adress1...Name25,Last_Name25,Id25,Adress25 in columns.

I need to know if I can recover them by a dynamic way with a loop to an array type like this:

Name(1-25),Last_Name(1-25),Id(1-25),Adress(1-25).

CodePudding user response：

Given a simplified example of the problem with the table:

CREATE TABLE table_name (id1, name1, id2, name2, id3, name3, id4, name4) AS
SELECT 1, 'N1', 2, 'N2', 3, 'N3', 4, 'N4' FROM DUAL;

I am assuming, that to get the %ROWTYPE variable, you are using something like:

DECLARE
  data TABLE_NAME%ROWTYPE;
BEGIN
  SELECT * INTO data FROM table_name FETCH FIRST ROW ONLY;

  DBMS_OUTPUT.PUT_LINE('1: ' || data.id1 || ', ' || data.name1);
  DBMS_OUTPUT.PUT_LINE('2: ' || data.id2 || ', ' || data.name2);
  DBMS_OUTPUT.PUT_LINE('3: ' || data.id3 || ', ' || data.name3);
  DBMS_OUTPUT.PUT_LINE('4: ' || data.id4 || ', ' || data.name4);
END;
/

Rather than trying to dynamically access the %ROWTYPE record, why do you not bypass the problem and separate the values in the SELECT statement using UNPIVOT in a cursor and then you can use %ROWTYPE on the cursor (rather than the table):

DECLARE
  CURSOR cur IS
    SELECT id, name
    FROM   (SELECT * FROM table_name FETCH FIRST ROW ONLY)
    UNPIVOT (
      (id, name)
      FOR idx IN (
        (id1, name1) AS 1,
        (id2, name2) AS 2,
        (id3, name3) AS 3,
        (id4, name4) AS 4
      )
    );
  
  TYPE cur_row_arr IS TABLE OF cur%ROWTYPE;
  rw  cur%ROWTYPE;
  arr cur_row_arr := cur_row_arr();
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO rw;
    EXIT WHEN cur%NOTFOUND;
    arr.EXTEND;
    arr(arr.COUNT) := rw;
  END LOOP;
  CLOSE cur;
  
  FOR i IN 1 .. arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( i || ': ' || arr(i).id || ', ' || arr(i).name );
  END LOOP;
END;
/

db<>fiddle here

CodePudding user response：

Don't try to do it dynamically, just hard-code the values.

Given the simplified example:

CREATE TABLE table_name (id1, name1, id2, name2, id3, name3, id4, name4) AS
SELECT 1, 'N1', 2, 'N2', 3, 'N3', 4, 'N4' FROM DUAL;

Then:

CREATE PROCEDURE test(
  v_row IN TABLE_NAME%ROWTYPE
)
IS
  TYPE details_type IS RECORD(
    id   TABLE_NAME.ID1%TYPE,
    name TABLE_NAME.NAME1%TYPE
  );

  TYPE details_tab_type IS TABLE OF details_type;

  details details_tab_type := details_tab_type();
BEGIN
  details.EXTEND(4);

  details(1) := details_type(v_row.id1, v_row.name1);
  details(2) := details_type(v_row.id2, v_row.name2);
  details(3) := details_type(v_row.id3, v_row.name3);
  details(4) := details_type(v_row.id4, v_row.name4);

  FOR i IN 1 .. details.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(i || ': ' || details(i).id || ', ' || details(i).name);
  END LOOP;
END;
/

All you need to do is write the first assignment statement details(1) := details_type(v_row.id1, v_row.name1); and then copy-paste multiple copies and increment the numbers by 1 each time.

Then you can call it using:

DECLARE
  v_row TABLE_NAME%ROWTYPE;
BEGIN
  SELECT * INTO v_row FROM table_name FETCH FIRST ROW ONLY;
  
  test(v_row);
END;
/

Which outputs:

1: 1, N1
2: 2, N2
3: 3, N3
4: 4, N4

db<>fiddle here