23 Pipelined and Parallel Table Functions

ODCITableStart

ODCITableStart initializes the scan of a table function.

STATIC FUNCTION ODCITableStart(
   sctx IN OUT <imptype>, 
   <args>) 
RETURN NUMBER

sctx (IN OUT)

args (IN)

ODCIConst.Success on success, ODCIConst.Error otherwise.

• If ODCITablePrepare is not implemented, this is the first routine that is invoked to begin retrieving rows from a table function. This routine typically performs the setup needed for the scan. The scan context is created (as an object instance sctx) and returned to Oracle. The arguments to the table function, specified by the user in the SELECT statement, are passed in as parameters to this routine. If ODCITablePrepare is implemented, it creates the scan context at compile time, and that scan context is passed in to this routine.

• Any REF CURSOR arguments of the table function must be declared as SYS_REFCURSOR type in the declaration of the ODCITableStart method.

ODCITableFetch

ODCITableFetch returns the next batch of rows from a table function.

MEMBER FUNCTION ODCITableFetch(
   self IN OUT <imptype>, 
   nrows IN NUMBER, 
   rws OUT <coll-type>) 
RETURN NUMBER

self (IN)

self (OUT)

nrows (IN)

rws (OUT)

ODCIConst.Success on success, ODCIConst.Error otherwise.

• ODCITableFetch is invoked one or more times by Oracle to retrieve all the rows in the collection returned by the table function. The scan context is passed in as a parameter. Typically ODCITableFetch uses the input scan context and computes the next set of rows to be returned to Oracle. In addition, it may update the scan context accordingly.

• Returning more rows in each invocation of fetch() reduces the number of fetch calls that need to be made and thus improves performance.

• Oracle calls ODCITableFetch repeatedly until all rows in the table function's collection have been returned. When all rows have been returned, ODCITableFetch should return a null collection.

ODCITableClose

ODCITableClose performs cleanup operations after scanning a table function.

MEMBER FUNCTION ODCITableClose(
   self IN <imptype>) 
RETURN NUMBER

self (IN)

ODCIConst.Success on success, ODCIConst.Error otherwise.

• Oracle invokes ODCITableClose after the last fetch call. The scan context is passed in as a parameter. ODCITableClose then performs any necessary cleanup operations, such as freeing memory.

• If ODCITablePrepare is implemented, this routine is only called once, at the end of query execution, rather than each time the table function exits.

ODCITablePrepare

Prepares the scan context and other query information at compile time.

STATIC FUNCTION ODCITablePrepare(
   sctx OUT <imptype>, 
   tf_info SYS.ODCITabFuncInfo,
   args);

sctx (OUT)

tf_info

args (IN)

• This method prepares the scan context based on the information known at compile time. This scan context is passed to ODCITableStart when it is called at the beginning of query execution.

• If this optional method is implemented, ODCITableClose is only called once at the end of query execution. Each time the table function is restarted, ODCITableStart is called and passed the scan context. This allows the table function to maintain context between restarts, and to perform cleanup operations only once at the end of query execution.

ODCITableDescribe

ODCITableDescribe returns describe information for a table function whose return type is ANYDATASET.

STATIC FUNCTION ODCITableDescribe(
   rtype OUT ANYTYPE, 
   <args>) 
RETURN NUMBER

rtype (OUT)

args (IN)

ODCIConst.Success on success, ODCIConst.Error otherwise.

• If the optional routine ODCITableDescribe is implemented, Oracle invokes it at query compilation time to retrieve the specific type information.

• This interface is applicable only for table functions whose return type is ANYDATASET. The format of elements within the returned collection is conveyed to Oracle by returning an instance of ANYTYPE. The ANYTYPE instance specifies the actual structure of the returned rows in the context of the specific query.

• ANYTYPE provides a datatype to model the metadata of a row—the names and datatypes of all the columns (fields) comprising the row. It also provides a set of PL/SQL and C interfaces for users to construct and access the metadata information. ANYDATASET, like ANYTYPE, contains a description of a given type, but ANYDATASET also contains a set of data instances of that type

• The following example shows a query on a table function that uses the ANYDATASET type:

  SELECT * FROM 
  TABLE(CAST(AnyBooks('http://.../books.xml') AS ANYDATASET));
  
  

  At query compilation time, Oracle invokes the ODCITableDescribe routine. The routine typically uses the user arguments to figure out the nature of the return rows. In this example, ODCITableDescribe consults the DTD of the XML documents at the specified location to determine the appropriate ANYTYPE value to return. Each ANYTYPE instance is constructed by invoking the constructor APIs with this field name and datatype information.

• Any arguments of the table function that are not constants are passed to ODCITableDescribe as NULLs because their values are not known at compile time.