CODEWITHSUNDEEP
databaseoracle-databaseloopsplsqldatabase-cursor
user5507535
user5507535

Reputation: 1800

Oracle PL/SQL - Iterate on column names of locally defined table

I would like to iterate on the names of a locally defined table, but it doesn't work as expected:

declare
   type books is record
        (title   varchar(50)  := 'First Book'
        ,author  varchar(50)  := 'Me'
        ,subject varchar(100) := 'Simple ones'
        ,book_id number       := 94321
        );      
   type table_of_books is 
      table of books;
   list_of_books table_of_books;
   
   CURSOR c IS
     SELECT COLUMN_NAME FROM ALL_COL_COMMENTS WHERE table_name='list_of_books';
begin
  dbms_output.enable;
  list_of_books := table_of_books();
  list_of_books.extend(1);
  list_of_books(1) := books();
  
  FOR current_field IN c LOOP -- Is not iterating
    dbms_output.put_line(current_field.column_name); -- I would expect output here
  END LOOP;
end;
/

This is Oracle 19c.

Upvotes: 1

Views: 619

Answers (4)

0xdb
0xdb

Reputation: 3697

Attributes of the types declared in the PL/SQL scope are not stored as metadata in the data dictionary.

One possible solution is to get the column names as a result of unnesting query with the TABLE operator. The collection must be known to the SQL parser, i.e. at least declared in the package. In this case the data in the collection is not needed, just open the cursor with an empty collection:

create or replace package libapi as 
    type book is record (
        title varchar(50),
        author varchar(50),
        subject varchar(100),
        book_id number);      
    type books is table of book index by pls_integer;
end;
/
declare
    lib libapi.books;
    nc number; 
    cols number;
    ds dbms_sql.desc_tab;
    rc sys_refcursor;
begin
    open rc for select * from table (lib);
    nc := dbms_sql.to_cursor_number (rc);
    dbms_sql.describe_columns (nc, cols, ds);
    dbms_sql.close_cursor (nc);
    for i in 1..cols loop dbms_output.put_line (ds(i).col_name);
    end loop;
end;
/

Outcome:

TITLE
AUTHOR
SUBJECT
BOOK_ID

Upvotes: 2

user5507535
user5507535

Reputation: 1800

Solution using JSON_OBJECT_T:

DECLARE
  books CONSTANT JSON_OBJECT_T := JSON_OBJECT_T.parse(
  '{
    "title":   "First Book", 
    "author":  "Me",
    "subject": "Simple ones",
    "book_id": 94321
  }');

  book_keys JSON_KEY_LIST := books.get_keys;

BEGIN
  FOR i IN 1..book_keys.COUNT LOOP
     DBMS_OUTPUT.put_line( book_keys(i) || ': ' ||  books.get_string(book_keys(i)) );
  END LOOP;
END;
/

Upvotes: 0

MT0
MT0

Reputation: 167867

If you use an object created in the SQL scope rather than a record created in the PL/SQL scope then you can use ANYDATA to find out the attributes and their values.

For example, if your types are:

CREATE TYPE book is OBJECT(
  title           varchar(50),
  author          varchar(50),
  subject         varchar(100),
  book_id         number,
  first_published DATE
);

CREATE TYPE table_of_books IS TABLE OF book;

Then you can create the package:

CREATE PACKAGE reflection IS
  TYPE type_info IS RECORD(
    prec        PLS_INTEGER,
    scale       PLS_INTEGER,
    len         PLS_INTEGER,
    csid        PLS_INTEGER,
    csfrm       PLS_INTEGER,
    schema_name VARCHAR2(30),
    type_name   VARCHAR2(30),
    version     VARCHAR2(100),
    count       PLS_INTEGER
  );

  TYPE attr_info IS RECORD(
    prec           PLS_INTEGER,
    scale          PLS_INTEGER,
    len            PLS_INTEGER,
    csid           PLS_INTEGER,
    csfrm          PLS_INTEGER,
    attr_elt_type  ANYTYPE,
    aname          VARCHAR2(30)
  );

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER;

  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;

  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;
END;
/

With the package body:

