8 New Oracle compatibility features in EnterpriseDB PPAS 9.2 Beta

Some of new Oracle feature included in PostgresPlus Advanced Server 9.2 beta. Most of them are charm to the product and needed if anyone has migrated from Oracle to PostgreSQL. Many more new features in the product itself, however I would like to walk-through few of them which are related to Oracle compatibility.

1. Object Type Support: In this version, member functions are supported, but not yet MAP member functions.

create or replace type mf_test as object
(
    name varchar2(30),
     member function disp return varchar2
);
create or replace type body mf_test as
    member function disp return varchar2 is
     begin
           return 'Name : '||name;
     end;
end;
edb=# declare
edb-#    v_mf mf_test;
edb$# begin
edb$#    v_mf := mf_test('Raghavendra');
edb$#    dbms_output.put_line(v_mf.disp());
edb$# end;
Name : Raghavendra

EDB-SPL Procedure successfully completed

2. Pl/SQL sub types : Now we can define own subtypes in the declarative part of any PL/SQL block, subprogram, or package.

Syntax: SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

declare
   subtype hiredate is date not null;
   pfdate hiredate := sysdate;
begin
   dbms_output.put_line(pfdate);
end;
06-OCT-12 19:53:44

EDB-SPL Procedure successfully completed

3. DROP TABLE CASCADE CONSTRAINTS: This option will delete all foriegn key constraints that referencing the table to be dropped, and then drops the table.

edb=# create table master(id int primary key);
edb=# create table master1(id int references master(id));
edb=# \d master1
 Table "enterprisedb.master1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Foreign-key constraints:
    "master1_id_fkey" FOREIGN KEY (id) REFERENCES master(id)

edb=# drop table master cascade constraints;
NOTICE:  drop cascades to constraint master1_id_fkey on table master1
DROP TABLE
edb=# \d master1
 Table "enterprisedb.master1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |

4. TYPE's in Package definition:

create or replace package t_pack as 
    type ftype is record(name varchar2(20)); 
end;
edb=# call t_pack.ftype('EDB');
 ftype
-------
 (EDB)
(1 row)

5. TABLE() function call on Nested tables: A TABLE() enables to query a collection in the FROM clause like a table.

CREATE OR REPLACE TYPE string_a IS TABLE OF VARCHAR2(765);
select * from table(string_a('abc','xyz')) ;
column_value
--------------
abc
xyz
(2 rows)

6. IN/OUT of UDT in function call: The functions udtabletype_in and udtabletype_out are now supported for nested tables.

7. Usage on reserved Keywords(LOG/CURRENT_DATE): Now LOG word can be used for naming the function. Also CURRENT_DATE, can be used for variable names.

edb=# create or replace function log(t text) return text as 
begin 
   return t;
end;

edb=# select log('EDB');
log
-----
 EDB
(1 row)

edb=# declare
edb-# current_date date := '07-OCT-2012';
edb$# begin
edb$#    dbms_output.put_line(current_date);
edb$# end;
07-OCT-12 00:00:00

EDB-SPL Procedure successfully completed

8. STRING /NVARCHAR2 datatype support: Now STRING datatype and NVARCHAR2 for multi-byte data is supported for table column. String aliased to VARCHAR2 and NVARCHAR2 maps to varchar of PPAS data-types.

edb=# create table dtype( a string, b nvarchar2);
CREATE TABLE
edb=# \d dtype
       Table "enterprisedb.dtype"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 a      | character varying |
 b      | character varying |

Download & release notes link:
http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server/downloads

--Raghav

PAGER setting in windows for psql client

psql is a great tool. Oftentimes, output of the particular command in psql is too big to fit on one screen, hence outputs are wrapped to fit in one screen which becomes output unreadable. To custommize command output in readable format we have system variable called PAGER. If its on linux, I can simply set PAGER variable with LESS command + options to view command ouptut in readable format.
Eg:-
export PAGER='less -RSX'

Whereas on windows, you can customize psql output like linux, but with the help of windows tool less.exe which is similar to linux. Below, I copied download link of less.exe, download the binaries which are in zip format and unzip to your desired location.
http://gnuwin32.sourceforge.net/packages/less.htm

Now, set the windows system variable PAGER and VALUE pointing to the less.exe location. Also, include less.exe location in "Path".  After setting system variable, your psql terminal has all sort of functionality like LESS command in linux. "less.exe -F <filename>", behaves as "tail -f" in linux. Many more feature in less.exe which I haven't tried but you can give a try and make your psql output eye-catching :).

PAGER setting screenshot:

--Raghav