Table Functions            Ammar Sajdi

Assume that you have a string that contains several consecutive names, the names have fixed length positions. You are required to extract the names from the string and manipulate them as if they are rows in some table.

For example

    AMMARSAJDIPALCOORACLEREALSOFTINFORMOBILITYDATACODE

    5    5    5   6      8        12          8

you want it to enlist the values in tabular format

AMMAR

SAJDI

PALCO

ORACLE

REALSOFT

INFORMOBILITY

DATACODE

A nice solution would be to use Oracle Table Functions (available oracle 9i and beyond)

First of all you need to create an object and a table of object

create or replace type str_obj is object  
    (

      field_n    varchar2(50)
   )
/

create or replace type fields_table is table of str_obj

Then create Function with return type of table object PIPELINED

or Replace FUNCTION tabulate (The_string IN varchar2)
   RETURN FIELDS_TABLE
   PIPELINED
   IS

TEMP   varchar2(50);

Begin

    TEMP := Substr(the_string,1,5);

      PIPE ROW (str_obj(temp));

    TEMP := Substr(the_string,6,5);

      PIPE ROW (str_obj(temp));

    TEMP := Substr (the_string,11,5);

      PIPE ROW  (str_obj(temp));

    TEMP := Substr (the_string,16,6);

      PIPE ROW (str_obj(temp));

    TEMP := Substr (the_string,22,8);

PIPE ROW (str_obj(temp));

   TEMP := Substr (the_string,30,13);

      PIPE ROW (str_obj(temp));

TEMP := Substr (the_string,43,8);

      PIPE ROW (str_obj(temp));

End;

Note that usage of PIPE ROW which is responsible to fill the table object 

To fetch the data, use the TABLE casting in the following format

SELECT .. from TABLE() ;

an example is

SQL> select * from Table(tabulate('AMMARSAJDIPALCOORACLEREALSOFTINFORMOBILITYDATACODE'));

FIELD_N

--------------------------------------------------

AMMAR

SAJDI

PALCO

ORACLE

REALSOFT

INFORMOBILITY

DATACODE

another example is

CREATE TABLE NAMES AS

select * from Table(tabulate('AMMARSAJDIPALCOORACLEREALSOFTINFORMOBILITYDATACODE'));