CREATE PACKAGE BODY reflection IS
  DEBUG BOOLEAN := FALSE;

  FUNCTION get_type(
    p_anydata IN ANYDATA
  ) RETURN ANYTYPE
  IS
    v_typeid    PLS_INTEGER;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anydata.GetType( typ => v_anytype );
    RETURN v_anytype;
  END;

  FUNCTION get_info(
    p_anytype IN ANYTYPE
  ) RETURN type_info
  IS
    v_typeid    PLS_INTEGER;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anytype.GetInfo (
      v_type_info.prec, 
      v_type_info.scale,
      v_type_info.len, 
      v_type_info.csid,
      v_type_info.csfrm,
      v_type_info.schema_name, 
      v_type_info.type_name, 
      v_type_info.version,
      v_type_info.count
    );

    IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
      RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
    END IF;

    RETURN v_type_info;
  END;

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER
  IS
  BEGIN
    RETURN Get_Info( Get_Type( p_anydata ) ).COUNT;
  END;
  
  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata     ANYDATA := p_anydata;
    v_anytype     ANYTYPE;
    v_type_info   REFLECTION.TYPE_INFO;
    v_output      VARCHAR2(4000);
    v_attr_typeid PLS_INTEGER;
    v_attr_info   REFLECTION.ATTR_INFO;
  BEGIN
    v_anytype := Get_Type( v_anydata );
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    v_attr_typeid := v_anytype.getAttrElemInfo(
      pos            => p_index,
      prec           => v_attr_info.prec,
      scale          => v_attr_info.scale,
      len            => v_attr_info.len,
      csid           => v_attr_info.csid,
      csfrm          => v_attr_info.csfrm,
      attr_elt_type  => v_attr_info.attr_elt_type,
      aname          => v_attr_info.aname
    );
    RETURN v_attr_info.aname;
  END;
        
  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata   ANYDATA := p_anydata;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
    v_output    VARCHAR2(4000);
  BEGIN
    v_anytype := Get_Type( v_anydata );
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    
    FOR i IN 1 .. p_index LOOP
      DECLARE
        v_attr_typeid PLS_INTEGER;
        v_attr_info   REFLECTION.ATTR_INFO;
        v_result_code PLS_INTEGER;
      BEGIN
        v_attr_typeid := v_anytype.getAttrElemInfo(
          pos            => i,
          prec           => v_attr_info.prec,
          scale          => v_attr_info.scale,
          len            => v_attr_info.len,
          csid           => v_attr_info.csid,
          csfrm          => v_attr_info.csfrm,
          attr_elt_type  => v_attr_info.attr_elt_type,
          aname          => v_attr_info.aname
        );

        IF DEBUG THEN
          DBMS_OUTPUT.PUT_LINE(
            'Attribute ' || i || ': '
            || v_attr_info.aname
            || ' (type ' || v_attr_typeid || ')'
          );
        END IF;

        CASE v_attr_typeid
        WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
          DECLARE
            v_value NUMBER;
          BEGIN
            v_result_code := v_anydata.GetNumber( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value );
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
          DECLARE
            v_value VARCHAR2(4000);
          BEGIN
            v_result_code := v_anydata.GetVarchar2( v_value );
            IF i = p_index THEN
              RETURN v_value;
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_DATE THEN
          DECLARE
            v_value DATE;
          BEGIN
            v_result_code := v_anydata.GetDate( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
            END IF;
          END;
        ELSE
          NULL;
        END CASE;
      END;
    END LOOP;
    RETURN NULL;
  END;
END;
/

Then your code to get the values could be:

DECLARE
   list_of_books table_of_books;
   idx           PLS_INTEGER := 1;
   p_anydata     ANYDATA;
   p_attr_name   VARCHAR2(30);
   p_attr_value  VARCHAR2(4000);
BEGIN
  dbms_output.enable;
  list_of_books := table_of_books(
    book(
      'First book',
      'Me',
      'Simple Ones',
      94321,
      DATE '1970-01-01'
    ),
    book(
      'Second book',
      'You',
      'Intermediate Ones',
      55555,
      DATE '2020-01-01'
    ),
    book(
      'Third book',
      NULL,
      'Advanced Ones',
      77777,
      DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND
    )
  );
  
  FOR book_no IN 1 .. list_of_books.COUNT LOOP
    p_anydata := ANYDATA.ConvertObject( list_of_books(book_no) );
    DBMS_OUTPUT.PUT_LINE( 'Book ' || book_no || ':' );
    FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
      p_attr_name  := REFLECTION.get_attr_name_at( p_anydata, attr_no );
      p_attr_value := REFLECTION.get_attr_value_at( p_anydata, attr_no );
      DBMS_OUTPUT.PUT_LINE( '  ' || p_attr_name || ': ' || p_attr_value );
    END LOOP;
  END LOOP;
END;
/

Which outputs:

Book 1:
  TITLE: First book
  AUTHOR: Me
  SUBJECT: Simple Ones
  BOOK_ID: 94321
  FIRST_PUBLISHED: 1970-01-01 00:00:00
Book 2:
  TITLE: Second book
  AUTHOR: You
  SUBJECT: Intermediate Ones
  BOOK_ID: 55555
  FIRST_PUBLISHED: 2020-01-01 00:00:00
Book 3:
  TITLE: Third book
  AUTHOR: 
  SUBJECT: Advanced Ones
  BOOK_ID: 77777
  FIRST_PUBLISHED: 2099-12-31 23:59:59

db<>fiddle here

Upvotes: 3

Chris Saxon
Chris Saxon

Reputation: 9775

Short answer: You can't. There's no facility to iterate through record attributes in PL/SQL like that.

Long answer: You can use the table operator to read arrays using SQL. So you can unpivot the structure, returning the column names as rows.

To do this, you need to define the record and array either:

  • In a package spec
  • As SQL object types

Sticking with PL/SQL types gives something like:

create or replace package pkg as 

   type books is record
        (title   varchar(50)  := 'First Book'
        ,author  varchar(50)  := 'Me'
        ,subject varchar(100) := 'Simple ones'
        ,book_id number       := 94321
        );      
   type table_of_books is 
      table of books
      index by pls_integer;

end;
/

declare
   
   list_of_books pkg.table_of_books;
   cursor c is
     select * from table ( list_of_books )
     unpivot (
       val for c in ( 
         title, author, subject
       )
     );
begin
  list_of_books := pkg.table_of_books (
    1 => pkg.books ( 
      'First book', 'me', 'this', 100
    )
  );

  for current_field in c loop 
    dbms_output.put_line(current_field.c); 
  end loop;
end;
/

TITLE
AUTHOR
SUBJECT

Some notes:

  • You need to know the names of the attributes you want to become rows in the unpivot clause
  • To unpivot columns, they need to have the same data type, so you'll need a subquery to to_char everything

If you're on 19c, you could also look into converting the objects to JSON

Upvotes: 2

Related Questions