Some people ask this, How to use the Nested tables in PPAS? Since there is no Constructor Function Available and Oracle Compatibility Documentation is based on Procedure/Functions.

So, I thought to give an overview on this.

We can use nested tables by creating some manual Constructor and Using those constructor function to fetch the data or inserting the data:
Following is an example:

create type test_type as object (col1 varchar(200));
create type nested_type as table of test_type;
create table test_nested(col1 nested_type);

Now, Create some constructor Functions which can convert the data types as given below:

--- Constructor Function of test_type
CREATE OR REPLACE FUNCTION test_type(varchar) return test_type
as
declare
 result test_type;
Begin
  result=row($1);
  return result;
END;

And

--Constructor Function of nested_type
CREATE OR REPLACE FUNCTION nested_type(test_type[]) return nested_type
as
  counter integer:=0;
  rec test_type;
  result nested_type;
BEGIN
  for rec in select unnest($1)
  LOOP
    counter:=counter+1;
    result(counter):=rec;
  END LOOP;
  RETURN result;
END;

To Fetch the data, Users can make their function. Function which I have made is going to return the nested table data type as an array which can be use:

--Function to display the content
CREATE OR REPLACE function nested_type_print(arg1 nested_type) return test_type[]
as
  counter integer;
  len integer;
  res test_type[];
  rec test_type;
  arg nested_type;
BEGIN
  arg:=$1;
  len:=arg.COUNT;
  FOR counter in 1..len
  LOOP
     rec:=arg(counter);
     res:=array_append(res,rec);
  END LOOP;
  return res;
END;

Some Example is given below:

insert into test_nested values(nested_type(ARRAY[test_type('testing'),test_type('testing')]));
select nested_type_print(col1) from test_nested;
nested_type_print
---------------------------------------
{"(\"(testing)\")","(\"(testing)\")"}
{"(\"(testing)\")","(\"(testing)\")"}
For Usage of Nested Tables in Procedure, user can use following PPAS Documentation:
http://www.enterprisedb.com/docs/en/8.4/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-81.htm#P9436_501307

Note: PPAS 9.0 on wards, User doesn’t have to make constructor Functions. PPAS 9.0 would be coming with default constructor for each user-defined datatypes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s