[3/9] incubator-hawq git commit: HAWQ-149. Add orafce, gp_cancel_query, pgbench and extprotocol to HAWQ

[fo...@apache.org]

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/plvstr.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/plvstr.c b/contrib/orafce/plvstr.c
new file mode 100644
index 0000000..8e8b97e
--- /dev/null
+++ b/contrib/orafce/plvstr.c
@@ -0,0 +1,1347 @@
+/*
+  This code implements one part of functonality of
+  free available library PL/Vision. Please look www.quest.com
+
+  Original author: Steven Feuerstein, 1996 - 2002
+  PostgreSQL implementation author: Pavel Stehule, 2006
+
+  This module is under BSD Licence
+
+  History:
+    1.0. first public version 13. March 2006
+*/
+
+
+#include "postgres.h"
+#include "utils/builtins.h"
+#include "utils/numeric.h"
+#include "string.h"
+#include "stdlib.h"
+#include "utils/pg_locale.h"
+#include "mb/pg_wchar.h"
+#include "nodes/execnodes.h"
+
+#include "catalog/pg_type.h"
+#include "libpq/pqformat.h"
+#include "orafunc.h"
+#include "builtins.h"
+
+PG_FUNCTION_INFO_V1(plvstr_rvrs);
+PG_FUNCTION_INFO_V1(plvstr_normalize);
+PG_FUNCTION_INFO_V1(plvstr_is_prefix);
+PG_FUNCTION_INFO_V1(plvstr_is_prefix_text);
+PG_FUNCTION_INFO_V1(plvstr_is_prefix_int);
+PG_FUNCTION_INFO_V1(plvstr_is_prefix_int64);
+PG_FUNCTION_INFO_V1(plvstr_lpart);
+PG_FUNCTION_INFO_V1(plvstr_rpart);
+PG_FUNCTION_INFO_V1(plvstr_lstrip);
+PG_FUNCTION_INFO_V1(plvstr_rstrip);
+PG_FUNCTION_INFO_V1(plvstr_left);
+PG_FUNCTION_INFO_V1(plvstr_right);
+PG_FUNCTION_INFO_V1(plvstr_substr2);
+PG_FUNCTION_INFO_V1(plvstr_substr3);
+PG_FUNCTION_INFO_V1(plvstr_instr2);
+PG_FUNCTION_INFO_V1(plvstr_instr3);
+PG_FUNCTION_INFO_V1(plvstr_instr4);
+PG_FUNCTION_INFO_V1(plvstr_betwn_i);
+PG_FUNCTION_INFO_V1(plvstr_betwn_c);
+PG_FUNCTION_INFO_V1(plvstr_swap);
+
+PG_FUNCTION_INFO_V1(plvchr_nth);
+PG_FUNCTION_INFO_V1(plvchr_first);
+PG_FUNCTION_INFO_V1(plvchr_last);
+PG_FUNCTION_INFO_V1(plvchr_is_kind_i);
+PG_FUNCTION_INFO_V1(plvchr_is_kind_a);
+PG_FUNCTION_INFO_V1(plvchr_char_name);
+
+PG_FUNCTION_INFO_V1(oracle_substr2);
+PG_FUNCTION_INFO_V1(oracle_substr3);
+
+static text *ora_substr(Datum str, int start, int len);
+
+#define ora_substr_text(str, start, len) \
+	ora_substr(PointerGetDatum((str)), (start), (len))
+
+static const char* char_names[] = {
+	"NULL","SOH","STX","ETX","EOT","ENQ","ACK","DEL",
+	"BS",  "HT", "NL", "VT", "NP", "CR", "SO", "SI",
+	"DLE", "DC1","DC2","DC3","DC4","NAK","SYN","ETB",
+	"CAN", "EM","SUB","ESC","FS","GS","RS","US","SP"
+};
+
+#define NON_EMPTY_CHECK(str) \
+if (VARSIZE_ANY_EXHDR(str) == 0) \
+	ereport(ERROR, \
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+			 errmsg("invalid parameter"), \
+		 errdetail("Empty string is not allowed.")));
+
+#define PARAMETER_ERROR(detail) \
+	ereport(ERROR, \
+		(errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+		 errmsg("invalid parameter"), \
+		 errdetail(detail)));
+
+
+#ifndef _pg_mblen
+#define _pg_mblen	pg_mblen
+#endif
+
+typedef enum
+{
+	POSITION,
+	FIRST,
+	LAST
+}  position_mode;
+
+
+#if PG_VERSION_NUM < 80400
+text *
+cstring_to_text_with_len(const char *c, int n)
+{
+	text *result;
+	result = palloc(n + VARHDRSZ);
+	SET_VARSIZE(result, n + VARHDRSZ);
+	memcpy(VARDATA(result), c, n);
+
+	return result;
+}
+#endif
+
+/*
+ * Make substring, can handle negative start
+ *
+ */
+
+
+int
+ora_mb_strlen(text *str, char **sizes, int **positions)
+{
+	int r_len;
+	int cur_size = 0;
+	int sz;
+	char *p;
+	int cur = 0;
+
+	p = VARDATA_ANY(str);
+	r_len = VARSIZE_ANY_EXHDR(str);
+
+	if (NULL != sizes)
+		*sizes = palloc(r_len * sizeof(char));
+	if (NULL != positions)
+		*positions = palloc(r_len * sizeof(int));
+
+	while (cur < r_len)
+	{
+		sz = _pg_mblen(p);
+		if (sizes)
+			(*sizes)[cur_size] = sz;
+		if (positions)
+			(*positions)[cur_size] = cur;
+		cur += sz;
+		p += sz;
+		cur_size += 1;
+	}
+
+	return cur_size;
+}
+
+
+int
+ora_mb_strlen1(text *str)
+{
+	int r_len;
+	int c;
+	char *p;
+
+	r_len = VARSIZE_ANY_EXHDR(str);
+
+	if (pg_database_encoding_max_length() == 1)
+		return r_len;
+
+	p = VARDATA_ANY(str);
+	c = 0;
+	while (r_len > 0)
+	{
+		int sz;
+
+		sz = _pg_mblen(p);
+		p += sz;
+		r_len -= sz;
+		c += 1;
+	}
+
+	return c;
+}
+
+/*
+ * len < 0 means "length is not specified".
+ */
+static text *
+ora_substr(Datum str, int start, int len)
+{
+	if (start == 0)
+		start = 1;	/* 0 is interpreted as 1 */
+	else if (start < 0)
+	{
+		text   *t;
+		int32	n;
+
+		t = DatumGetTextPP(str);
+		n = pg_mbstrlen_with_len(VARDATA_ANY(t), VARSIZE_ANY_EXHDR(t));
+		start = n + start + 1;
+		if (start <= 0)
+			return cstring_to_text("");
+		str = PointerGetDatum(t);	/* save detoasted text */
+	}
+
+	if (len < 0)
+		return DatumGetTextP(DirectFunctionCall2(text_substr_no_len,
+			str, Int32GetDatum(start)));
+	else
+		return DatumGetTextP(DirectFunctionCall3(text_substr,
+			str, Int32GetDatum(start), Int32GetDatum(len)));
+}
+
+/* 
+ * Simply search algorithm. Could be greatly improved if we
+ * used KMP. Morris-Boyer would be more efficient but it might
+ * give us head aches in the presence of multibyte. For example, see
+ * `CHINESE STRING SEARCHING USING THE KMP ALGORITHM' by R. Luk.
+ */
+static int
+ora_instr_mb(text *txt, text *pattern, int start, int nth)
+{
+	int			c_len_txt, c_len_pat;
+	int			b_len_pat;
+	int		   *pos_txt;
+	const char *str_txt, *str_pat;
+	int			beg, end, i, dx;
+
+	str_txt = VARDATA_ANY(txt);
+	c_len_txt = ora_mb_strlen(txt, NULL, &pos_txt);
+	str_pat = VARDATA_ANY(pattern);
+	b_len_pat = VARSIZE_ANY_EXHDR(pattern);
+	c_len_pat = pg_mbstrlen_with_len(str_pat, b_len_pat);
+
+	if (start > 0)
+	{
+		dx = 1;
+		beg = start - 1;
+		end = c_len_txt - c_len_pat + 1;
+		if (beg >= end)
+			return 0;	/* out of range */
+	}
+	else
+	{
+		dx = -1;
+		beg = Min(c_len_txt + start, c_len_txt - c_len_pat);
+		end = -1;
+		if (beg <= end)
+			return 0;	/* out of range */
+	}
+
+	for (i = beg; i != end; i += dx)
+	{
+		if (memcmp(str_txt + pos_txt[i], str_pat, b_len_pat) == 0)
+		{
+			if (--nth == 0)
+				return i + 1;
+		}
+	}
+
+	return 0;
+}
+
+
+int
+ora_instr(text *txt, text *pattern, int start, int nth)
+{
+	int			len_txt, len_pat;
+	const char *str_txt, *str_pat;
+	int			beg, end, i, dx;
+
+	if (nth <= 0)
+		PARAMETER_ERROR("Fourth parameter must be a positive integer.");
+
+	/* Forward for multibyte strings */
+	if (pg_database_encoding_max_length() > 1)
+		return ora_instr_mb(txt, pattern, start, nth);
+
+	str_txt = VARDATA_ANY(txt);
+	len_txt = VARSIZE_ANY_EXHDR(txt);
+	str_pat = VARDATA_ANY(pattern);
+	len_pat = VARSIZE_ANY_EXHDR(pattern);
+
+	if (start > 0)
+	{
+		dx = 1;
+		beg = start - 1;
+		end = len_txt - len_pat + 1;
+		if (beg >= end)
+			return 0;	/* out of range */
+	}
+	else
+	{
+		dx = -1;
+		beg = Min(len_txt + start, len_txt - len_pat);
+		end = -1;
+		if (beg <= end)
+			return 0;	/* out of range */
+	}
+
+	for (i = beg; i != end; i += dx)
+	{
+		if (memcmp(str_txt + i, str_pat, len_pat) == 0)
+		{
+			if (--nth == 0)
+				return i + 1;
+		}
+	}
+
+	return 0;
+}
+
+
+/****************************************************************
+ * PLVstr.normalize
+ *
+ * Syntax:
+ *   FUNCTION plvstr.normalize (string_in IN VARCHAR)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Normalize string - replace white chars by space, replace
+ * spaces by space
+ *
+ ****************************************************************/
+
+Datum
+plvstr_normalize(PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	text *result;
+	char *aux, *aux_cur;
+	int i, l;
+	char c, *cur;
+	bool write_spc = false;
+	bool ignore_stsp = true;
+	bool mb_encode;
+	int sz;
+
+	mb_encode = pg_database_encoding_max_length() > 1;
+
+	l = VARSIZE_ANY_EXHDR(str);
+	aux_cur = aux = palloc(l);
+
+	write_spc = false;
+	cur = VARDATA_ANY(str);
+
+	for (i = 0; i < l; i++)
+	{
+		switch ((c = *cur))
+		{
+			case '\t':
+			case '\n':
+			case '\r':
+			case ' ':
+				write_spc = ignore_stsp ? false : true;
+				break;
+			default:
+				/* ignore all other unvisible chars */
+
+				if (mb_encode)
+				{
+					sz = _pg_mblen(cur);
+					if (sz > 1 || (sz == 1 && c > 32))
+					{
+						int j;
+
+						if (write_spc)
+						{
+							*aux_cur++ = ' ';
+							write_spc = false;
+
+						}
+						for (j = 0; j < sz; j++)
+						{
+							*aux_cur++ = *cur++;
+						}
+						ignore_stsp = false;
+						i += sz - 1;
+					}
+					continue;
+
+				}
+				else
+					if (c > 32)
+					{
+						if (write_spc)
+						{
+							*aux_cur++ = ' ';
+							write_spc = false;
+						}
+						*aux_cur++ = c;
+						ignore_stsp = false;
+						continue;
+					}
+
+		}
+		cur += 1;
+	}
+
+	l = aux_cur - aux;
+	result = palloc(l+VARHDRSZ);
+	SET_VARSIZE(result, l + VARHDRSZ);
+	memcpy(VARDATA(result), aux, l);
+
+	PG_RETURN_TEXT_P(result);
+}
+
+
+/****************************************************************
+ * PLVstr.instr
+ *
+ * Syntax:
+ *   FUNCTION plvstr.instr (string_in VARCHAR, pattern VARCHAR)
+ *   FUNCTION plvstr.instr (string_in VARCHAR, pattern VARCHAR,
+ *            start_in INTEGER)
+ *   FUNCTION plvstr.instr (string_in VARCHAR, pattern VARCHAR,
+ *            start_in INTEGER, nth INTEGER)
+ *            RETURN INT;
+ *
+ * Purpose:
+ *   Search pattern in string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_instr2 (PG_FUNCTION_ARGS)
+{
+	text *arg1 = PG_GETARG_TEXT_PP(0);
+	text *arg2 = PG_GETARG_TEXT_PP(1);
+
+	PG_RETURN_INT32(ora_instr(arg1, arg2, 1, 1));
+}
+
+Datum
+plvstr_instr3 (PG_FUNCTION_ARGS)
+{
+	text *arg1 = PG_GETARG_TEXT_PP(0);
+	text *arg2 = PG_GETARG_TEXT_PP(1);
+	int arg3 = PG_GETARG_INT32(2);
+
+	PG_RETURN_INT32(ora_instr(arg1, arg2, arg3, 1));
+}
+
+Datum
+plvstr_instr4 (PG_FUNCTION_ARGS)
+{
+	text *arg1 = PG_GETARG_TEXT_PP(0);
+	text *arg2 = PG_GETARG_TEXT_PP(1);
+	int arg3 = PG_GETARG_INT32(2);
+	int arg4 = PG_GETARG_INT32(3);
+
+	PG_RETURN_INT32(ora_instr(arg1, arg2, arg3, arg4));
+}
+
+
+/****************************************************************
+ * PLVstr.is_prefix
+ *
+ * Syntax:
+ *   FUNCTION plvstr.is_prefix (string_in IN VARCHAR,
+ *                    prefix_in VARCHAR,
+ *                    case_sensitive BOOL := true)
+ *      RETURN bool;
+ *   FUNCTION plvstr.is_prefix (num_in IN NUMERIC,
+ *                    prefix_in NUMERIC) RETURN bool;
+ *   FUNCTION plvstr.is_prefix (int_in IN INT,
+ *                    prefix_in INT)  RETURN bool;
+ *
+ * Purpose:
+ *   Returns true, if prefix_in is prefix of string_in
+ *
+ ****************************************************************/
+
+
+Datum
+plvstr_is_prefix_text (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	text *prefix = PG_GETARG_TEXT_PP(1);
+	bool case_sens = PG_GETARG_BOOL(2);
+	bool mb_encode;
+
+	int str_len = VARSIZE_ANY_EXHDR(str);
+	int pref_len = VARSIZE_ANY_EXHDR(prefix);
+
+	int i;
+	char *ap, *bp;
+
+
+	mb_encode = pg_database_encoding_max_length() > 1;
+
+	if (mb_encode && !case_sens)
+	{
+		str = (text*)DatumGetPointer(DirectFunctionCall1(lower, PointerGetDatum(str)));
+		prefix = (text*)DatumGetPointer(DirectFunctionCall1(lower, PointerGetDatum(prefix)));
+	}
+
+	ap = VARDATA_ANY(str);
+	bp = VARDATA_ANY(prefix);
+
+	for (i = 0; i < pref_len; i++)
+	{
+		if (i >= str_len)
+			break;
+		if (case_sens || mb_encode)
+		{
+			if (*ap++ != *bp++)
+				break;
+		}
+		else if (!mb_encode)
+		{
+			if (pg_toupper((unsigned char) *ap++) != pg_toupper((unsigned char) *bp++))
+				break;
+		}
+	}
+
+	PG_RETURN_BOOL(i == pref_len);
+}
+
+Datum
+plvstr_is_prefix_int (PG_FUNCTION_ARGS)
+{
+	int n = PG_GETARG_INT32(0);
+	int prefix = PG_GETARG_INT32(1);
+	bool result = false;
+
+	do
+	{
+		if (n == prefix)
+		{
+			result = true;
+			break;
+		}
+		n = n / 10;
+
+	} while (n >= prefix);
+
+	PG_RETURN_BOOL(result);
+}
+
+Datum
+plvstr_is_prefix_int64 (PG_FUNCTION_ARGS)
+{
+	int64 n = PG_GETARG_INT64(0);
+	int64 prefix = PG_GETARG_INT64(1);
+	bool result = false;
+
+	do
+	{
+		if (n == prefix)
+		{
+			result = true;
+			break;
+		}
+		n = n / 10;
+
+	} while (n >= prefix);
+
+	PG_RETURN_BOOL(result);
+}
+
+
+/****************************************************************
+ * PLVstr.rvrs
+ *
+ * Syntax:
+ *   FUNCTION plvstr.rvrs (string_in IN VARCHAR,
+ *					  start_in IN INTEGER := 1,
+ *					  end_in IN INTEGER := NULL)
+ *  	RETURN VARCHAR2;
+ *
+ * Purpose:
+ *   Reverse string or part of string
+ *
+ ****************************************************************/
+
+Datum
+plvstr_rvrs(PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	int start = PG_GETARG_INT32(1);
+	int end = PG_GETARG_INT32(2);
+	int len, aux;
+	int i;
+	int new_len;
+	text *result;
+	char *data;
+	char *sizes = NULL;
+	int *positions = NULL;
+	bool mb_encode;
+
+	if (PG_ARGISNULL(0))
+		PG_RETURN_NULL();
+
+	mb_encode = pg_database_encoding_max_length() > 1;
+
+	if (!mb_encode)
+		len = VARSIZE_ANY_EXHDR(str);
+	else
+		len = ora_mb_strlen(str, &sizes, &positions);
+
+
+
+	start = PG_ARGISNULL(1) ? 1 : start;
+	end = PG_ARGISNULL(2) ? (start < 0 ? -len : len) : end;
+
+	if ((start > end && start > 0) || (start < end && start < 0))
+		PARAMETER_ERROR("Third parameter must be greater than second.");
+
+	if (start < 0)
+	{
+		aux = len + end + 1;
+		end = len + start + 1;
+		start = end;
+	}
+
+	new_len = end - start + 1;
+
+	if (mb_encode)
+	{
+		int max_size;
+		int cur_size;
+		char *p;
+		int j;
+		int fz_size;
+
+		fz_size = VARSIZE_ANY_EXHDR(str);
+
+		if ((max_size = (new_len*pg_database_encoding_max_length())) > fz_size)
+			result = palloc(fz_size + VARHDRSZ);
+		else
+			result = palloc(max_size + VARHDRSZ);
+		data = (char*) VARDATA(result);
+
+		cur_size = 0;
+		p = VARDATA_ANY(str);
+		for (i = end - 1; i>= start - 1; i--)
+		{
+			for (j=0; j<sizes[i]; j++)
+				*data++ = *(p+positions[i]+j);
+			cur_size += sizes[i];
+		}
+		SET_VARSIZE(result, cur_size + VARHDRSZ);
+
+	}
+	else
+	{
+		char *p = VARDATA_ANY(str);
+		result = palloc(new_len + VARHDRSZ);
+		data = (char*) VARDATA(result);
+		SET_VARSIZE(result, new_len + VARHDRSZ);
+
+		for (i = end - 1; i >= start - 1; i--)
+			*data++ = p[i];
+	}
+
+	PG_RETURN_TEXT_P(result);
+}
+
+
+/****************************************************************
+ * PLVstr.lpart
+ *
+ * Syntax:
+ *   FUNCTION PLVstr.lpart (string_in IN VARCHAR,
+ *					   divider_in IN VARCHAR,
+ *					   start_in IN INTEGER := 1,
+ *					   nth_in IN INTEGER := 1,
+ *					   all_if_notfound_in IN BOOLEAN := FALSE)
+ *	RETURN VARCHAR2;
+ *
+ * Purpose:
+ *   Call this function to return the left part of a string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_lpart (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_P(0);
+	text *div = PG_GETARG_TEXT_P(1);
+	int start = PG_GETARG_INT32(2);
+	int nth   = PG_GETARG_INT32(3);
+	bool all_if_notfound  = PG_GETARG_BOOL(4);
+	int loc;
+
+	loc = ora_instr(str, div, start, nth);
+	if (loc == 0)
+	{
+		if (all_if_notfound)
+			PG_RETURN_TEXT_P(TextPCopy(str));
+		else
+			PG_RETURN_NULL();
+	}
+	else
+		PG_RETURN_TEXT_P(ora_substr_text(str, 1, loc-1));
+}
+
+
+/****************************************************************
+ * PLVstr.rpart
+ *
+ * Syntax:
+ *   FUNCTION PLVstr.rpart (string_in IN VARCHAR,
+ *					   divider_in IN VARCHAR,
+ *					   start_in IN INTEGER := 1,
+ *					   nth_in IN INTEGER := 1,
+ *					   all_if_notfound_in IN BOOLEAN := FALSE)
+ *	RETURN VARCHAR2;
+ *
+ * Purpose:
+ *   Call this function to return the right part of a string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_rpart (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_P(0);
+	text *div = PG_GETARG_TEXT_P(1);
+	int start = PG_GETARG_INT32(2);
+	int nth   = PG_GETARG_INT32(3);
+	bool all_if_notfound  = PG_GETARG_BOOL(4);
+	int loc;
+
+	loc = ora_instr(str, div, start, nth);
+	if (loc == 0)
+	{
+		if (all_if_notfound)
+			PG_RETURN_TEXT_P(TextPCopy(str));
+		else
+			PG_RETURN_NULL();
+	}
+	else
+		PG_RETURN_TEXT_P(ora_substr_text(str, loc+1, -1));
+}
+
+
+/****************************************************************
+ * PLVstr.lstrip
+ *
+ * Syntax:
+ *   FUNCTION plvstr.lstrip (string_in IN VARCHAR,
+ *							substring_in IN VARCHAR,
+ *							num_in IN INTEGER := 1)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to remove characters from the beginning
+ * (left) of a string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_lstrip (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	text *pat = PG_GETARG_TEXT_PP(1);
+	int num = PG_GETARG_INT32(2);
+	int count = 0;
+	int len_p, len_s, i;
+
+	char *str_p, *aux_str_p, *pat_p;
+	len_p = VARSIZE_ANY_EXHDR(pat);
+	len_s = VARSIZE_ANY_EXHDR(str);
+
+	str_p = VARDATA_ANY(str);
+	while (count < num)
+	{
+		pat_p = VARDATA_ANY(pat);
+		aux_str_p = str_p;
+
+		if (len_s < len_p)
+			break;
+
+		for (i = 0; i < len_p; i++)
+			if (*aux_str_p++ != *pat_p++)
+				break;
+
+		if (i >= len_p)
+		{
+			count++;
+			/* found */
+			str_p = aux_str_p;
+			len_s -= len_p;
+			continue;
+		}
+		break;
+	}
+
+	PG_RETURN_TEXT_P(cstring_to_text_with_len(str_p,len_s));
+}
+
+
+/****************************************************************
+ * PLVstr.rstrip
+ *
+ * Syntax:
+ *   FUNCTION plvstr.rstrip (string_in IN VARCHAR,
+ *							substring_in IN VARCHAR,
+ *							num_in IN INTEGER := 1)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to remove characters from the end
+ * (right) of a string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_rstrip (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	text *pat = PG_GETARG_TEXT_PP(1);
+	int num = PG_GETARG_INT32(2);
+	int count = 0;
+	int len_p, len_s, i;
+
+	char *str_p, *aux_str_p, *pat_p;
+	len_p = VARSIZE_ANY_EXHDR(pat);
+	len_s = VARSIZE_ANY_EXHDR(str);
+
+	str_p = VARDATA_ANY(str) + len_s - 1;
+
+	while (count < num)
+	{
+		pat_p = VARDATA_ANY(pat) + len_p - 1;
+		aux_str_p = str_p;
+
+		if (len_s < len_p)
+			break;
+
+		for (i = 0; i < len_p; i++)
+			if (*aux_str_p-- != *pat_p--)
+				break;
+
+		if (i >= len_p)
+		{
+			count++;
+			/* found */
+			str_p = aux_str_p;
+			len_s -= len_p;
+			continue;
+		}
+		break;
+	}
+
+	PG_RETURN_TEXT_P(cstring_to_text_with_len(VARDATA_ANY(str),len_s));
+}
+
+
+/****************************************************************
+ * PLVstr.left
+ *
+ * Syntax:
+ *   FUNCTION plvstr.left (string_in IN VARCHAR,
+ *							num_in INTEGER)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns firs num_in charaters. You can use negative num_in
+ *   left('abcde', -2) -> abc
+ *
+ ****************************************************************/
+
+
+Datum
+plvstr_left (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_P(0);
+	int n = PG_GETARG_INT32(1);
+	if (n < 0)
+		n = ora_mb_strlen1(str) + n;
+	n = n < 0 ? 0 : n;
+
+	PG_RETURN_TEXT_P(ora_substr_text(str, 1, n));
+}
+
+
+/****************************************************************
+ * PLVstr.right
+ *
+ * Syntax:
+ *   FUNCTION plvstr.right (string_in IN VARCHAR,
+ *							num_in INTEGER)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns last (right) num_in characters.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_right (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_P(0);
+	int n = PG_GETARG_INT32(1);
+	if (n < 0)
+		n = ora_mb_strlen1(str) + n;
+	n = (n < 0) ? 0 : n;
+
+	PG_RETURN_TEXT_P(ora_substr_text(str, -n, -1));
+}
+
+/****************************************************************
+ * PLVstr.substr2
+ *
+ * Syntax:
+ *   FUNCTION plvstr.substr (string_in IN VARCHAR,
+ *							start INTEGER)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns substring started on start_in to end
+ *
+ ****************************************************************/
+
+Datum
+plvstr_substr2 (PG_FUNCTION_ARGS)
+{
+	return oracle_substr2(fcinfo);
+}
+
+
+/****************************************************************
+ * PLVstr.substr3
+ *
+ * Syntax:
+ *   FUNCTION plvstr.substr (string_in IN VARCHAR,
+ *							start INTEGER, len INTEGER)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns len chars from start_in position
+ *
+ ****************************************************************/
+
+Datum
+plvstr_substr3 (PG_FUNCTION_ARGS)
+{
+	return oracle_substr3(fcinfo);
+}
+
+
+/****************************************************************
+ * PLVchr.nth
+ *
+ * Syntax:
+ *   FUNCTION plvchr.nth (string_in IN VARCHAR,
+ * 					 nth_in IN INTEGER)
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to return the Nth character in a string.
+ *
+ ****************************************************************/
+
+Datum
+plvchr_nth (PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(ora_substr(PG_GETARG_DATUM(0), PG_GETARG_INT32(1), 1));
+}
+
+
+/****************************************************************
+ * PLVchr.first
+ *
+ * Syntax:
+ *   FUNCTION plvchr.first (string_in IN VARCHAR,
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to return the first character in a string.
+ *
+ ****************************************************************/
+
+Datum
+plvchr_first (PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(ora_substr(PG_GETARG_DATUM(0), 1, 1));
+}
+
+
+/****************************************************************
+ * PLVchr.last
+ *
+ * Syntax:
+ *   FUNCTION plvchr.last (string_in IN VARCHAR,
+ *  	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to return the last character in a string.
+ *
+ ****************************************************************/
+
+Datum
+plvchr_last (PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(ora_substr(PG_GETARG_DATUM(0), -1, 1));
+}
+
+
+/****************************************************************
+ * PLVchr.is_blank, plvchr.is_digit, ...
+ *
+ * Syntax:
+ *   FUNCTION plvchr.is_kind (string_in IN VARCHAR,
+ *      kind INT)
+ *          RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Call this function to see if a character is blank, ...
+ *   1 blank, 2 digit, 3 quote, 4 other, 5 letter
+ *
+ ****************************************************************/
+
+static bool
+is_kind(char c, int kind)
+{
+	switch (kind)
+	{
+		case 1:
+			return c ==' ';
+		case 2:
+			return !!(isdigit((unsigned char)c));
+		case 3:
+			return c == '\'';
+		case 4:
+			return
+				(32 <= c && c <= 47) ||
+				(58 <= c && c <= 64) ||
+				(91 <= c && c <= 96) || (123 <= c && c <= 126);
+		case 5:
+			return !!(isalpha((unsigned char)c));
+		default:
+			PARAMETER_ERROR("Second parameter isn't in enum {1,2,3,4,5}");
+			return false;
+	}
+}
+
+Datum
+plvchr_is_kind_i (PG_FUNCTION_ARGS)
+{
+	int32 c = PG_GETARG_INT32(0);
+	int32 k = PG_GETARG_INT32(1);
+
+	PG_RETURN_INT32(is_kind((char)c,k));
+}
+
+Datum
+plvchr_is_kind_a (PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	int32 k = PG_GETARG_INT32(1);
+	char c;
+
+	NON_EMPTY_CHECK(str);
+	if (pg_database_encoding_max_length() > 1)
+	{
+		if (_pg_mblen(VARDATA_ANY(str)) > 1)
+			PG_RETURN_INT32( (k == 5) );
+	}
+
+	c = *VARDATA_ANY(str);
+	PG_RETURN_INT32(is_kind(c,k));
+}
+
+
+/****************************************************************
+ * PLVchr.char_name
+ *
+ * Syntax:
+ *   FUNCTION plvchr.char_name (letter_in IN VARCHAR)
+ *   	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns the name of the character to ascii code as a VARCHAR.
+ *
+ ****************************************************************/
+
+Datum
+plvchr_char_name(PG_FUNCTION_ARGS)
+{
+	text *str = PG_GETARG_TEXT_PP(0);
+	text *result;
+	unsigned char c;
+
+	NON_EMPTY_CHECK(str);
+	c = (unsigned char)*(VARDATA_ANY(str));
+
+	if (c >= lengthof(char_names))
+		result = ora_substr_text(str, 1, 1);
+	else
+		result = cstring_to_text(char_names[c]);
+
+	PG_RETURN_TEXT_P(result);
+}
+
+
+/****************************************************************
+ * substr
+ *
+ * Syntax:
+ *   FUNCTION substr (string, start_position, [length])
+ *   	RETURN VARCHAR;
+ *
+ * Purpose:
+ *   Returns len chars from start_in position, compatible with Oracle
+ *
+ ****************************************************************/
+
+Datum
+oracle_substr3(PG_FUNCTION_ARGS)
+{
+	int32	len = PG_GETARG_INT32(2);
+	if (len < 0)
+		PG_RETURN_NULL();
+	PG_RETURN_TEXT_P(ora_substr(PG_GETARG_DATUM(0), PG_GETARG_INT32(1), len));
+}
+
+Datum
+oracle_substr2(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TEXT_P(ora_substr(PG_GETARG_DATUM(0), PG_GETARG_INT32(1), -1));
+}
+
+
+static text*
+ora_concat2(text *str1, text *str2)
+{
+	int l1;
+	int l2;
+	text *result;
+
+	l1 = VARSIZE_ANY_EXHDR(str1);
+	l2 = VARSIZE_ANY_EXHDR(str2);
+
+	result = palloc(l1+l2+VARHDRSZ);
+	memcpy(VARDATA(result), VARDATA_ANY(str1), l1);
+	memcpy(VARDATA(result) + l1, VARDATA_ANY(str2), l2);
+	SET_VARSIZE(result, l1 + l2 + VARHDRSZ);
+
+	return result;
+}
+
+
+static text*
+ora_concat3(text *str1, text *str2, text *str3)
+{
+	int l1;
+	int l2;
+	int l3;
+	text *result;
+
+	l1 = VARSIZE_ANY_EXHDR(str1);
+	l2 = VARSIZE_ANY_EXHDR(str2);
+	l3 = VARSIZE_ANY_EXHDR(str3);
+
+	result = palloc(l1+l2+l3+VARHDRSZ);
+	memcpy(VARDATA(result), VARDATA_ANY(str1), l1);
+	memcpy(VARDATA(result) + l1, VARDATA_ANY(str2), l2);
+	memcpy(VARDATA(result) + l1+l2, VARDATA_ANY(str3), l3);
+	SET_VARSIZE(result, l1 + l2 + l3 + VARHDRSZ);
+
+	return result;
+}
+
+
+/****************************************************************
+ * PLVchr.swap
+ *
+ * Syntax:
+ *    FUNCTION swap
+ *      (string_in IN VARCHAR2,
+ *       replace_in IN VARCHAR2,
+ *       start_in IN INTEGER := 1,
+ *       oldlen_in IN INTEGER := NULL)
+ *  RETURN VARCHAR2
+ *
+ * Purpose:
+ *   Replace a substring in a string with a specified string.
+ *
+ ****************************************************************/
+
+Datum
+plvstr_swap(PG_FUNCTION_ARGS)
+{
+	text *string_in;
+	text *replace_in;
+	int start_in = 1;
+	int oldlen_in;
+	int v_len;
+
+	if (PG_ARGISNULL(0))
+		PG_RETURN_NULL();
+	else
+		string_in = PG_GETARG_TEXT_P(0);
+
+	if (PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+	else
+		replace_in = PG_GETARG_TEXT_P(1);
+
+	if (!PG_ARGISNULL(2))
+		start_in = PG_GETARG_INT32(2);
+
+	if (PG_ARGISNULL(3))
+		oldlen_in = ora_mb_strlen1(replace_in);
+	else
+		oldlen_in = PG_GETARG_INT32(3);
+
+	v_len =  ora_mb_strlen1(string_in);
+
+	start_in = start_in > 0 ? start_in : v_len + start_in + 1;
+
+	if (start_in == 0 || start_in > v_len)
+		PG_RETURN_TEXT_P(TextPCopy(string_in));
+	else if (start_in == 1)
+		PG_RETURN_TEXT_P(ora_concat2(
+			replace_in, ora_substr_text(string_in, oldlen_in+1, -1)));
+	else
+		PG_RETURN_TEXT_P(ora_concat3(
+			ora_substr_text(string_in, 1, start_in - 1),
+			replace_in,
+			ora_substr_text(string_in, start_in + oldlen_in, -1)));
+}
+
+/****************************************************************
+ * PLVchr.betwn
+ *
+ * Find the Substring Between Start and End Locations
+ *
+ * Syntax:
+ *     FUNCTION plvstr.betwn (string_in IN VARCHAR2,
+ *       start_in IN INTEGER,
+ *       end_in IN INTEGER,
+ *       inclusive IN BOOLEAN := TRUE)
+ *      RETURN VARCHAR2;
+ *
+ *     FUNCTION plvstr.betwn (string_in IN VARCHAR2,
+ *       start_in IN VARCHAR2,
+ *       end_in IN VARCHAR2 := NULL,
+ *       startnth_in IN INTEGER := 1,
+ *       endnth_in IN INTEGER := 1,
+ *       inclusive IN BOOLEAN := TRUE,
+ *       gotoend IN BOOLEAN := FALSE)
+ *      RETURN VARCHAR2;
+ *
+ * Purpose:
+ *   Call this function to extract a sub-string from a string. This
+ * function is overloaded. You can either provide the start and end
+ * locations or you can provide start and end substrings.
+ *
+ ****************************************************************/
+
+
+Datum
+plvstr_betwn_i(PG_FUNCTION_ARGS)
+{
+	text *string_in = PG_GETARG_TEXT_P(0);
+	int start_in = PG_GETARG_INT32(1);
+	int end_in = PG_GETARG_INT32(2);
+	bool inclusive = PG_GETARG_BOOL(3);
+
+	if ((start_in < 0 && end_in > 0) ||
+		(start_in > 0 && end_in < 0) ||
+		(start_in > end_in))
+		PARAMETER_ERROR("Wrong positions.");
+
+	if (start_in < 0)
+	{
+		int v_len =  ora_mb_strlen1(string_in);
+		start_in = v_len + start_in + 1;
+		end_in = v_len + start_in + 1;
+	}
+
+	if (!inclusive)
+	{
+		start_in += 1;
+		end_in -= 1;
+
+		if (start_in > end_in)
+			PG_RETURN_TEXT_P(cstring_to_text(""));
+	}
+
+	PG_RETURN_TEXT_P(ora_substr_text(string_in,
+									 start_in,
+									 end_in - start_in + 1));
+}
+
+
+Datum
+plvstr_betwn_c(PG_FUNCTION_ARGS)
+{
+	text *string_in;
+	text *start_in;
+	text *end_in;
+	int startnth_in;
+	int endnth_in;
+	bool inclusive;
+	bool gotoend;
+
+	int v_start;
+	int v_end;
+
+	if (PG_ARGISNULL(0) || PG_ARGISNULL(1) ||
+		PG_ARGISNULL(3) || PG_ARGISNULL(4) ||
+		PG_ARGISNULL(5) || PG_ARGISNULL(6))
+		PG_RETURN_NULL();
+
+
+	string_in = PG_GETARG_TEXT_P(0);
+	start_in = PG_GETARG_TEXT_P(1);
+	end_in = PG_ARGISNULL(2) ? start_in : PG_GETARG_TEXT_P(2);
+	startnth_in = PG_GETARG_INT32(3);
+	endnth_in = PG_GETARG_INT32(4);
+	inclusive = PG_GETARG_BOOL(5);
+	gotoend = PG_GETARG_BOOL(6);
+
+	if (startnth_in == 0)
+	{
+		v_start = 1;
+		v_end = ora_instr(string_in, end_in, 1, endnth_in);
+	}
+	else
+	{
+		v_start = ora_instr(string_in, start_in, 1, startnth_in);
+		v_end = ora_instr(string_in, end_in, v_start + 1, endnth_in);
+	}
+
+	if (v_start == 0)
+		PG_RETURN_NULL();
+
+	if (!inclusive)
+	{
+		if (startnth_in > 0)
+			v_start += ora_mb_strlen1(start_in);
+
+		v_end -= 1;
+	}
+	else
+		v_end += (ora_mb_strlen1(end_in) - 1);
+
+	if (((v_start > v_end) && (v_end > 0)) ||
+		(v_end <= 0 && !gotoend))
+		PG_RETURN_NULL();
+
+	if (v_end <= 0)
+		v_end = ora_mb_strlen1(string_in);
+
+	PG_RETURN_TEXT_P(ora_substr_text(string_in,
+									 v_start,
+									 v_end - v_start + 1));
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/plvsubst.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/plvsubst.c b/contrib/orafce/plvsubst.c
new file mode 100644
index 0000000..3017a7b
--- /dev/null
+++ b/contrib/orafce/plvsubst.c
@@ -0,0 +1,252 @@
+/*
+  This code implements one part of functonality of
+  free available library PL/Vision. Please look www.quest.com
+
+  Original author: Steven Feuerstein, 1996 - 2002
+  PostgreSQL implementation author: Pavel Stehule, 2006
+
+  This module is under BSD Licence
+
+  History:
+    1.0. first public version 22. September 2006
+
+*/
+
+#include "postgres.h"
+#include "utils/builtins.h"
+#include "utils/numeric.h"
+#include "string.h"
+#include "stdlib.h"
+#include "utils/pg_locale.h"
+#include "mb/pg_wchar.h"
+#include "lib/stringinfo.h"
+
+#include "catalog/pg_type.h"
+#include "libpq/pqformat.h"
+#include "utils/array.h"
+#include "utils/memutils.h"
+#include "utils/lsyscache.h"
+#include "access/tupmacs.h"
+#include "orafunc.h"
+#include "builtins.h"
+
+PG_FUNCTION_INFO_V1(plvsubst_string_array);
+PG_FUNCTION_INFO_V1(plvsubst_string_string);
+PG_FUNCTION_INFO_V1(plvsubst_setsubst);
+PG_FUNCTION_INFO_V1(plvsubst_setsubst_default);
+PG_FUNCTION_INFO_V1(plvsubst_subst);
+
+#define C_SUBST  "%s"
+
+
+text *c_subst = NULL;
+
+static void
+init_c_subst()
+{
+	if (!c_subst)
+	{
+		MemoryContext oldctx;
+
+		oldctx = MemoryContextSwitchTo(TopMemoryContext);
+		c_subst = cstring_to_text(C_SUBST);
+		MemoryContextSwitchTo(oldctx);
+	}
+}
+
+static void
+set_c_subst(text *sc)
+{
+	MemoryContext oldctx;
+
+	if (c_subst)
+		pfree(c_subst);
+
+	oldctx = MemoryContextSwitchTo(TopMemoryContext);
+	c_subst = sc ? TextPCopy(sc) : cstring_to_text(C_SUBST);
+	MemoryContextSwitchTo(oldctx);
+}
+
+static text*
+plvsubst_string(text *template_in, ArrayType *vals_in, text *c_subst, FunctionCallInfo fcinfo)
+{
+	ArrayType	   *v = vals_in;
+	int				nitems,
+				   *dims,
+					ndims;
+	char		   *p;
+	int16			typlen;
+	bool			typbyval;
+	char			typalign;
+	char			typdelim;
+	Oid				typelem;
+	Oid				typiofunc;
+	FmgrInfo		proc;
+	int				i = 0, items = 0;
+	StringInfo		sinfo;
+	const char	   *template_str;
+	int				template_len;
+	char		   *sizes;
+	int			   *positions;
+	int				subst_mb_len;
+	int				subst_len;
+	const bits8	   *bitmap;
+	int				bitmask;
+
+	if (v != NULL && (ndims = ARR_NDIM(v)) > 0)
+	{
+		if (ndims != 1)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("invalid parameter"),
+					 errdetail("Array of arguments has wrong dimension: %d", ndims)));
+
+		p = ARR_DATA_PTR(v);
+		dims = ARR_DIMS(v);
+		nitems = ArrayGetNItems(ndims, dims);
+		bitmap = ARR_NULLBITMAP(v);
+		get_type_io_data(ARR_ELEMTYPE(v), IOFunc_output,
+							&typlen, &typbyval,
+							&typalign, &typdelim,
+							&typelem, &typiofunc);
+		fmgr_info_cxt(typiofunc, &proc, fcinfo->flinfo->fn_mcxt);
+	}
+	else
+	{
+		nitems = 0;
+		p = NULL;
+		bitmap = NULL;
+	}
+
+	template_str = VARDATA(template_in);
+	template_len = ora_mb_strlen(template_in, &sizes, &positions);
+	subst_mb_len = ora_mb_strlen1(c_subst);
+	subst_len = VARSIZE_ANY_EXHDR(c_subst);
+	sinfo = makeStringInfo();
+
+	bitmask = 1;
+	for (i = 0; i < template_len; i++)
+	{
+		if (strncmp(&template_str[positions[i]], VARDATA(c_subst), subst_len) == 0)
+		{
+			Datum    itemvalue;
+			char     *value;
+
+			if (items++ < nitems)
+			{
+				if (bitmap && (*bitmap & bitmask) == 0)
+					value = pstrdup("NULL");
+				else
+				{
+					itemvalue = fetch_att(p, typbyval, typlen);
+					value = DatumGetCString(FunctionCall3(&proc,
+								itemvalue,
+								ObjectIdGetDatum(typelem),
+								Int32GetDatum(-1)));
+
+					p = att_addlength_pointer(p, typlen, p);
+					p = (char *) att_align_nominal(p, typalign);
+				}
+				appendStringInfoString(sinfo, value);
+				pfree(value);
+
+				if (bitmap)
+				{
+					bitmask <<= 1;
+					if (bitmask == 0x100)
+					{
+						bitmap++;
+						bitmask = 1;
+					}
+				}
+			}
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("too few parameters specified for template string")));
+
+			i += subst_mb_len - 1;
+		}
+		else
+			appendBinaryStringInfo(sinfo, &template_str[positions[i]], sizes[i]);
+	}
+
+	return cstring_to_text(sinfo->data);
+}
+
+
+Datum
+plvsubst_string_array(PG_FUNCTION_ARGS)
+{
+	init_c_subst();
+
+	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(plvsubst_string(PG_GETARG_TEXT_P(0),
+					 PG_GETARG_ARRAYTYPE_P(1),
+					 PG_ARGISNULL(2) ? c_subst : PG_GETARG_TEXT_P(2),
+					 fcinfo));
+}
+
+Datum
+plvsubst_string_string(PG_FUNCTION_ARGS)
+{
+	Datum		r;
+	ArrayType  *array;
+	FunctionCallInfoData locfcinfo;
+
+	init_c_subst();
+
+	if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+		PG_RETURN_NULL();
+
+	/*
+	 * I can't use DirectFunctionCall2
+	 */
+
+	InitFunctionCallInfoData(locfcinfo, fcinfo->flinfo, 2, NULL, NULL);
+	locfcinfo.arg[0] = PG_GETARG_DATUM(1);
+	locfcinfo.arg[1] = PG_GETARG_IF_EXISTS(2, DATUM, CStringGetTextDatum(","));
+	locfcinfo.argnull[0] = false;
+	locfcinfo.argnull[1] = false;
+	r = text_to_array(&locfcinfo);
+
+	if (locfcinfo.isnull || r == (Datum) 0)
+		array = NULL;
+	else
+		array = DatumGetArrayTypeP(r);
+
+	PG_RETURN_TEXT_P(plvsubst_string(PG_GETARG_TEXT_P(0),
+					 array,
+					 PG_GETARG_IF_EXISTS(3, TEXT_P, c_subst),
+					 fcinfo));
+}
+
+Datum
+plvsubst_setsubst(PG_FUNCTION_ARGS)
+{
+	if (PG_ARGISNULL(0))
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("substition is NULL"),
+				 errdetail("Substitution keyword may not be NULL.")));
+
+	set_c_subst(PG_GETARG_TEXT_P(0));
+	PG_RETURN_VOID();
+}
+
+Datum
+plvsubst_setsubst_default(PG_FUNCTION_ARGS)
+{
+	set_c_subst(NULL);
+	PG_RETURN_VOID();
+}
+
+
+Datum
+plvsubst_subst(PG_FUNCTION_ARGS)
+{
+	init_c_subst();
+	PG_RETURN_TEXT_P(TextPCopy(c_subst));
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/putline.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/putline.c b/contrib/orafce/putline.c
new file mode 100644
index 0000000..288ca59
--- /dev/null
+++ b/contrib/orafce/putline.c
@@ -0,0 +1,347 @@
+#include "postgres.h"
+#include "funcapi.h"
+#include "access/heapam.h"
+#include "catalog/pg_type.h"
+#include "lib/stringinfo.h"
+
+#include "libpq/libpq.h"
+#include "libpq/pqformat.h"
+#include "utils/memutils.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+
+#include "orafunc.h"
+#include "builtins.h"
+
+extern PGDLLIMPORT ProtocolVersion FrontendProtocol;
+
+/*
+ * TODO: BUFSIZE_UNLIMITED to be truely unlimited (or INT_MAX),
+ * and allocate buffers on-demand.
+ */
+#define BUFSIZE_DEFAULT		20000
+#define BUFSIZE_MIN			2000
+#define BUFSIZE_MAX			1000000
+#define BUFSIZE_UNLIMITED	BUFSIZE_MAX
+
+static bool is_server_output = false;
+static char *buffer = NULL;
+static int   buffer_size = 0;	/* allocated bytes in buffer */
+static int   buffer_len = 0;	/* used bytes in buffer */
+static int   buffer_get = 0;	/* retrieved bytes in buffer */
+
+static void add_str(const char *str, int len);
+static void add_text(text *str);
+static void add_newline(void);
+static void send_buffer(void);
+
+/*
+ * Aux. buffer functionality
+ */
+static void
+add_str(const char *str, int len)
+{
+	/* Discard all buffers if get_line was called. */
+	if (buffer_get > 0)
+	{
+		buffer_get = 0;
+		buffer_len = 0;
+	}
+
+	if (buffer_len + len > buffer_size)
+		ereport(ERROR,
+			(errcode(ERRCODE_INSUFFICIENT_RESOURCES),
+			 errmsg("buffer overflow"),
+			 errdetail("Buffer overflow, limit of %d bytes", buffer_size),
+			 errhint("Increase buffer size in dbms_output.enable() next time")));
+
+	memcpy(buffer + buffer_len, str, len);
+	buffer_len += len;
+	buffer[buffer_len] = '\0';
+}
+
+static void
+add_text(text *str)
+{
+	add_str(VARDATA_ANY(str), VARSIZE_ANY_EXHDR(str));
+}
+
+static void
+add_newline(void)
+{
+	add_str("", 1);	/* add \0 */
+	if (is_server_output)
+		send_buffer();
+}
+
+
+static void
+send_buffer()
+{
+    if (buffer_len > 0)
+    {
+	StringInfoData msgbuf;
+	char *cursor = buffer;
+
+	while (--buffer_len > 0)
+	{
+	    if (*cursor == '\0')
+		*cursor = '\n';
+	    cursor++;
+	}
+
+	if (*cursor != '\0')
+	        ereport(ERROR,
+		        (errcode(ERRCODE_INTERNAL_ERROR),
+			 errmsg("internal error"),
+		         errdetail("Wrong message format detected")));
+
+	pq_beginmessage(&msgbuf, 'N');
+
+	if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 3)
+	{
+		pq_sendbyte(&msgbuf, PG_DIAG_MESSAGE_PRIMARY);
+		pq_sendstring(&msgbuf, buffer);
+		pq_sendbyte(&msgbuf, '\0');
+	}
+	else
+	{
+		*cursor++ = '\n';
+		*cursor = '\0';
+		pq_sendstring(&msgbuf, buffer);
+	}
+
+	pq_endmessage(&msgbuf);
+	pq_flush();
+    }
+}
+
+
+/*
+ * Aux db functions
+ *
+ */
+
+static void
+dbms_output_enable_internal(int32 n_buf_size)
+{
+	/* We allocate +2 bytes for an end-of-line and a string terminator. */
+	if (buffer == NULL)
+	{
+		buffer = MemoryContextAlloc(TopMemoryContext, n_buf_size + 2);
+		buffer_size = n_buf_size;
+		buffer_len = 0;
+		buffer_get = 0;
+	}
+	else if (n_buf_size > buffer_len)
+	{
+		/* We cannot shrink buffer less than current length. */
+		buffer = repalloc(buffer, n_buf_size + 2);
+		buffer_size = n_buf_size;
+	}
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_enable_default);
+
+Datum
+dbms_output_enable_default(PG_FUNCTION_ARGS)
+{
+	dbms_output_enable_internal(BUFSIZE_DEFAULT);
+    PG_RETURN_VOID();
+}
+
+
+PG_FUNCTION_INFO_V1(dbms_output_enable);
+
+Datum
+dbms_output_enable(PG_FUNCTION_ARGS)
+{
+	int32 n_buf_size;
+
+	if (PG_ARGISNULL(0))
+		n_buf_size = BUFSIZE_UNLIMITED;
+	else
+	{
+		n_buf_size = PG_GETARG_INT32(0);
+
+		if (n_buf_size > BUFSIZE_MAX)
+		{
+			n_buf_size = BUFSIZE_MAX;
+			elog(WARNING, "Limit decreased to %d bytes.", BUFSIZE_MAX);
+		}
+		else if (n_buf_size < BUFSIZE_MIN)
+		{
+			n_buf_size = BUFSIZE_MIN;
+			elog(WARNING, "Limit increased to %d bytes.", BUFSIZE_MIN);
+		}
+	}
+
+	dbms_output_enable_internal(n_buf_size);
+	PG_RETURN_VOID();
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_disable);
+
+Datum
+dbms_output_disable(PG_FUNCTION_ARGS)
+{
+    if (buffer)
+        pfree(buffer);
+    buffer = NULL;
+    buffer_size = 0;
+    buffer_len = 0;
+    buffer_get = 0;
+    PG_RETURN_VOID();
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_serveroutput);
+
+Datum
+dbms_output_serveroutput(PG_FUNCTION_ARGS)
+{
+	is_server_output = PG_GETARG_BOOL(0);
+	if (is_server_output && !buffer)
+		dbms_output_enable_internal(BUFSIZE_DEFAULT);
+	PG_RETURN_VOID();
+}
+
+
+/*
+ * main functions
+ */
+
+PG_FUNCTION_INFO_V1(dbms_output_put);
+
+Datum
+dbms_output_put(PG_FUNCTION_ARGS)
+{
+    if (buffer)
+		add_text(PG_GETARG_TEXT_PP(0));
+    PG_RETURN_VOID();
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_put_line);
+
+Datum
+dbms_output_put_line(PG_FUNCTION_ARGS)
+{
+    if (buffer)
+    {
+		add_text(PG_GETARG_TEXT_PP(0));
+		add_newline();
+    }
+    PG_RETURN_VOID();
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_new_line);
+
+Datum
+dbms_output_new_line(PG_FUNCTION_ARGS)
+{
+    if (buffer)
+		add_newline();
+    PG_RETURN_VOID();
+}
+
+static text *
+dbms_output_next(void)
+{
+	if (buffer_get < buffer_len)
+	{
+		text *line = cstring_to_text(buffer + buffer_get);
+		buffer_get += VARSIZE_ANY_EXHDR(line) + 1;
+		return line;
+	}
+	else
+		return NULL;
+}
+
+PG_FUNCTION_INFO_V1(dbms_output_get_line);
+
+Datum
+dbms_output_get_line(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		result;
+	HeapTuple	tuple;
+	Datum		values[2];
+	bool		nulls[2] = { false, false };
+	text	   *line;
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	if ((line = dbms_output_next()) != NULL)
+	{
+		values[0] = PointerGetDatum(line);
+		values[1] = Int32GetDatum(0);	/* 0: succeeded */
+	}
+	else
+	{
+		nulls[0] = true;
+		values[1] = Int32GetDatum(1);	/* 1: failed */
+	}
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}
+
+
+PG_FUNCTION_INFO_V1(dbms_output_get_lines);
+
+Datum
+dbms_output_get_lines(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		result;
+	HeapTuple	tuple;
+	Datum		values[2];
+	bool		nulls[2] = { false, false };
+	text	   *line;
+
+	int32		max_lines = PG_GETARG_INT32(0);
+	int32		n;
+    ArrayBuildState *astate = NULL;
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	for (n = 0; n < max_lines && (line = dbms_output_next()) != NULL; n++)
+	{
+		astate = accumArrayResult(astate, PointerGetDatum(line), false,
+					TEXTOID, CurrentMemoryContext);
+	}
+
+	/* 0: lines as text array */
+	if (n > 0)
+		values[0] = makeArrayResult(astate, CurrentMemoryContext);
+	else
+	{
+		int16		typlen;
+		bool		typbyval;
+		char		typalign;
+		ArrayType  *arr;
+
+		get_typlenbyvalalign(TEXTOID, &typlen, &typbyval, &typalign);
+		arr = construct_md_array(
+			NULL,
+#if PG_VERSION_NUM >= 80200
+			NULL,
+#endif
+			0, NULL, NULL, TEXTOID, typlen, typbyval, typalign);
+		values[0] = PointerGetDatum(arr);
+	}
+
+	/* 1: # of lines as integer */
+	values[1] = Int32GetDatum(n);
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/random.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/random.c b/contrib/orafce/random.c
new file mode 100644
index 0000000..be39e1b
--- /dev/null
+++ b/contrib/orafce/random.c
@@ -0,0 +1,358 @@
+/*
+ * Note - I don't find any documentation about pseudo random
+ * number generator used in Oracle. So the results of these
+ * functions should be different then native Oracle functions!
+ * This library is based on ANSI C implementation.
+ */
+
+#include "postgres.h"
+#include "access/hash.h"
+#include "lib/stringinfo.h"
+#include "utils/builtins.h"
+
+#include "stdlib.h"
+#include "time.h"
+#include <math.h>
+#include <errno.h>
+
+#include "orafunc.h"
+#include "builtins.h"
+
+PG_FUNCTION_INFO_V1(dbms_random_initialize);
+PG_FUNCTION_INFO_V1(dbms_random_normal);
+PG_FUNCTION_INFO_V1(dbms_random_random);
+PG_FUNCTION_INFO_V1(dbms_random_seed_int);
+PG_FUNCTION_INFO_V1(dbms_random_seed_varchar);
+PG_FUNCTION_INFO_V1(dbms_random_string);
+PG_FUNCTION_INFO_V1(dbms_random_terminate);
+PG_FUNCTION_INFO_V1(dbms_random_value);
+PG_FUNCTION_INFO_V1(dbms_random_value_range);
+
+/* Coefficients in rational approximations. */
+static const double a[] =
+{
+	-3.969683028665376e+01,
+	 2.209460984245205e+02,
+	-2.759285104469687e+02,
+	 1.383577518672690e+02,
+	-3.066479806614716e+01,
+	 2.506628277459239e+00
+};
+
+static const double b[] =
+{
+	-5.447609879822406e+01,
+	 1.615858368580409e+02,
+	-1.556989798598866e+02,
+	 6.680131188771972e+01,
+	-1.328068155288572e+01
+};
+
+static const double c[] =
+{
+	-7.784894002430293e-03,
+	-3.223964580411365e-01,
+	-2.400758277161838e+00,
+	-2.549732539343734e+00,
+	 4.374664141464968e+00,
+	 2.938163982698783e+00
+};
+
+static const double d[] =
+{
+	7.784695709041462e-03,
+	3.224671290700398e-01,
+	2.445134137142996e+00,
+	3.754408661907416e+00
+};
+
+#define LOW 0.02425
+#define HIGH 0.97575
+
+static double ltqnorm(double p);
+
+
+/* 
+ * dbms_random.initialize (seed IN BINARY_INTEGER)
+ *
+ *     Initialize package with a seed value
+ */
+Datum 
+dbms_random_initialize(PG_FUNCTION_ARGS)
+{
+	int seed = PG_GETARG_INT32(0);
+
+	srand(seed);
+	
+	PG_RETURN_VOID();
+}
+
+/*
+ * dbms_random.normal() RETURN NUMBER;
+ *
+ *     Returns random numbers in a standard normal distribution
+ */
+Datum
+dbms_random_normal(PG_FUNCTION_ARGS)
+{
+	float8 result;
+	
+	/* need random value from (0..1) */
+	result = ltqnorm(((double) rand() + 1) / ((double) RAND_MAX + 2));
+
+	PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * dbms_random.random() RETURN BINARY_INTEGER;
+ *
+ *     Generate Random Numeric Values
+ */
+Datum
+dbms_random_random(PG_FUNCTION_ARGS)
+{
+	int result;
+	/* 
+	 * Oracle generator generates numebers from -2^31 and +2^31,
+	 * ANSI C only from 0 .. RAND_MAX, 
+	 */ 
+	result = 2 * (rand() - RAND_MAX / 2);
+
+	PG_RETURN_INT32(result);
+}
+
+/* 
+ * dbms_random.seed(val IN BINARY_INTEGER);
+ * dbms_random.seed(val IN VARCHAR2);
+ *
+ *     Reset the seed value
+ */
+Datum
+dbms_random_seed_int(PG_FUNCTION_ARGS)
+{
+	int seed = PG_GETARG_INT32(0);
+	
+	srand(seed);
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * Atention! 
+ *
+ * Hash function should be changed between mayor pg versions,
+ * don't use text based seed for regres tests!
+ */
+Datum
+dbms_random_seed_varchar(PG_FUNCTION_ARGS)
+{
+	text *key = PG_GETARG_TEXT_P(0);
+	Datum seed;
+	
+	seed = hash_any((unsigned char *) VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key));
+	
+	srand((int) seed);
+					    
+	PG_RETURN_VOID();
+}
+
+/*
+ * dbms_random.string(opt IN CHAR, len IN NUMBER) RETURN VARCHAR2;
+ * 
+ *     Create Random Strings
+ * opt seed values:
+ * 'a','A'  alpha characters only (mixed case)
+ * 'l','L'  lower case alpha characters only
+ * 'p','P'  any printable characters
+ * 'u','U'  upper case alpha characters only
+ * 'x','X'  any alpha-numeric characters (upper)
+ */
+static text *
+random_string(const char *charset, int chrset_size, int len)
+{
+	StringInfo	str;
+	int	i;
+	
+	str = makeStringInfo();
+	for (i = 0; i < len; i++)
+	{
+		int pos = (double) rand() / ((double) RAND_MAX + 1) * chrset_size;
+		
+		appendStringInfoChar(str, charset[pos]);
+	}
+	
+	return cstring_to_text(str->data);
+}
+
+Datum
+dbms_random_string(PG_FUNCTION_ARGS)
+{
+	char *option;
+	int	len;
+	const char *charset;
+	int chrset_size;
+
+	const char *alpha_mixed = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
+	const char *lower_only = "abcdefghijklmnopqrstuvwxyz";
+	const char *upper_only = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
+	const char *upper_alphanum = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
+	const char *printable = "`1234567890-=qwertyuiop[]asdfghjkl;'zxcvbnm,./!@#$%^&*()_+QWERTYUIOP{}|ASDFGHJKL:\"ZXCVVBNM<>? ";
+	
+	option = text_to_cstring(PG_GETARG_TEXT_P(0));
+	len = PG_GETARG_INT32(1);
+	
+	switch (option[0])
+	{
+		case 'a':
+		case 'A':
+			charset = alpha_mixed;
+			chrset_size = strlen(alpha_mixed);
+			break;
+		case 'l':
+		case 'L':
+			charset = lower_only;
+			chrset_size = strlen(lower_only);
+			break;
+		case 'u':
+		case 'U':
+			charset = upper_only;
+			chrset_size = strlen(upper_only);
+			break;
+		case 'x':
+		case 'X':
+			charset = upper_alphanum;
+			chrset_size = strlen(upper_alphanum);
+			break;
+		case 'p':
+		case 'P':
+			charset = printable;
+			chrset_size = strlen(printable);
+			break;
+			
+		default:
+			ereport(ERROR, 
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE), 
+				 errmsg("unknown option '%s'", option),
+				 errhint("available option \"aAlLuUxXpP\"")));
+			/* be compiler a quiete */
+			charset = NULL;
+			chrset_size = 0;
+	}
+	
+	PG_RETURN_TEXT_P(random_string(charset, chrset_size, len));
+}
+
+/*
+ * dbms_random.terminate;
+ *
+ *     Terminate use of the Package
+ */
+Datum
+dbms_random_terminate(PG_FUNCTION_ARGS)
+{
+	/* do nothing */
+	PG_RETURN_VOID();
+}
+
+/*
+ * dbms_random.value() RETURN NUMBER;
+ *
+ *     Gets a random number, greater than or equal to 0 and less than 1.
+ */
+Datum
+dbms_random_value(PG_FUNCTION_ARGS)
+{
+	float8 result;
+	
+	/* result [0.0 - 1.0) */
+	result = (double) rand() / ((double) RAND_MAX + 1);
+	
+	PG_RETURN_FLOAT8(result);
+}
+
+/*
+ * dbms_random.value(low  NUMBER, high NUMBER) RETURN NUMBER
+ *
+ *     Alternatively, you can get a random Oracle number x, 
+ *     where x is greater than or equal to low and less than high 
+ */
+Datum
+dbms_random_value_range(PG_FUNCTION_ARGS)
+{
+	float8 low = PG_GETARG_FLOAT8(0);
+	float8 high = PG_GETARG_FLOAT8(1);
+	float8 result;
+	
+	if (low > high)
+		PG_RETURN_NULL();
+	
+	result = ((double) rand() / ((double) RAND_MAX + 1)) * ( high -  low) + low;
+	
+	PG_RETURN_FLOAT8(result);
+}
+
+
+/*
+ * Lower tail quantile for standard normal distribution function.
+ *
+ * This function returns an approximation of the inverse cumulative
+ * standard normal distribution function.  I.e., given P, it returns
+ * an approximation to the X satisfying P = Pr{Z <= X} where Z is a
+ * random variable from the standard normal distribution.
+ *
+ * The algorithm uses a minimax approximation by rational functions
+ * and the result has a relative error whose absolute value is less
+ * than 1.15e-9.
+ *
+ * Author:      Peter J. Acklam
+ * Time-stamp:  2002-06-09 18:45:44 +0200
+ * E-mail:      jacklam@math.uio.no
+ * WWW URL:     http://www.math.uio.no/~jacklam
+ *
+ * C implementation adapted from Peter's Perl version
+ */
+static double
+ltqnorm(double p)
+{
+	double q, r;
+
+	errno = 0;
+
+	if (p < 0 || p > 1)
+	{
+		errno = EDOM;
+		return 0.0;
+	}
+	else if (p == 0)
+	{
+		errno = ERANGE;
+		return -HUGE_VAL /* minus "infinity" */;
+	}
+	else if (p == 1)
+	{
+		errno = ERANGE;
+		return HUGE_VAL /* "infinity" */;
+	}
+	else if (p < LOW)
+	{
+		/* Rational approximation for lower region */
+		q = sqrt(-2*log(p));
+		return (((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) /
+			((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1);
+	}
+	else if (p > HIGH)
+	{
+		/* Rational approximation for upper region */
+		q  = sqrt(-2*log(1-p));
+		return -(((((c[0]*q+c[1])*q+c[2])*q+c[3])*q+c[4])*q+c[5]) /
+			((((d[0]*q+d[1])*q+d[2])*q+d[3])*q+1);
+	}
+	else
+	{
+		/* Rational approximation for central region */
+    		q = p - 0.5;
+    		r = q*q;
+		return (((((a[0]*r+a[1])*r+a[2])*r+a[3])*r+a[4])*r+a[5])*q /
+			(((((b[0]*r+b[1])*r+b[2])*r+b[3])*r+b[4])*r+1);
+	}
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/shmmc.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/shmmc.c b/contrib/orafce/shmmc.c
new file mode 100644
index 0000000..0a61d92
--- /dev/null
+++ b/contrib/orafce/shmmc.c
@@ -0,0 +1,341 @@
+/*
+ *
+ * Shared memory control - based on alocating chunks aligned on
+ * asize array (fibonachi), and dividing free bigger block.
+ *
+ */
+
+#include "postgres.h"
+#include "shmmc.h"
+#include "stdlib.h"
+#include "string.h"
+#include "orafunc.h"
+
+
+#define LIST_ITEMS  512
+
+int context;
+
+typedef struct {
+	size_t size;
+	void* first_byte_ptr;
+	bool dispossible;
+/*	int16 context; */
+} list_item;
+
+typedef struct {
+	int list_c;
+	int max_size;
+	vardata data[1];	 /* flexible array member */
+} mem_desc;
+
+#define MAX_SIZE 82688
+
+static size_t asize[] = {
+	32,
+	64,       96,   160,  256,
+	416,     672,  1088,  1760,
+	2848,   4608,  7456, 12064,
+	19520, 31584, 51104, 82688};
+
+
+int *list_c = NULL;
+list_item *list = NULL;
+size_t max_size;
+
+int cycle = 0;
+
+
+/* align requested size */
+
+static int
+ptr_comp(const void* a, const void* b)
+{
+	list_item *_a = (list_item*) a;
+	list_item *_b = (list_item*) b;
+
+	return (long)_a->first_byte_ptr - (long)_b->first_byte_ptr;
+}
+
+char *
+ora_sstrcpy(char *str)
+{
+	int len;
+	char *result;
+
+	len = strlen(str);
+	if (NULL != (result = ora_salloc(len+1)))
+		memcpy(result, str, len + 1);
+	else
+		ereport(ERROR,
+			(errcode(ERRCODE_OUT_OF_MEMORY),
+			errmsg("out of memory"),
+			errdetail("Failed while allocation block %d bytes in shared memory.", len+1),
+			errhint("Increase SHMEMMSGSZ and recompile package.")));
+
+	return result;
+}
+
+char *
+ora_scstring(text *str)
+{
+	int len;
+	char *result;
+
+	len = VARSIZE_ANY_EXHDR(str);
+
+	if (NULL != (result = ora_salloc(len+1)))
+	{
+		memcpy(result, VARDATA_ANY(str), len);
+		result[len] = '\0';
+	}
+	else
+		ereport(ERROR,
+			(errcode(ERRCODE_OUT_OF_MEMORY),
+			errmsg("out of memory"),
+			errdetail("Failed while allocation block %d bytes in shared memory.", len+1),
+			errhint("Increase SHMEMMSGSZ and recompile package.")));
+
+	return result;
+}
+
+/*
+ * Compact the list of slots, by merging adjacent unused slots into larger
+ * slots.
+ */
+static void
+defragmentation()
+{
+	int src, target;
+
+	/* Sort the array to pointer order */
+	qsort(list, *list_c, sizeof(list_item), ptr_comp);
+
+	/* Merge adjacent dispossible slots, and move up other slots */
+	target = 0;
+	for (src = 0; src < *list_c; src++)
+	{
+		if (target > 0 &&
+			list[src].dispossible &&
+			list[target - 1].dispossible)
+		{
+			list[target - 1].size += list[src].size;
+		}
+		else
+		{
+			if (src != target)
+				memcpy(&list[target], &list[src], sizeof(list_item));
+			target++;
+		}
+	}
+	*list_c = target;
+}
+
+static size_t
+align_size(size_t size)
+{
+	int i;
+
+	/* default, we can allocate max MAX_SIZE memory block */
+
+	for (i = 0; i < 17; i++)
+		if (asize[i] >= size)
+			return asize[i];
+
+	ereport(ERROR,
+		   (errcode(ERRCODE_OUT_OF_MEMORY),
+		    errmsg("too much large memory block request"),
+		    errdetail("Failed while allocation block %lu bytes in shared memory.", (unsigned long) size),
+		    errhint("Increase MAX_SIZE constant, fill table a_size and recompile package.")));
+
+	return 0;
+}
+
+/*
+  inicialize shared memory. It works in two modes, create and no create.
+  No create is used for mounting shared memory buffer. Top of memory is
+  used for list_item array.
+*/
+
+void
+ora_sinit(void *ptr, size_t size, bool create)
+{
+	if (list == NULL)
+	{
+		mem_desc *m = (mem_desc*)ptr;
+		list = (list_item*)m->data;
+		list_c = &m->list_c;
+		max_size = m->max_size = size;
+
+		if (create)
+		{
+			list[0].size = size - sizeof(list_item)*LIST_ITEMS - sizeof(mem_desc);
+			list[0].first_byte_ptr = &m->data + sizeof(list_item)*LIST_ITEMS;
+			list[0].dispossible = true;
+			*list_c = 1;
+		}
+	}
+}
+
+
+void*
+ora_salloc(size_t size)
+{
+	size_t aligned_size;
+	int repeat_c;
+	void *ptr = NULL;
+
+	aligned_size = align_size(size);
+
+	for (repeat_c = 0; repeat_c < 2; repeat_c++)
+	{
+		size_t	max_min = max_size;
+		int		select = -1;
+		int		i;
+
+		/* find first good free block */
+		for (i = 0; i < *list_c; i++)
+		{
+			if (list[i].dispossible)
+			{
+				/* If this block is just the right size, return it */
+				if (list[i].size == aligned_size)
+				{
+					list[i].dispossible = false;
+					ptr = list[i].first_byte_ptr;
+					/* list[i].context = context; */
+
+					return ptr;
+				}
+
+				if (list[i].size > aligned_size && list[i].size < max_min)
+				{
+					max_min = list[i].size;
+					select = i;
+				}
+			}
+		}
+
+		/* If no suitable free slot found, defragment and try again. */
+		if (select == -1 || *list_c == LIST_ITEMS)
+		{
+			defragmentation();
+			continue;
+		}
+
+		/*
+		 * A slot larger than required was found. Divide it to avoid wasting
+		 * space, and return the slot of the right size.
+		 */
+		list[*list_c].size = list[select].size - aligned_size;
+		list[*list_c].first_byte_ptr = (char*)list[select].first_byte_ptr + aligned_size;
+		list[*list_c].dispossible = true;
+		list[select].size = aligned_size;
+		list[select].dispossible = false;
+		/* list[select].context = context; */
+		ptr = list[select].first_byte_ptr;
+		*list_c += 1;
+ 		break;
+	}
+
+	return ptr;
+}
+
+void
+ora_sfree(void* ptr)
+{
+	int i;
+
+/*
+	if (cycle++ % 100 == 0)
+	{
+		size_t suma = 0;
+		for (i = 0; i < *list_c; i++)
+			if (list[i].dispossible)
+				suma += list[i].size;
+		elog(NOTICE, "=============== FREE MEM REPORT === %10d ================", suma);
+	}
+*/
+
+	for (i = 0; i < *list_c; i++)
+		if (list[i].first_byte_ptr == ptr)
+		{
+			list[i].dispossible = true;
+			/* list[i].context = -1; */
+			memset(list[i].first_byte_ptr, '#', list[i].size);
+			return;
+		}
+
+	ereport(ERROR,
+			(errcode(ERRCODE_INTERNAL_ERROR),
+			 errmsg("corrupted pointer"),
+			 errdetail("Failed while reallocating memory block in shared memory."),
+			 errhint("Report this bug to autors.")));
+}
+
+
+void*
+ora_srealloc(void *ptr, size_t size)
+{
+	void *result;
+	size_t aux_s = 0;
+	int i;
+
+	for (i = 0; i < *list_c; i++)
+		if (list[i].first_byte_ptr == ptr)
+		{
+			if (align_size(size) <= list[i].size)
+				return ptr;
+			aux_s = list[i].size;
+		}
+
+	if (aux_s == 0)
+		ereport(ERROR,
+			(errcode(ERRCODE_INTERNAL_ERROR),
+			errmsg("corrupted pointer"),
+			errdetail("Failed while reallocating memory block in shared memory."),
+			errhint("Report this bug to autors.")));
+
+
+	if (NULL != (result = ora_salloc(size)))
+	{
+		memcpy(result, ptr, aux_s);
+		ora_sfree(ptr);
+	}
+
+	return result;
+}
+
+/*
+ *  alloc shared memory, raise exception if not
+ */
+
+void*
+salloc(size_t size)
+{
+	void* result;
+
+	if (NULL == (result = ora_salloc(size)))
+		ereport(ERROR,
+			(errcode(ERRCODE_OUT_OF_MEMORY),
+			errmsg("out of memory"),
+			errdetail("Failed while allocation block %lu bytes in shared memory.", (unsigned long) size),
+			errhint("Increase SHMEMMSGSZ and recompile package.")));
+
+	return result;
+}
+
+void*
+srealloc(void *ptr, size_t size)
+{
+	void* result;
+
+	if (NULL == (result = ora_srealloc(ptr, size)))
+		ereport(ERROR,
+			(errcode(ERRCODE_OUT_OF_MEMORY),
+			errmsg("out of memory"),
+			errdetail("Failed while reallocation block %lu bytes in shared memory.", (unsigned long) size),
+			errhint("Increase SHMEMMSGSZ and recompile package.")));
+
+	return result;
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/shmmc.h
----------------------------------------------------------------------
diff --git a/contrib/orafce/shmmc.h b/contrib/orafce/shmmc.h
new file mode 100644
index 0000000..2bf26b9
--- /dev/null
+++ b/contrib/orafce/shmmc.h
@@ -0,0 +1,12 @@
+#ifndef __SHMMC__
+#define __SHMMC__
+
+void  ora_sinit(void *ptr, size_t size, bool create);
+void* ora_salloc(size_t size);
+void* ora_srealloc(void *ptr, size_t size);
+void  ora_sfree(void* ptr);
+char* ora_sstrcpy(char *str);
+char* ora_scstring(text *str);
+void* salloc(size_t size);
+void* srealloc(void *ptr,size_t size);
+#endif

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/sql/dbms_output.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/sql/dbms_output.sql b/contrib/orafce/sql/dbms_output.sql
new file mode 100644
index 0000000..c46088c
--- /dev/null
+++ b/contrib/orafce/sql/dbms_output.sql
@@ -0,0 +1,764 @@
+\set ECHO none
+SET client_min_messages = warning;
+SET DATESTYLE TO ISO;
+SET client_encoding = utf8;
+\pset null '<NULL>'
+\set ECHO all
+
+DROP FUNCTION dbms_output_test();
+DROP TABLE dbms_output_test;
+
+-- DBMS_OUTPUT.DISABLE [0]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.PUT_LINE [1]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff1	VARCHAR(20) := 'orafce';
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE');
+	PERFORM DBMS_OUTPUT.PUT_LINE (buff1);
+	PERFORM DBMS_OUTPUT.PUT ('ABC');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.PUT_LINE [2]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORA
+F
+CE');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.PUT [1]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff1	VARCHAR(20) := 'ora';
+	buff2	VARCHAR(20) := 'f';
+	buff3	VARCHAR(20) := 'ce';
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT ('ORA');
+	PERFORM DBMS_OUTPUT.PUT ('F');
+	PERFORM DBMS_OUTPUT.PUT ('CE');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('');
+	PERFORM DBMS_OUTPUT.PUT ('ABC');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.PUT [2]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT ('ORA
+F
+CE');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [1]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [2]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 3');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [3]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.PUT ('ORA');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [4]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [5]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1
+');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT REPLACE(buff, '
+', '<LF>') FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINE [6]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORA
+F
+CE');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT regexp_replace(buff, E'\n', '<LF>', 'g') FROM dbms_output_test limit 1;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [1]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	buff1	VARCHAR(20);
+	buff2	VARCHAR(20);
+	buff3	VARCHAR(20);
+	stts	INTEGER := 10;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 3');
+	SELECT INTO buff1,buff2,buff3,stts lines[1],lines[2],lines[3],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff1, stts);
+	INSERT INTO dbms_output_test VALUES (buff2, stts);
+	INSERT INTO dbms_output_test VALUES (buff3, stts);
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [2]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	buff1	VARCHAR(20);
+	buff2	VARCHAR(20);
+	stts	INTEGER := 2;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 3');
+	SELECT INTO buff1,buff2,stts lines[1],lines[2],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff1, stts);
+	INSERT INTO dbms_output_test VALUES (buff2, stts);
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [3]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 1;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 3');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [4]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 1;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.PUT ('ORA');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [5]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 1;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.GET_LINES [6]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 1;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORA
+F
+CE');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT regexp_replace(buff, E'\n', '<LF>', 'g') FROM dbms_output_test limit 1;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.NEW_LINE [1]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff1	VARCHAR(20);
+	buff2	VARCHAR(20);
+	stts	INTEGER := 10;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT ('ORA');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	PERFORM DBMS_OUTPUT.PUT ('FCE');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff1,buff2,stts lines[1],lines[2],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff1, stts);
+	INSERT INTO dbms_output_test VALUES (buff2, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.NEW_LINE [2]
+CREATE TABLE dbms_output_test (buff VARCHAR(3000), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff1	VARCHAR(3000);
+	stts	INTEGER := 10;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.ENABLE(2000);
+	FOR j IN 1..1999 LOOP
+		PERFORM DBMS_OUTPUT.PUT ('A');
+	END LOOP;
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff1,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff1, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT buff FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.DISABLE [1]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+	PERFORM DBMS_OUTPUT.ENABLE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 3');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.ENABLE();
+
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 4');
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 5');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.DISABLE [2]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 10;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [1]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	status	INTEGER;
+	num		INTEGER := 2000;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.ENABLE(2000);
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [2]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 2');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [3]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER := 10;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	SELECT INTO buff,stts lines[1],numlines FROM DBMS_OUTPUT.GET_LINES(stts);
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [4]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	FOR j IN 1..2000 LOOP
+		PERFORM DBMS_OUTPUT.PUT ('A');
+	END LOOP;
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [5]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE(NULL);
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- DBMS_OUTPUT.ENABLE [6]
+CREATE TABLE dbms_output_test (buff VARCHAR(20), status INTEGER);
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+DECLARE
+	buff	VARCHAR(20);
+	stts	INTEGER;
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.ENABLE();
+	SELECT INTO buff,stts line,status FROM DBMS_OUTPUT.GET_LINE();
+	INSERT INTO dbms_output_test VALUES (buff, stts);
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+SELECT * FROM dbms_output_test;
+DROP TABLE dbms_output_test;
+DROP FUNCTION dbms_output_test();
+
+-- SERVEROUTPUT [1]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIn
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 2');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- SERVEROUTPUT [2]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 1');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT ('ORAFCE TEST 2');
+	PERFORM DBMS_OUTPUT.NEW_LINE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+-- SERVEROUTPUT [3]
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('f');
+	PERFORM DBMS_OUTPUT.DISABLE();
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();
+
+CREATE FUNCTION dbms_output_test() RETURNS VOID AS $$
+BEGIN
+	PERFORM DBMS_OUTPUT.DISABLE();
+	PERFORM DBMS_OUTPUT.ENABLE();
+	PERFORM DBMS_OUTPUT.SERVEROUTPUT ('t');
+	PERFORM DBMS_OUTPUT.PUT_LINE ('ORAFCE TEST 1');
+END;
+$$ LANGUAGE plpgsql;
+SELECT dbms_output_test();
+DROP FUNCTION dbms_output_test();

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/sql/dbms_pipe.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/sql/dbms_pipe.sql b/contrib/orafce/sql/dbms_pipe.sql
new file mode 100644
index 0000000..853b9d1
--- /dev/null
+++ b/contrib/orafce/sql/dbms_pipe.sql
@@ -0,0 +1,58 @@
+CREATE TYPE testt AS (x integer, y integer, v varchar);
+
+CREATE OR REPLACE FUNCTION st(integer, integer, varchar) 
+RETURNS void AS $$
+DECLARE t testt; r record;
+BEGIN t.x := $1; t.y := $2; t.v := $3;
+  select into r 10,10,'boo';
+  PERFORM dbms_pipe.pack_message(t);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION sk() 
+RETURNS void AS $$
+DECLARE t testt;
+ o testt;
+BEGIN t.x := 1; t.y := 2; t.v := 'Pavel Stehule';
+  RAISE NOTICE 'SEND';
+  PERFORM dbms_pipe.pack_message(t);
+  PERFORM dbms_pipe.send_message('boo',4,10);
+  RAISE NOTICE 'RECEIVE';
+--  PERFORM dbms_pipe.receive_message('boo',4);
+--  SELECT INTO o * from dbms_pipe.unpack_message_record() as (x integer, y integer, v varchar);
+--  RAISE NOTICE 'received %', o.v;  
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION SessionA() RETURNS void AS $$
+BEGIN
+  FOR i IN 1..100000 LOOP
+    PERFORM dbms_pipe.pack_message('Prvni '||i);
+    PERFORM dbms_pipe.pack_message('Druhy '||i);
+    RAISE NOTICE 'SEND';
+    IF dbms_pipe.send_message('pipe_name',4,10) = 1 THEN
+      RAISE NOTICE 'Timeout';
+      PERFORM pg_sleep(5);
+      PERFORM dbms_pipe.send_message('pipe_name',4,10);
+    END IF;
+    PERFORM pg_sleep(random());
+  END LOOP;
+END; $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION SessionB() RETURNS void AS $$
+BEGIN
+  FOR i IN 1..100000 LOOP
+    IF dbms_pipe.receive_message('pipe_name',4) = 1 THEN
+      RAISE NOTICE 'Timeout';
+      PERFORM pg_sleep(5);
+      CONTINUE;
+    END IF;
+    RAISE NOTICE 'RECEIVE % %', dbms_pipe.unpack_message_text(), 
+      dbms_pipe.unpack_message_text();
+    PERFORM pg_sleep(random());
+  END LOOP;
+END; $$ LANGUAGE plpgsql;
+
+