3 Replies Latest reply: Aug 3, 2010 9:51 AM by BluShadow RSS

    Piping dynamic number of columns

    Z?
      Hi

      Following on from this thread...

      How to pipeline a function with a dynamic number of columns?

      I have really hit a brick wall and am hoping for some inspiration!

      My requirement is to return a delimited string as a record with each value in it's own column. Altough I have other ways of doing this, the method I am stuck with has now become more of an intellectual exercise!

      This method uses the Oracle Data Cartridge Interface, specifically ODCITablePrepare, ODCITableFetch, ODCITableStart and ODCITableDescribe. The problem stems from the fact that ODCITableDescribe can only accept constants as parameters, making it difficult to derive the number of columns at run time. I have followed Solomon's approach of setting the number of coumns using SYS_CONTEXT and then referring to this within ODCITableDescribe.

      This is working fine, but I still have to set the context - then run the query (I may as well still be just banging a constant in!). So what I need to do is set the context either within a wrapper function, somehow within the type or in a way that I have not yet thought of.

      Version is (no idea if it works in 11g)...
      SELECT * FROM v$version;
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
      PL/SQL Release 10.2.0.4.0 - Production
      CORE    10.2.0.4.0      Production
      TNS for Solaris: Version 10.2.0.4.0 - Production
      NLSRTL Version 10.2.0.4.0 - Production
      Here is the script - apologies for the large amount of code here...
      CREATE OR REPLACE CONTEXT dyncolpipectx 
      USING dyncolpipe_ctx;
      /
      
      CREATE OR REPLACE PACKAGE dyncolpipe_ctx
      AS
        PROCEDURE set_dyncolpipe_ctx(p_cols IN NUMBER);
        END dyncolpipe_ctx;
      /
      
      CREATE OR REPLACE PACKAGE BODY dyncolpipe_ctx
      AS
         PROCEDURE set_dyncolpipe_ctx(p_cols IN NUMBER)
         IS
      
         BEGIN
      
            DBMS_SESSION.set_context('dyncolpipectx', 'columns', p_cols);
      
         END set_dyncolpipe_ctx;
      
      END dyncolpipe_ctx;
      /
      
      CREATE OR REPLACE TYPE dynColPipe AS 
      OBJECT(
             fetch_more     VARCHAR2(1),
             tmt            SYS.ANYTYPE,
             g_string       VARCHAR2(4000),
             g_delim        VARCHAR2(1),
             g_numCols      NUMBER, 
             rows_returned  NUMBER,
             STATIC FUNCTION ODCITableStart(
                                            sctx    IN OUT dyncolpipe,
                                            p_str   IN     VARCHAR2,
                                            p_delim IN     VARCHAR2
                                            ) 
                    RETURN PLS_INTEGER,
                    
             STATIC FUNCTION ODCITablePrepare(
                                              sctx    OUT dyncolpipe,
                                              tf_info IN  SYS.ODCITabFuncInfo,
                                              p_str   IN  VARCHAR2,
                                              p_delim IN  VARCHAR2
                                              ) 
                    RETURN PLS_INTEGER,
                    
             MEMBER FUNCTION ODCITableFetch(
                                            self   IN OUT dyncolpipe,
                                            nrows  IN     NUMBER,
                                            objSet OUT    SYS.ANYDATASET
                                            )
                    RETURN PLS_INTEGER,
                    
             MEMBER FUNCTION ODCITableClose(
                                            self IN dyncolpipe
                                            ) 
                    RETURN PLS_INTEGER,
                    
             STATIC FUNCTION ODCITableDescribe(
                                               rtype   OUT SYS.ANYTYPE,
                                               p_str   IN  VARCHAR2,
                                               p_delim IN  VARCHAR2
                                               ) 
                    RETURN PLS_INTEGER
                   
             );
      / 
      
      
      CREATE OR REPLACE TYPE BODY dyncolpipe
      IS
      
        STATIC FUNCTION ODCITableStart(
                                       sctx    IN OUT dyncolpipe,
                                       p_str   IN     VARCHAR2,
                                       p_delim IN     VARCHAR2
                                       ) RETURN PLS_INTEGER
        IS
              
          BEGIN
          
            RETURN ODCICONST.SUCCESS;
              
          END;
      
        STATIC FUNCTION ODCITablePrepare(
                                         sctx    OUT dyncolpipe,
                                         tf_info IN  SYS.ODCITabFuncInfo,
                                         p_str   IN  VARCHAR2,
                                         p_delim IN  VARCHAR2
                                         ) RETURN PLS_INTEGER
        IS    
        
          rtn       NUMBER;
          prec      PLS_INTEGER;
          scale     PLS_INTEGER;
          len       PLS_INTEGER;
          csid      PLS_INTEGER;
          csfrm     PLS_INTEGER;
          elem_typ  SYS.ANYTYPE;    
          aname     VARCHAR2(30);
      
          l_numCols PLS_INTEGER;
      
          BEGIN
                
            SELECT LENGTH(p_str) - LENGTH(REPLACE(p_str, p_delim, NULL)) - 1
            INTO   l_numCols
            FROM   dual;
                
            rtn := tf_info.RetType.GetAttreleminfo(
                                                   1,
                                                   prec,
                                                   scale,
                                                   len,
                                                   csid,
                                                   csfrm,
                                                   elem_typ,
                                                   aname
                                                   );                                   
                                                     
            sctx:= dyncolpipe('Y',elem_typ, p_str, p_delim, l_numCols, 0);
             
            RETURN ODCICONST.SUCCESS;    
              
          END;
      
        MEMBER FUNCTION ODCITableFetch(
                                       self   IN OUT dyncolpipe,
                                       nrows  IN     NUMBER,
                                       objSet OUT    SYS.ANYDATASET
                                       ) RETURN PLS_INTEGER
        IS
          
          elem_typ     SYS.ANYTYPE;
          
          l_numCols    INTEGER;
          l_string     VARCHAR2(4000);
          l_delim      VARCHAR2(1);
          l_curCol     INTEGER;
          l_char       VARCHAR2(1);
          l_element    VARCHAR2(4000);
          l_loopStart  INTEGER;
                  
          BEGIN
                
            SYS.ANYDATASET.BeginCreate(
                                       SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                       tmt,
                                       objSet
                                       );
            IF fetch_more = 'Y' THEN
            
              fetch_more  := 'N';
              
              l_numCols   := g_numCols;
              l_string    := g_string;
              l_delim     := g_delim;
              l_loopStart := 2;
              l_curCol    := 1;
              
              objSet.Addinstance;          
              objSet.PieceWise;         
      
              FOR i IN l_loopStart .. LENGTH(l_string)
              LOOP
      
                l_char := SUBSTR(l_string,i,1);
      
                IF l_char = l_delim THEN
      
                  objSet.SetVarchar2(
                                     l_element,
                                     CASE WHEN l_curCol = l_numCols
                                          THEN TRUE
                                          ELSE FALSE
                                     END
                                     );        
                                          
                  l_curCol := l_curCol + 1;             
      
                  l_element := NULL;
      
                ELSE
      
                  l_element := l_element||l_char;
      
                END IF;
      
              END LOOP;
                
              objSet.Endcreate;            
                
            ELSE
                
              objSet:= NULL;
                
            END IF;
                
            RETURN ODCICONST.SUCCESS;
            
          END;
      
        MEMBER FUNCTION ODCITableClose(
                                       self IN dyncolpipe
                                       ) RETURN PLS_INTEGER
        IS
              
          t_id NUMBER;
              
          BEGIN
      
            SELECT object_id
            INTO   t_id
            FROM   user_objects
            WHERE  object_name = 'DYNCOLPIPE'                         
            AND    object_type = 'TYPE BODY';                              
                
            DBMS_UTILITY.invalidate( t_id );
      
            RETURN odciconst.success;
      
          END;
      
        STATIC FUNCTION ODCITableDescribe(
                                          rtype   OUT SYS.ANYTYPE,
                                          p_str   IN  VARCHAR2,
                                          p_delim IN  VARCHAR2
                                          ) RETURN PLS_INTEGER
        IS
        
          mt       SYS.ANYTYPE;
          tmt      SYS.ANYTYPE;
                  
          l_numCols INTEGER;
                  
          BEGIN
      
            l_numCols := SYS_CONTEXT('dyncolpipectx','columns');
      
            SYS.ANYTYPE.BeginCreate(
                                    SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                    mt
                                    );    
           
            FOR i IN 1..l_numCols 
            LOOP
            
              mt.AddAttr(
                         'C' || i,
                         SYS.DBMS_TYPES.TYPECODE_VARCHAR2,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL
                         );
              
            END LOOP;  
            
            mt.EndCreate;
            
            SYS.ANYTYPE.BeginCreate(
                                    SYS.DBMS_TYPES.TYPECODE_NAMEDCOLLECTION,
                                    tmt
                                    );    
            
            tmt.SetInfo(
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        mt,
                        DBMS_TYPES.TYPECODE_OBJECT,
                        0
                        );
            
            tmt.EndCreate;
            
            rtype := tmt;
            
            RETURN ODCICONST.SUCCESS;
            
          END;
      
      END;
      / 
       
      CREATE OR REPLACE FUNCTION dynamic_pipe(p_str   VARCHAR2, 
                                              p_delim VARCHAR2) 
                        RETURN ANYDATASET PIPELINED
                        USING  dyncolpipe;
      / 
      The following can then be run and the output will be as desired.
      EXEC dyncolpipe_ctx.set_dyncolpipe_ctx(7);
      
      WITH my_table AS
      (
       SELECT  '#ab#c#def#h#ij#klm#nop#' str
       FROM    dual
       )
      SELECT src.*
      FROM   my_table, 
             TABLE(dynamic_pipe(str, '#')) src;
      Any help would be greatly appreciated!

      Cheers

      Ben
        • 1. Re: Piping dynamic number of columns
          BluShadow
          Munky wrote:
          The following can then be run and the output will be as desired.
          EXEC dyncolpipe_ctx.set_dyncolpipe_ctx(7);
          
          WITH my_table AS
          (
          SELECT  '#ab#c#def#h#ij#klm#nop#' str
          FROM    dual
          )
          SELECT src.*
          FROM   my_table, 
          TABLE(dynamic_pipe(str, '#')) src;
          As I said on the other thread it can't be done.

          At the time the parser comes to prepare the dynamic pipelined table (DPT) it needs to know the input of the arguments, but that happens before any data is actually fetched as part of the query, so you can't use data from within the query itself to prepare the DPT because it's a chicken and egg situation... each is dependant on the other. Hence why Oracle stipulates that the arguments need to be constants otherwise they will default to NULL.

          It's exactly the same principle if you use XMLTABLE... e.g.
          SQL> select * from xmltable('1,2,3');
          
          COLUMN_VALUE
          --------------------------------------------------
          1
          2
          3
          
          SQL> with t as (select '1,2,3' as vals from dual)
            2  select * from t, xmltable(vals);
          select * from t, xmltable(vals)
                                    *
          ERROR at line 2:
          ORA-19102: XQuery string literal expected
          
          
          SQL>
          • 2. Re: Piping dynamic number of columns
            MichaelS
            It's exactly the same principle if you use XMLTABLE... e.g.
            It's not: Not in 11gR2 at least ;) :
            SQL> select * from v$version where rownum = 1
            /
            BANNER                                                                          
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
            1 row selected.
            
            SQL> with t as (
             select '1,2,3' as vals from dual
            )
            --
            --
            select * from t, xmltable((vals))
            /
            VALS  COLUMN_VALUE
            ----- ------------
            1,2,3 1           
            1,2,3 2           
            1,2,3 3           
            
            3 rows selected.
            • 3. Re: Piping dynamic number of columns
              BluShadow
              michaels2 wrote:
              It's exactly the same principle if you use XMLTABLE... e.g.
              It's not: Not in 11gR2 at least ;) :
              SQL> select * from v$version where rownum = 1
              /
              BANNER                                                                          
              --------------------------------------------------------------------------------
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
              1 row selected.
              
              SQL> with t as (
              select '1,2,3' as vals from dual
              )
              --
              --
              select * from t, xmltable((vals))
              /
              VALS  COLUMN_VALUE
              ----- ------------
              1,2,3 1           
              1,2,3 2           
              1,2,3 3           
              
              3 rows selected.
              Ooooo! :) They've changed it.