How do I create an heterogeneous iterable data structure in PL/SQL? — oracle-tech

    Forum Stats

  • 3,701,864 Users
  • 2,239,504 Discussions
  • 7,835,593 Comments

Discussions

How do I create an heterogeneous iterable data structure in PL/SQL?

User_CTJPXUser_CTJPX Posts: 3 Newbie
edited November 20 in SQL & PL/SQL

I'm looking for a way to define a data structure in Oracle PL/SQL with heterogeneous data and where I'm able to iterate the values.

My use case is that I receive a bunch of values from a Resource Template and have to process them and insert them in a table.

I would like to have something like this:

DECLARE
   TYPE books IS RECORD
      (title  varchar( 50) := myvar1,
      author  varchar( 50) := myvar2,
      subject varchar(100) := myvar3,
      book_id number       := myvar4);
BEGIN
  FOR item IN ('SELECT * FROM books')
  LOOP
    -- Here I would access each individual item key and value (title, author...)
    my_function(item.key, item.value)
  END LOOP;
END;

Note that in the example above I want to iterate the fields (columns), not records, there's a single record.

So I want something very similar to an object in Javascript:

var obj = {
  title: 'Romeo and Juliet',
  author: 'Some dude',
  subject: 'Romance',
  book_id: 332764
};

for (const prop in obj) {
  console.log(`obj.${prop} = ${obj[prop]}`);
}

This is for Oracle 19c.

Is there a way to achieve this in a simple way?

Note that I'm using RECORD in the example, but it could be something else.

This was also asked in StackOverflow, but there were no answers.

