1 2 Previous Next 27 Replies Latest reply: Feb 26, 2013 7:26 AM by BluShadow Go to original post RSS
      • 15. Re: How to pipeline a function with a dynamic number of columns?
        431127
        Thank you SOOOOO much, Blu!!! I will give it a try!

        Janel
        • 16. Re: How to pipeline a function with a dynamic number of columns?
          Solomon Yakobson
          Billy Verreynne wrote:
          Fricken excellent! I rate this the best new stuff I've read here in a very long time. Thanks. I love being educated on new things in Oracle.

          Those static functions are the actual RTTI interface I was refering too. I had no idea Oracle exposed those now. Hmm.. this opens all kinds of interesting possibilities... :-)
          Well, initially I was thrilled too. But reality is RTTI possibilities are quite limited. It does not react to variables passed as parameters, since ODCITableDescribe is called at statement parse time and is only passed literal parameters values (non-literal parameters are passed to ODCITableDescribe as NULLs). At some point, before I got a clear understanding of ODCITableDescribe I was trying to fool it using context:
          CREATE OR REPLACE
            PROCEDURE set_natural_vector_ctx(
                                             p_size in positive
                                            )
              IS
              BEGIN
                  dbms_session.set_context(
                                           'natural_vector',
                                           'size',
                                           p_size
                                          );
          END;
          /
          CREATE CONTEXT natural_vector
            USING set_natural_vector_ctx
          /
          CREATE OR REPLACE
            TYPE  natural_vector_type
              AUTHID CURRENT_USER
              AS OBJECT(
                        fetch_more varchar2(1),
                        tmt        SYS.ANYTYPE,
                        STATIC FUNCTION ODCITableStart(
                                                       sctx  IN OUT natural_vector_type,
                                                       dummy IN     INTEGER
                                                      ) RETURN PLS_INTEGER,
                        STATIC FUNCTION ODCITablePrepare(
                                                         sctx    OUT natural_vector_type,
                                                         tf_info IN  SYS.ODCITabFuncInfo,
                                                         dummy   IN  INTEGER
                                                        ) RETURN PLS_INTEGER,
                        MEMBER FUNCTION ODCITableFetch(
                                                       self   IN OUT natural_vector_type,
                                                       nrows  IN     NUMBER,
                                                       objSet OUT    SYS.ANYDATASET
                                                      ) RETURN PLS_INTEGER,
                        MEMBER FUNCTION ODCITableClose(
                                                       self IN natural_vector_type
                                                      ) RETURN PLS_INTEGER,
                        STATIC FUNCTION ODCITableDescribe(
                                                          rtype OUT SYS.ANYTYPE,
                                                          dummy IN  INTEGER
                                                         ) RETURN PLS_INTEGER
                       )
          /
          CREATE OR REPLACE
            TYPE BODY natural_vector_type
              IS
                STATIC FUNCTION ODCITableStart(
                                               sctx  IN OUT natural_vector_type,
                                               dummy IN     INTEGER
                                              ) RETURN PLS_INTEGER
                  IS
                  BEGIN
                      RETURN ODCICONST.SUCCESS;
                END;
                STATIC FUNCTION ODCITablePrepare(
                                                 sctx    OUT natural_vector_type,
                                                 tf_info IN  SYS.ODCITabFuncInfo,
                                                 dummy   IN  INTEGER
                                                ) 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) ;
                  BEGIN
                      rtn:=tf_info.RetType.GetAttreleminfo(
                                                           1,
                                                           prec,
                                                           scale,
                                                           len,
                                                           csid,
                                                           csfrm,
                                                           elem_typ,
                                                           aname
                                                          );
                      sctx:= natural_vector_type('Y',elem_typ);
                      RETURN ODCICONST.SUCCESS;    
                END;
                MEMBER FUNCTION ODCITableFetch(
                                               self   IN OUT natural_vector_type,
                                               nrows  IN     NUMBER,
                                               objSet OUT    SYS.ANYDATASET
                                             ) RETURN PLS_INTEGER
                  IS
                      elem_typ SYS.ANYTYPE;
                      num_cols INTEGER;
                  BEGIN
                      SYS.ANYDATASET.BeginCreate(
                                                 SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                                 tmt,
                                                 objSet
                                                );
                      IF fetch_more = 'Y'
                        THEN
                          fetch_more := 'N';
                          num_cols := SYS_CONTEXT('natural_vector',
                                                  'size'
                                                 );
                          objSet.Addinstance;          
                          objSet.PieceWise;
                          FOR i IN 1..num_cols LOOP  
                            objSet.SetNumber(
                                             i,
                                             CASE
                                               WHEN i = num_cols
                                                 THEN
                                                   TRUE
                                                 ELSE
                                                   FALSE
                                             END
                                            );              
                          END LOOP;           
                          objSet.Endcreate;            
                        ELSE
                          objSet:=null;
                      END IF;
                      RETURN ODCICONST.SUCCESS;
                END;
                MEMBER FUNCTION ODCITableClose(
                                               self IN natural_vector_type
                                              ) RETURN PLS_INTEGER
                  IS
                  BEGIN
                      RETURN ODCICONST.SUCCESS;
                END;
                STATIC FUNCTION ODCITableDescribe(
                                                  rtype OUT SYS.ANYTYPE,
                                                  dummy IN  INTEGER
                                                 ) RETURN PLS_INTEGER
                  IS
                      mt SYS.ANYTYPE;
                      tmt SYS.ANYTYPE;
                      num_cols INTEGER;
                  BEGIN
                      num_cols := SYS_CONTEXT('natural_vector',
                                              'size'
                                             );
                      SYS.ANYTYPE.BeginCreate(
                                              SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                              mt
                                             );    
                      FOR i IN 1..num_cols LOOP
                        mt.AddAttr(
                                   'C' || i,
                                   SYS.DBMS_TYPES.TYPECODE_NUMBER,
                                   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 natural_vector(dummy INTEGER) RETURN ANYDATASET
              PIPELINED
                USING natural_vector_type;
          /
          Now:
          SQL> EXEC set_natural_vector_ctx(5);
          
          PL/SQL procedure successfully completed.
          
          SQL> select * from TABLE(natural_vector(0));
          
           C1         C2         C3         C4         C5
          --- ---------- ---------- ---------- ----------
            1          2          3          4          5
          But look what happens if we change context variable value:
          SQL> EXEC set_natural_vector_ctx(3);
          
          PL/SQL procedure successfully completed.
          
          SQL> select * from TABLE(natural_vector(0));
          select * from TABLE(natural_vector(0))
          *
          ERROR at line 1:
          ORA-22625: OCIAnyData is not well-formed
          ORA-06512: at "SYS.ANYDATASET", line 109
          ORA-06512: at "SCOTT.NATURAL_VECTOR_TYPE", line 73
          Why? Because select * from TABLE(natural_vector(0)) was already parsed and is sitting in shared pool and therefore ODCITableDescribe will not be called. If we change at least case of one letter in the statement:
          SQL> Select * from TABLE(natural_vector(0));
          
           C1         C2         C3
          --- ---------- ----------
            1          2          3
          
          SQL> 
          Now, statement is not found in shared pool and therefore is parsed causing ODCITableDescribe to be called. Same will happen if we flush shared pool:
          SQL> EXEC set_natural_vector_ctx(7);
          
          PL/SQL procedure successfully completed.
          
          SQL> Select * from TABLE(natural_vector(0));
          Select * from TABLE(natural_vector(0))
          *
          ERROR at line 1:
          ORA-22626: Type Mismatch while constructing or accessing OCIAnyData
          ORA-06512: at "SYS.ANYDATASET", line 18
          ORA-06512: at "SCOTT.NATURAL_VECTOR_TYPE", line 64
          
          
          SQL> alter system flush shared_pool
            2  /
          
          System altered.
          
          SQL> Select * from TABLE(natural_vector(0));
          
           C1         C2         C3         C4         C5         C6         C7
          --- ---------- ---------- ---------- ---------- ---------- ----------
            1          2          3          4          5          6          7
          
          SQL> 
          Now see what happens when we pass variables as table function parameters. I will use ascheffer's code since mine is context based and disregards parameter:
          SQL> variable n number
          SQL> exec :n := 6;
          
          PL/SQL procedure successfully completed.
          
          SQL> select * from table( NColPipe.show( 'test', :n ) );
          select * from table( NColPipe.show( 'test', :n ) )
          *
          ERROR at line 1:
          ORA-06502: PL/SQL: numeric or value error
          ORA-06512: at "SCOTT.NCOLPIPE", line 101
          What happened? Statement is parsed and ODCITableDescribe is called. Since :n is not a literal, a NULL is passed to ODCITableDescribe instead, so code does not know how many columns to create. Anyway, as you can see we are in catch 22 situation. We can run a statement that returns any number of rows/columns we want, but we can only supply that number statically as literal.

          SY.
          • 17. Re: How to pipeline a function with a dynamic number of columns?
            431127
            Hi Blu!

            This worked! However, I am now running in to the problem of my sql statement being over 4,000 characters. Do you know if I can use a clob instead? I see that there are about 3 different parse functions, with a varchar2a and varchar2s, but I am not familiar with these. Can someone help, or point me where to look for some examples?

            THanks!
            Janel
            • 18. Re: How to pipeline a function with a dynamic number of columns?
              BluShadow
              4000 characters shouldn't be a problem as you can define VARCHAR2 variables up to 32767 characters (32K) in PL/SQL and the DBMS_SQL package will accept those ok.

              If you need to go larger than 32K then the DBMS_SQL package provides a VARCHAR2S type that is a table of VARCHAR2 strings which allows you to split up your SQL and pass them as sequential strings which it will then join back together internally to execute.

              http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#i997676
              • 19. Re: How to pipeline a function with a dynamic number of columns?
                ascheffer
                If you change my code a bit it will work with "parameters":
                  static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                  return number
                  is
                  begin 
                    sctx.rows_returned := 0; 
                    sctx.rows_requested := p_rows_req; 
                    return odciconst.success; 
                  end; 
                And if you change ODCITableClose a bit ODCITabledescribe is called everytime, so you don't have to flush the shared pool:
                  member function ODCITableClose( self in NColPipe )
                  return number
                  is
                    t_id number;
                  begin
                    select object_id
                    into t_id
                    from user_objects
                    where object_name = 'NCOLPIPE' -- name of your type!
                    and object_type = 'TYPE BODY'; -- invalidating of the type body forces that ODCITableDescribe is executed for every call to this function
                    dbms_utility.invalidate( t_id );
                    return odciconst.success; 
                  end; 
                Anton
                SQL>select *
                  2  from ( select 'abc' x, 1 r from dual
                  3       union all select 'def', 3 from dual
                  4       )
                  5     , table( NColPipe.show( x, r ) )
                  6  /
                
                X            R 1          2                   3
                --- ---------- ---------- ---------- ----------
                abc          1 row: 1     row: 1              1
                def          3 row: 1     row: 1              1
                def          3 row: 2     row: 2              2
                def          3 row: 3     row: 3              3
                
                SQL>select *
                  2  from ( select 'abc' x, 1 r from dual
                  3       union all select 'def', 3 from dual
                  4       )
                  5     , table( NColPipe.show( 'one', r ) )
                  6  /
                
                X            R one
                --- ---------- ----------
                abc          1 1
                def          3 1
                def          3 2
                def          3 3
                
                SQL>
                Edited by: ascheffer on Nov 17, 2009 5:01 PM
                Added example
                • 20. Re: How to pipeline a function with a dynamic number of columns?
                  Z?
                  Anton

                  Sorry to dig up this old thread but I have become somewhat stuck.

                  I come back to this point made by Solomon...
                  >
                  Statement is parsed and ODCITableDescribe is called. Since :n is not a literal, a NULL is passed to ODCITableDescribe instead, so code does not know how many columns to create. Anyway, as you can see we are in catch 22 situation. We can run a statement that returns any number of rows/columns we want, but we can only supply that number statically as literal.
                  >
                  You provided a change to the ODCITableStart function but I can't see how this helps. From your example -
                  SQL>select *
                    2  from ( select 'abc' x, 1 r from dual
                    3       union all select 'def', 3 from dual
                    4       )
                    5     , table( NColPipe.show( x, r ) )
                    6  / 
                   
                  X            R 1          2                   3
                  --- ---------- ---------- ---------- ----------
                  abc          1 row: 1     row: 1              1
                  def          3 row: 1     row: 1              1
                  def          3 row: 2     row: 2              2
                  def          3 row: 3     row: 3              3
                  I can see that you have achieved exactly what I am trying to do, but I can't see or understand how. Would it be possible for you to post the full type script that was used to create this example?

                  Here is where I'm at at the moment (bit messy but it's a work in progress!)...
                  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
                        
                        SELECT LENGTH(p_str) - LENGTH(REPLACE(p_str, p_delim, NULL)) - 1
                        INTO   l_numCols
                        FROM   dual;
                  
                        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;
                  / 
                  SELECT *
                  FROM   TABLE(dynamic_pipe('#hj#kgfjhg#jhfgdjh#jhgfdjg#gf#djg#', '#'));
                  
                  C1    C2      C3       C4        C5    C6
                  ------------------------------------------
                  hj    kgfjhg  jhfgdjh  jhgfdjg   gf    djg
                  But then...
                  WITH my_table AS
                  (SELECT '#hjkgfjhg#jhfgdjh#jhgfdjg#gf#djg#' str
                   FROM    dual)
                  SELECT *
                  FROM   my_table, TABLE(dynamic_pipe(str, '#'));
                  
                  ORA-06502: PL/SQL: numeric or value error
                  ORA-06512: at "DYNCOLPIPE", line 179
                  ORA-06512: at line 4
                  If Anton, or anyone else could help out with this, it would be massively appreciated.

                  Cheers

                  Ben
                  • 21. Re: How to pipeline a function with a dynamic number of columns?
                    ascheffer
                    Try changing the code for ODCITableStart
                    create or replace type NColPipe as object 
                    ( 
                      l_parm varchar2(10),   -- The parameter given to the table function
                      rows_requested number, -- The parameter given to the table function
                      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_parm in varchar2, p_rows_req in number := 2 )
                      return number, 
                      static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
                      return number, 
                      static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                      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_parm in varchar2, p_rows_req in number := 2 )
                      return anydataset pipelined using NColPipe
                    );
                    / 
                    
                    create or replace type body NColPipe as 
                      static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
                      return number
                      is
                        atyp anytype; 
                      begin 
                        anytype.begincreate( dbms_types.typecode_object, atyp );
                        if p_parm = 'one'
                        then
                          atyp.addattr( 'one'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                        elsif p_parm = 'two'
                        then
                          atyp.addattr( 'one'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( 'two'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                        else
                          atyp.addattr( p_parm || '1'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( p_parm || '2'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( p_parm || '3'
                                      , dbms_types.typecode_number
                                      , 10
                                      , 0
                                      , null
                                      , null
                                      , null
                                      ); 
                        end if;
                        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_parm in varchar2, p_rows_req in number := 2 )
                      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_parm, p_rows_req, elem_typ, 0 ); 
                        return odciconst.success; 
                      end; 
                    --
                      static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                      return number
                      is
                      begin 
                        sctx.rows_returned := 0; 
                        sctx.rows_requested := p_rows_req; 
                        return odciconst.success; 
                      end;
                    --
                      member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
                      return number
                      is
                      begin 
                        anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset ); 
                        for i in self.rows_returned + 1 .. self.rows_requested
                        loop
                          outset.addinstance;
                          outset.piecewise(); 
                          if self.l_parm = 'one'
                          then
                            outset.setvarchar2( to_char( i ) ); 
                          elsif self.l_parm = 'two'
                          then
                            outset.setvarchar2( to_char( i ) ); 
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                          else
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                            outset.setnumber( i ); 
                          end if;
                          self.rows_returned := self.rows_returned + 1;
                        end loop;
                        outset.endcreate; 
                        return odciconst.success; 
                      end; 
                    --  
                      member function ODCITableClose( self in NColPipe )
                      return number
                      is
                        t_id number;
                      begin
                        select object_id
                        into t_id
                        from user_objects
                        where object_name = 'NCOLPIPE' -- name of your type!
                        and object_type = 'TYPE BODY'; -- invalidating of the type body forces that ODCITableDescribe is executed for every call to this function
                        dbms_utility.invalidate( t_id );
                        return odciconst.success; 
                      end;
                    end; 
                    /
                    
                    create or replace type NColPipe as object 
                    ( 
                      l_parm varchar2(10),   -- The parameter given to the table function
                      rows_requested number, -- The parameter given to the table function
                      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_parm in varchar2, p_rows_req in number := 2 )
                      return number, 
                      static function ODCITablePrepare( sctx out NColPipe, ti in sys.ODCITabFuncInfo, p_parm in varchar2, p_rows_req in number := 2 )
                      return number, 
                      static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                      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_parm in varchar2, p_rows_req in number := 2 )
                      return anydataset pipelined using NColPipe
                    );
                    / 
                    
                    create or replace type body NColPipe as 
                      static function ODCITableDescribe( rtype out anytype, p_parm in varchar2, p_rows_req in number := 2 )
                      return number
                      is
                        atyp anytype; 
                      begin 
                        anytype.begincreate( dbms_types.typecode_object, atyp );
                        if p_parm = 'one'
                        then
                          atyp.addattr( 'one'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                        elsif p_parm = 'two'
                        then
                          atyp.addattr( 'one'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( 'two'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                        else
                          atyp.addattr( p_parm || '1'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( p_parm || '2'
                                      , dbms_types.typecode_varchar2
                                      , null
                                      , null
                                      , 10
                                      , null
                                      , null
                                      ); 
                          atyp.addattr( p_parm || '3'
                                      , dbms_types.typecode_number
                                      , 10
                                      , 0
                                      , null
                                      , null
                                      , null
                                      ); 
                        end if;
                        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_parm in varchar2, p_rows_req in number := 2 )
                      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_parm, p_rows_req, elem_typ, 0 ); 
                        return odciconst.success; 
                      end; 
                    --
                      static function ODCITableStart( sctx in out NColPipe, p_parm in varchar2, p_rows_req in number := 2 )
                      return number
                      is
                      begin 
                        sctx.rows_returned := 0; 
                        sctx.rows_requested := p_rows_req; 
                        return odciconst.success; 
                      end;
                    --
                      member function ODCITableFetch( self in out NColPipe, nrows in number, outset out anydataset )
                      return number
                      is
                      begin 
                        anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset ); 
                        for i in self.rows_returned + 1 .. self.rows_requested
                        loop
                          outset.addinstance;
                          outset.piecewise(); 
                          if self.l_parm = 'one'
                          then
                            outset.setvarchar2( to_char( i ) ); 
                          elsif self.l_parm = 'two'
                          then
                            outset.setvarchar2( to_char( i ) ); 
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                          else
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                            outset.setvarchar2( 'row: ' || to_char( i ) ); 
                            outset.setnumber( i ); 
                          end if;
                          self.rows_returned := self.rows_returned + 1;
                        end loop;
                        outset.endcreate; 
                        return odciconst.success; 
                      end; 
                    --  
                      member function ODCITableClose( self in NColPipe )
                      return number
                      is
                        t_id number;
                      begin
                        select object_id
                        into t_id
                        from user_objects
                        where object_name = 'NCOLPIPE' -- name of your type!
                        and object_type = 'TYPE BODY'; -- invalidating of the type body forces that ODCITableDescribe is executed for every call to this function
                        dbms_utility.invalidate( t_id );
                        return odciconst.success; 
                      end;
                    end; 
                    /
                    
                    ANTON@XE>var n number
                    ANTON@XE>var y varchar2(10)
                    ANTON@XE>exec :n := 6
                    
                    PL/SQL procedure successfully completed.
                    
                    ANTON@XE>exec :y := 'Hello'
                    
                    PL/SQL procedure successfully completed.
                    
                    ANTON@XE>
                    ANTON@XE>select * from table( NColPipe.show( :y, :n ) );
                    
                    1          2                   3
                    ---------- ---------- ----------
                    row: 1     row: 1              1
                    row: 2     row: 2              2
                    row: 3     row: 3              3
                    row: 4     row: 4              4
                    row: 5     row: 5              5
                    row: 6     row: 6              6
                    
                    6 rows selected.
                    
                    ANTON@XE>
                    ANTON@XE>select *
                      2  from ( select 'abc' x, 1 r from dual
                      3         union all select 'def', 3 from dual
                      4       )
                      5     , table( NColPipe.show( x, r ) );
                    
                    X            R 1          2                   3
                    --- ---------- ---------- ---------- ----------
                    abc          1 row: 1     row: 1              1
                    def          3 row: 1     row: 1              1
                    def          3 row: 2     row: 2              2
                    def          3 row: 3     row: 3              3
                    Anton
                    • 22. Re: How to pipeline a function with a dynamic number of columns?
                      Z?
                      Hi Anton

                      Thanks for the quick reply. That solution is great for affecting the number of rows returned but I am trying to derive the number of columns from a parameter being passed. Essentially, if I have a string of...
                      '#abc#def#ghi#'
                      I can work out that there are three columns if i know the delimiter character (#). In the code I posted, this is working fine when I call it using a constant but falls over in ODCITableDescribe when I pass a bind variable or column value of another table. After some debugging, I found that this is because the p_str parameter passed to ODCITableDescribe is NULL. From the documentation...
                      >
                      Any arguments of the table function that are not constants are passed to ODCITableDescribe as NULLs since their values are not known at compile time.
                      >
                      What I'm trying to establish is whether you have ever found a way round this? Basically a dynamic number of columns passed as a parameter rather than a constant.

                      Cheers

                      Ben
                      • 23. Re: How to pipeline a function with a dynamic number of columns?
                        ascheffer
                        No, I haven't. Sorry.

                        Anton
                        • 24. Re: How to pipeline a function with a dynamic number of columns?
                          BluShadow
                          Munky wrote:
                          Hi Anton

                          Thanks for the quick reply. That solution is great for affecting the number of rows returned but I am trying to derive the number of columns from a parameter being passed. Essentially, if I have a string of...
                          '#abc#def#ghi#'
                          I can work out that there are three columns if i know the delimiter character (#). In the code I posted, this is working fine when I call it using a constant but falls over in ODCITableDescribe when I pass a bind variable or column value of another table. After some debugging, I found that this is because the p_str parameter passed to ODCITableDescribe is NULL. From the documentation...
                          >
                          Any arguments of the table function that are not constants are passed to ODCITableDescribe as NULLs since their values are not known at compile time.
                          >
                          What I'm trying to establish is whether you have ever found a way round this? Basically a dynamic number of columns passed as a parameter rather than a constant.
                          How could it ever work? If it's not a constant then, at compile time, it has no way of knowing the number of columns that will be generated and it can't be expected to cope with the possibility that a non constant value may produce different numbers of columns for each row. How on earth would SQL know how to allocate memory etc for the result sets during parsing of the query if there's the chance that each row can result in different numbers of columns?

                          Time to step back and reconsider what you are trying to achieve methinks.
                          • 25. Re: How to pipeline a function with a dynamic number of columns?
                            Z?
                            >
                            Time to step back and reconsider what you are trying to achieve methinks.
                            >
                            Already there - have gone in a different direction to fulfil the actual requirement, I'm just intrigued as to how to get this working. I just need a way to fire a procedure prior to ODCITableDescribe method being called as mentioned in this thread...

                            Piping dynamic number of columns

                            I know this is not really going to go anywhere, I just want to get it working and then dump it in my random crap schema - with the knowledge that it can be done, but that I would never use it.

                            Cheers

                            Ben
                            • 26. Re: How to pipeline a function with a dynamic number of columns?
                              ward3r
                              Hello!

                              munky, this code doesn't work on 11g. Do you have any success running it on 11g?
                              • 27. Re: How to pipeline a function with a dynamic number of columns?
                                BluShadow
                                ward3r wrote:
                                Hello!

                                munky, this code doesn't work on 11g. Do you have any success running it on 11g?
                                Please start your own thread with your own question, detailing which version of 11g you are running, and what code you have tried exactly (along with what you are actually trying to do).

                                Please read: {message:id=9360002} to ensure you post the correct information for people to help you.

                                You cannot rely on members on old threads still being around to answer your question, so you could be waiting a long time if you just post on the end of old threads like that.
                                1 2 Previous Next