This discussion is archived
5 Replies Latest reply: Jun 19, 2013 4:45 AM by marek27 RSS

2 dimensional array into columns

marek27 Newbie
Currently Being Moderated

Hi,

my function returns 2-dimensional array: array, in which each array has same length (but it is dynamic) and I want to transform it into columns. how is this possible?

 

for ex:

i have: array_2d( (array_1d(1,2), array_1d(3,4), array_1d(5,6) )

and i want: select * from xxxxx, that return:

col1col2
12
34
56
  • 2. Re: 2 dimensional array into columns
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    Here is one such approach:

     

    SQL> create or replace type TNumberArray is table of number;
      2  /
    Type created.
    SQL> 
    SQL> create or replace type T2DArray is table of TNumberArray;
      2  /
    Type created.
    SQL> 
    SQL> create or replace function DynamicNumbers( cells integer ) return T2DArray pipelined is
      2          cursor c is select object_id from all_objects;
      3          array   TNumberArray;
      4  begin
      5          open c;
      6          loop
      7                  fetch c bulk collect into array limit cells;
      8                  pipe row( array );
      9                  exit when c%NotFound;
     10          end loop;
     11          close c;
     12  end;
     13  /
    Function created.
    SQL> 
    SQL> select * from table(DynamicNumbers(10)) where rownum < 11;
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    TNUMBERARRAY(100, 116, 117, 280, 365, 367, 368, 370, 371, 373)
    TNUMBERARRAY(1081, 1253, 1255, 1256, 1276, 1277, 1280, 1282, 1284, 1286)
    TNUMBERARRAY(1288, 1290, 1292, 1294, 1296, 1298, 1300, 1302, 1304, 1306)
    TNUMBERARRAY(1308, 1310, 1312, 1314, 1316, 1318, 1320, 1322, 1324, 1326)
    TNUMBERARRAY(1328, 1329, 1330, 1332, 1334, 1336, 1338, 1340, 1342, 1344)
    TNUMBERARRAY(1346, 1348, 1350, 1352, 1354, 1356, 1358, 1360, 1362, 1364)
    TNUMBERARRAY(1366, 1368, 1370, 1372, 1374, 1376, 1378, 1380, 1382, 1384)
    TNUMBERARRAY(1386, 1388, 1390, 1392, 1394, 1396, 1397, 1398, 1399, 1400)
    TNUMBERARRAY(1401, 1402, 1404, 1406, 1408, 1410, 1412, 1414, 1416, 1418)
    TNUMBERARRAY(1420, 1422, 1424, 1426, 1428, 1430, 1432, 1434, 1436, 1438)
    10 rows selected.
    SQL> 
    SQL> select * from table(DynamicNumbers(4)) where rownum < 11;
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    TNUMBERARRAY(100, 116, 117, 280)
    TNUMBERARRAY(365, 367, 368, 370)
    TNUMBERARRAY(371, 373, 1081, 1253)
    TNUMBERARRAY(1255, 1256, 1276, 1277)
    TNUMBERARRAY(1280, 1282, 1284, 1286)
    TNUMBERARRAY(1288, 1290, 1292, 1294)
    TNUMBERARRAY(1296, 1298, 1300, 1302)
    TNUMBERARRAY(1304, 1306, 1308, 1310)
    TNUMBERARRAY(1312, 1314, 1316, 1318)
    TNUMBERARRAY(1320, 1322, 1324, 1326)
    10 rows selected.
    SQL> 
  • 3. Re: 2 dimensional array into columns
    marek27 Newbie
    Currently Being Moderated

    thanks for reply. actually I have it like your post, but I want some select, which return resultset of columns with values, not one column of arrays.

  • 4. Re: 2 dimensional array into columns
    ascheffer Expert
    Currently Being Moderated

    Using mdsys.edge_array as a 2D array of numbers this will do:

     

    create or replace type NColPipe as object
    (
      l_cols integer,
      l_2d mdsys.sdo_edge_array,
      ret_type anytype,      -- The return type of the table function
      rows_returned number,  -- The number of rows currently returned by the table function
      static function ODCITableDescribe( rtype out anytype, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number,
      static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number,
      static function ODCITableStart( sctx in out NColPipe, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number,
      member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
      return number,
      member function ODCITableClose( self in NColPipe )
      return number,
      static function show( p_cols integer, p_2d mdsys.sdo_edge_array )
      return anydataset pipelined using NColPipe
    );
    /
    create or replace type body NColPipe as
      static function ODCITableDescribe( rtype out anytype, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number
      is
        atyp anytype;
      begin
        anytype.begincreate( dbms_types.typecode_object, atyp );
        for i in 1 .. p_cols
        loop
          atyp.addattr( 'COL' || i
                      , dbms_types.typecode_number
                      , 10
                      , 0
                      , null
                      , null
                      , null
                      );
        end loop;
        atyp.endcreate;
        anytype.begincreate( dbms_types.typecode_table, rtype );
        rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
        rtype.endcreate();
        return odciconst.success;
      exception
        when others then
          return odciconst.error;
      end;
    --
      static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number
      is
        elem_typ sys.anytype;
        prec pls_integer;
        scale pls_integer;
        len pls_integer;
        csid pls_integer;
        csfrm pls_integer;
        tc pls_integer;
        aname varchar2(30);
      begin
        tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
        sctx := NColPipe( p_cols, p_2d, elem_typ, 0 );
        return odciconst.success;
      end;
    --
      static function ODCITableStart( sctx in out NColPipe, p_cols integer, p_2d mdsys.sdo_edge_array )
      return number
      is
      begin
        return odciconst.success;
      end;
    --
      member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
      return number
      is
        l_row mdsys.sdo_number_array;
      begin
        anydataset.begincreate( dbms_types.typecode_object, ret_type, outset );
        if l_2d is not null
           and l_2d.exists( l_2d.first + rows_returned )
        then
          l_row := l_2d( l_2d.first + rows_returned );
          outset.addinstance;
          outset.piecewise();
          for i in 1 .. l_cols
          loop
            outset.setnumber( l_row( l_row.first + i - 1 ) );
          end loop;
        end if;
        outset.endcreate;
        rows_returned := rows_returned + 1;
        return odciconst.success;
      end;
    --
      member function ODCITableClose( self in NColPipe )
      return number
      is
      begin
        return odciconst.success;
      end;
    end;
    /
    select *
    from table( NColPipe.show( 2, sdo_edge_array( sdo_number_array( 3,4 ), sdo_number_array( 13,14 ) ) ) )
    /
    select *
    from table( NColPipe.show( 3, sdo_edge_array( sdo_number_array( 3,4,5 ), sdo_number_array( 13,14,15 ) ) ) )
    /
  • 5. Re: 2 dimensional array into columns
    marek27 Newbie
    Currently Being Moderated

    thank you, is possible something like this for varchar values?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points