Answers

  • BEDEBEDE Oracle Developer Posts: 2,101 Silver Trophy

    If you want it to be like JavaScript, use JSON. See

    https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246

    Although it's best to use relational tables. I mean that's how SQL performs at its best.

  • Billy VerreynneBilly Verreynne Posts: 27,730 Red Diamond
    edited November 20

    Not supported by default.

    To dynamically reference a SQL cursor projection in PL/SQL requires the use of the DBMS_SQL interface.

    Here is a simplistic example of turning a cursor row fetch output into an associative array:

    create or replace package PL_Types as 
           type TColumns is table of varchar2(4000) index by varchar2(30); 
    end; 
    / 
    show errors 
     
    create or replace function CursorRowIterate( c integer ) return PL_Types.TColumns is 
           colCnt integer; 
           colList DBMS_SQL.DESC_TAB3; 
           rc     number; 
           colVal varchar2(4000); 
           cols   PL_Types.TColumns; 
    begin 
           DBMS_SQL.describe_columns3( c, colCnt, colList ); 
     
           for i in 1..colCnt loop 
                   DBMS_SQL.define_column( c, i, colVal, 4000 ); 
           end loop; 
     
           rc := DBMS_SQL.fetch_rows( c ); 
     
           for i in 1..colCnt loop 
                   DBMS_SQL.column_value( c, i, colVal ); 
                   cols( colList(i).col_name ) := colVal; 
           end loop; 
           return( cols ); 
    end; 
    / 
    show errors
    

    It treats all columns in the SQL projection as strings. Nor does it provide an indication that there are no more rows to fetch.

    Here is it in action:

    SQL> declare 
     2         cur    sys_refcursor; 
     3         c      integer; 
     4  
     5         procedure IterateRow is 
     6                 cols   PL_Types.TColumns; 
     7                 name   varchar2(30); 
     8         begin 
     9                 dbms_output.put_line('==[row]============================'); 
     10                 cols := CursorRowIterate(c); 
     11                 name := cols.First(); 
     12                 while cols.Exists(name) loop 
     13                         dbms_output.put_line( 
     14                                 'column='||name|| 
     15                                 ' value='||cols(name) 
     16                         ); 
     17                         name := cols.Next(name); 
     18                 end loop; 
     19         end; 
     20  
     21 begin 
     22         open cur for select * from emp where rownum <= 2; 
     23         c := DBMS_SQL.to_cursor_number( cur ); 
     24  
     25         IterateRow; 
     26         IterateRow; 
     27         IterateRow; 
     28 end; 
     29 / 
    ==[row]============================ 
    column=COMM value= 
    column=DEPTNO value=20 
    column=EMPNO value=7369 
    column=ENAME value=SMITH 
    column=HIREDATE value=1980/12/17 00:00:00 
    column=JOB value=CLERK 
    column=MGR value=7902 
    column=SAL value=800 
    ==[row]============================ 
    column=COMM value=300 
    column=DEPTNO value=30 
    column=EMPNO value=7499 
    column=ENAME value=ALLEN 
    column=HIREDATE value=1981/02/20 00:00:00 
    column=JOB value=SALESMAN 
    column=MGR value=7698 
    column=SAL value=1600 
    ==[row]============================ 
    column=COMM value= 
    column=DEPTNO value= 
    column=EMPNO value= 
    column=ENAME value= 
    column=HIREDATE value= 
    column=JOB value= 
    column=MGR value= 
    column=SAL value= 
     
    PL/SQL procedure successfully completed. 
    
    


    You can also use it for single row adhoc structures via SQLs like:

    select 'abc' as NAME, 123 as ID, sysdate as DAY, 'foo' as BAR from dual
    


  • PaulzipPaulzip Posts: 7,909 Gold Crown
    edited November 20

    I wrote something similar to Billy's offering a while ago for a similar task and like you want.

    create or replace package P_KeyValues as
      type TKeyValue is record (
        Key    varchar2(128 byte)
      , Value  varchar2(4000 byte)
      , RecNo  integer  
      , ColPos integer  
      ); 
      type TKeyValues is table of TKeyValue;
      function ToKeyValues(pSQL varchar2, pMaxCurRows integer default null) return TKeyValues pipelined;   
    end; 
    /
    
    
    create or replace package body P_KeyValues as 
    
    
    function ToKeyValues(pSQL varchar2, pMaxCurRows integer default null) return TKeyValues pipelined is
      vCur integer;
      vColCount integer; 
      vColList dbms_sql.desc_tab3; 
      vColVal varchar2(4000); 
      vResult TKeyValue;
      vRows integer;
      vRecNo integer := 1;
    begin
      vCur := dbms_sql.open_cursor;
      begin
        dbms_sql.parse(vCur, pSQL, dbms_sql.native);  
        dbms_sql.describe_columns3(vCur, vColCount, vColList);    
        for i in 1..vColCount 
        loop 
          dbms_sql.define_column(vCur, i, vColVal, 4000); 
        end loop; 
        vRows := dbms_sql.execute(vCur);  
        while dbms_sql.fetch_rows(vCur) > 0
        loop
          for i in 1..vColCount 
          loop
            dbms_sql.column_value(vCur, i, vColVal);        
            vResult.Key    := vColList(i).col_name; 
            vResult.Value  := vColVal;
            vResult.RecNo  := vRecNo;
            vResult.ColPos := i;     
            pipe row(vResult); 
          end loop;
          vRecNo := vRecNo + 1;
          exit when vRecNo > pMaxCurRows;      
        end loop;
        dbms_sql.close_cursor(vCur);
      exception
        when OTHERS then
          dbms_sql.close_cursor(vCur);
          raise;
      end; 
      return; 
    end;
    
    
    end; 
    /
    
    
    
    select *
    from table(P_KeyValues.ToKeyValues('select * from all_objects where rownum < 2'));
    
    KEY                           |VALUE                         |     RECNO|    COLPOS
    ------------------------------|------------------------------|----------|----------
    OWNER                         |SYS                           |         1|         1
    OBJECT_NAME                   |I_FILE#_BLOCK#                |         1|         2
    SUBOBJECT_NAME                |                              |         1|         3
    OBJECT_ID                     |9                             |         1|         4
    DATA_OBJECT_ID                |9                             |         1|         5
    OBJECT_TYPE                   |INDEX                         |         1|         6
    CREATED                       |04/02/2019 04:45:20           |         1|         7
    LAST_DDL_TIME                 |04/02/2019 04:45:20           |         1|         8
    TIMESTAMP                     |2019-02-04:04:45:20           |         1|         9
    STATUS                        |VALID                         |         1|        10
    TEMPORARY                     |N                             |         1|        11
    GENERATED                     |N                             |         1|        12
    SECONDARY                     |N                             |         1|        13
    NAMESPACE                     |4                             |         1|        14
    EDITION_NAME                  |                              |         1|        15
    SHARING                       |NONE                          |         1|        16
    EDITIONABLE                   |                              |         1|        17
    ORACLE_MAINTAINED             |Y                             |         1|        18
    APPLICATION                   |N                             |         1|        19
    DEFAULT_COLLATION             |                              |         1|        20
    DUPLICATED                    |N                             |         1|        21
    SHARDED                       |N                             |         1|        22
    CREATED_APPID                 |                              |         1|        23
    CREATED_VSNID                 |                              |         1|        24
    MODIFIED_APPID                |                              |         1|        25
    MODIFIED_VSNID                |                              |         1|        26
    OWNER                         |SYS                           |         2|         1
    OBJECT_NAME                   |I_OBJ3                        |         2|         2
    SUBOBJECT_NAME                |                              |         2|         3
    OBJECT_ID                     |38                            |         2|         4
    DATA_OBJECT_ID                |38                            |         2|         5
    OBJECT_TYPE                   |INDEX                         |         2|         6
    CREATED                       |04/02/2019 04:45:20           |         2|         7
    LAST_DDL_TIME                 |04/02/2019 04:45:20           |         2|         8
    TIMESTAMP                     |2019-02-04:04:45:20           |         2|         9
    STATUS                        |VALID                         |         2|        10
    TEMPORARY                     |N                             |         2|        11
    GENERATED                     |N                             |         2|        12
    SECONDARY                     |N                             |         2|        13
    NAMESPACE                     |4                             |         2|        14
    EDITION_NAME                  |                              |         2|        15
    SHARING                       |NONE                          |         2|        16
    EDITIONABLE                   |                              |         2|        17
    ORACLE_MAINTAINED             |Y                             |         2|        18
    APPLICATION                   |N                             |         2|        19
    DEFAULT_COLLATION             |                              |         2|        20
    DUPLICATED                    |N                             |         2|        21
    SHARDED                       |N                             |         2|        22
    CREATED_APPID                 |                              |         2|        23
    CREATED_VSNID                 |                              |         2|        24
    MODIFIED_APPID                |                              |         2|        25
    MODIFIED_VSNID                |                              |         2|        26
    
    

    You could easily plug this into your requirements...

    BEGIN
      FOR item IN (select * from table(P_KeyValues.ToKeyValues('SELECT * FROM books')))
      LOOP
        my_function(item.key, item.value)
      END LOOP;
    END;
    


    Billy Verreynne
Sign In or Register to comment.