Sunday, February 27, 2011

Nested Tables in PPAS 8.4

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.

Fix of "ORA-29275: partial multibyte character"

This happened to me when I was trying to migrate the Oracle Database to Advanced Server & PostgreSQL.

This kind of error comes if Data in Oracle have some junk/invisible Characters which conversion is unknown.
select * from junk_character;
'ORA-29275: partial multibyte character'
Oracle Descritption for this error is given below:
ORA-29275:
partial multibyte character
Cause: The requested read operation could not complete because a partial multibyte character was found at the end of the input.
Action: Ensure that the complete multibyte character is sent from the remote server and retry the operation. Or read the partial multibyte character as RAW.


Which doesn't give much information.

To find the the column and rows which have those junk/invisible character user can try following trick.
a. Select the data column wise as given below:
   select col1 from tablename;
   select col2 from tablename;
If you are sure about the columns which has those junk character, then you can do the range data SELECT using ROWNUM to find the Rows which has those Junk Character.
b. select col1,col2 from  rownum between lowerbound and upperbound;
After finding the rownum, user either can update the column value Or delete the rows if those are not important.

To get the data, without junk/invisible characters, user can also use Convert Function.

To fix this issue, I have used the convert Function of oracle to display the result.
CONVERT(COLUMN NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET');
Above function helped me.
Using Above function, I have created a view on top of the Table Data and using that view, I have been able to Migrate the data of Oracle.