Forum Stats

  • 3,757,260 Users
  • 2,251,216 Discussions
  • 7,869,781 Comments

Discussions

How can I access an array in Oracle SQL?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 17 Green Ribbon
edited Aug 3, 2021 1:14AM in SQL & PL/SQL

In Oracle BI Publisher I have the following list with multiple values to select and the result is the following array:

And what I need is the moth and the position from the array, just like this.

The reference of placing Field[0] is from PostgreSQL:

source: (127) SQL Tutorial: Working with ARRAYs - YouTube


I hope you can help me, thank you.

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,570 Red Diamond

    Example:

    SQL> create or replace type TDates as table of date; 
     2 / 
     
    Type created. 
     
    SQL> 
    SQL> -- the array as a type 
    SQL> select TDates(sysdate,sysdate-1,sysdate-100) as array from dual; 
     
    ARRAY 
    ------------------------------------------------------------------
    TDATES('2021-08-03', '2021-08-02', '2021-04-25') 
     
    1 row selected. 
     
    SQL> 
    SQL> -- array as a set of rows 
    SQL> select column_value as day from table(TDates(sysdate,sysdate-1,sysdate-100)); 
     
    DAY 
    ---------- 
    2021-08-03 
    2021-08-02 
    2021-04-25 
     
    3 rows selected. 
     
    SQL> 
    SQL> var c     refcursor 
    SQL> declare 
     2         array  TDates; 
     3 begin 
     4         -- client creates array 
     5         array := new TDates(sysdate,sysdate-1,sysdate-100); 
     6  
     7         -- client constructs SQL using array as bind variable 
     8         open :c for 
     9                 'select 
     10                         owner, object_type, object_name 
     11                 from   all_objects 
     12                 where  created <= :day1 
     13                 and    last_ddl_time < :day2 
     14                 and    sysdate in(select * from table(:day_array)) 
     15                 and    rownum < 11' 
     16         using 
     17                 array(1), 
     18                 array(2), 
     19                 array; 
     20 end; 
     21 / 
     
    PL/SQL procedure successfully completed. 
     
    SQL> 
    SQL> print c 
     
    OWNER                         OBJECT_TYPE                   OBJECT_NAME 
    ------------------------------ ------------------------------ ------------------------------ 
    SYS                           INDEX                         I_FILE#_BLOCK# 
    SYS                           INDEX                         I_OBJ3 
    SYS                           INDEX                         I_TS1 
    SYS                           INDEX                         I_CON1 
    SYS                           TABLE                         IND$ 
    SYS                           TABLE                         CDEF$ 
    SYS                           CLUSTER                       C_TS# 
    SYS                           INDEX                         I_CCOL2 
    SYS                           INDEX                         I_PROXY_DATA$ 
    SYS                           INDEX                         I_CDEF4 
     
    10 rows selected.
    


    BluShadow
  • User_H3J7U
    User_H3J7U Member Posts: 460 Bronze Trophy

    I assume that the BIP parameters are not binding, but substitution. So, query to get the first value should look like this:

    with function cn (co ku$_vcnt, pos number) return varchar2 as
    begin return co(pos+1); end;
    select * from tbl where key=cn(ku$_vcnt(:param),0);