This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 26, 2013 5:26 AM by BluShadow Go to original post RSS
  • 15. Re: How to pipeline a function with a dynamic number of columns?
    431127 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?
    munky Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?
    munky Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    No, I haven't. Sorry.

    Anton
  • 24. Re: How to pipeline a function with a dynamic number of columns?
    BluShadow Guru Moderator
    Currently Being Moderated
    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?
    munky Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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