5 Replies Latest reply: Jun 19, 2013 6:45 AM by marek27 RSS

    2 dimensional array into columns

    marek27

      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
          Billy~Verreynne

          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

            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

              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

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