You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by fo...@apache.org on 2015/11/12 21:25:25 UTC
[8/9] incubator-hawq git commit: HAWQ-149. Add orafce, gp_cancel_query,
pgbench and extprotocol to HAWQ
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/assert.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/assert.c b/contrib/orafce/assert.c
new file mode 100644
index 0000000..6b6a3c5
--- /dev/null
+++ b/contrib/orafce/assert.c
@@ -0,0 +1,386 @@
+#include "postgres.h"
+#include "funcapi.h"
+#include "assert.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/syscache.h"
+#include "catalog/namespace.h"
+#include "ctype.h"
+#include "string.h"
+#include "orafunc.h"
+#include "builtins.h"
+
+PG_FUNCTION_INFO_V1(dbms_assert_enquote_literal);
+PG_FUNCTION_INFO_V1(dbms_assert_enquote_name);
+PG_FUNCTION_INFO_V1(dbms_assert_noop);
+PG_FUNCTION_INFO_V1(dbms_assert_qualified_sql_name);
+PG_FUNCTION_INFO_V1(dbms_assert_schema_name);
+PG_FUNCTION_INFO_V1(dbms_assert_simple_sql_name);
+PG_FUNCTION_INFO_V1(dbms_assert_object_name);
+
+
+#define CUSTOM_EXCEPTION(code, msg) \
+ ereport(ERROR, \
+ (errcode(ERRCODE_ORA_PACKAGES_##code), \
+ errmsg(msg)))
+
+#define INVALID_SCHEMA_NAME_EXCEPTION() \
+ CUSTOM_EXCEPTION(INVALID_SCHEMA_NAME, "invalid schema name")
+
+#define INVALID_OBJECT_NAME_EXCEPTION() \
+ CUSTOM_EXCEPTION(INVALID_OBJECT_NAME, "invalid object name")
+
+#define ISNOT_SIMPLE_SQL_NAME_EXCEPTION() \
+ CUSTOM_EXCEPTION(ISNOT_SIMPLE_SQL_NAME, "string is not simple SQL name")
+
+#define ISNOT_QUALIFIED_SQL_NAME_EXCEPTION() \
+ CUSTOM_EXCEPTION(ISNOT_QUALIFIED_SQL_NAME, "string is not qualified SQL name")
+
+#define EMPTY_STR(str) ((VARSIZE(str) - VARHDRSZ) == 0)
+
+
+static bool check_sql_name(char *cp, int len);
+static bool ParseIdentifierString(char *rawstring);
+
+/*
+ * Procedure ParseIdentifierString is based on SplitIdentifierString
+ * from varlena.c. We need different behave of quote symbol evaluation.
+ */
+bool
+ParseIdentifierString(char *rawstring)
+{
+ char *nextp = rawstring;
+ bool done = false;
+
+ while (isspace((unsigned char) *nextp))
+ nextp++; /* skip leading whitespace */
+
+ if (*nextp == '\0')
+ return true; /* allow empty string */
+
+ /* At the top of the loop, we are at start of a new identifier. */
+ do
+ {
+ char *curname;
+ char *endp;
+
+ if (*nextp == '\"')
+ {
+ /* Quoted name --- collapse quote-quote pairs, no downcasing */
+ curname = nextp + 1;
+ for (;;)
+ {
+ endp = strchr(nextp + 1, '\"');
+ if (endp == NULL)
+ return false; /* mismatched quotes */
+ if (endp[1] != '\"')
+ break; /* found end of quoted name */
+ /* Collapse adjacent quotes into one quote, and look again */
+ memmove(endp, endp + 1, strlen(endp));
+ nextp = endp;
+ }
+ /* endp now points at the terminating quote */
+ nextp = endp + 1;
+ }
+ else
+ {
+ /* Unquoted name --- extends to separator or whitespace */
+ curname = nextp;
+ while (*nextp && *nextp != '.' &&
+ !isspace((unsigned char) *nextp))
+ {
+ if (!isalnum(*nextp) && *nextp != '_')
+ return false;
+ nextp++;
+ }
+ endp = nextp;
+ if (curname == nextp)
+ return false; /* empty unquoted name not allowed */
+ }
+
+ while (isspace((unsigned char) *nextp))
+ nextp++; /* skip trailing whitespace */
+
+ if (*nextp == '.')
+ {
+ nextp++;
+ while (isspace((unsigned char) *nextp))
+ nextp++; /* skip leading whitespace for next */
+ /* we expect another name, so done remains false */
+ }
+ else if (*nextp == '\0')
+ done = true;
+ else
+ return false; /* invalid syntax */
+
+ /* Loop back if we didn't reach end of string */
+ } while (!done);
+
+ return true;
+}
+
+
+
+/****************************************************************
+ * DBMS_ASSERT.ENQUOTE_LITERAL
+ *
+ * Syntax:
+ * FUNCTION ENQUOTE_LITERAL(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * Add leading and trailing quotes, verify that all single quotes
+ * are paired with adjacent single quotes.
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_enquote_literal(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATUM(DirectFunctionCall1(quote_literal, PG_GETARG_DATUM(0)));
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.ENQUOTE_NAME
+ *
+ * Syntax:
+ * FUNCTION ENQUOTE_NAME(str varchar) RETURNS varchar;
+ * FUNCTION ENQUOTE_NAME(str varchar, loweralize boolean := true)
+ * RETURNS varchar;
+ * Purpouse:
+ * Enclose name in double quotes.
+ * Atention!:
+ * On Oracle is second parameter capitalize!
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_enquote_name(PG_FUNCTION_ARGS)
+{
+ Datum name = PG_GETARG_DATUM(0);
+ bool loweralize = PG_GETARG_BOOL(1);
+
+ name = DirectFunctionCall1(quote_ident, name);
+
+ if (loweralize)
+ name = DirectFunctionCall1(lower, name);
+
+ PG_RETURN_DATUM(name);
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.NOOP
+ *
+ * Syntax:
+ * FUNCTION NOOP(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * Returns value without any checking.
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_noop(PG_FUNCTION_ARGS)
+{
+ text *str = PG_GETARG_TEXT_P(0);
+
+ PG_RETURN_TEXT_P(TextPCopy(str));
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.QUALIFIED_SQL_NAME
+ *
+ * Syntax:
+ * FUNCTION QUALIFIED_SQL_NAME(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * This function verifies that the input string is qualified SQL
+ * name.
+ * Exception: 44004 string is not a qualified SQL name
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_qualified_sql_name(PG_FUNCTION_ARGS)
+{
+ text *qname;
+
+ if (PG_ARGISNULL(0))
+ ISNOT_QUALIFIED_SQL_NAME_EXCEPTION();
+
+ qname = PG_GETARG_TEXT_P(0);
+ if (EMPTY_STR(qname))
+ ISNOT_QUALIFIED_SQL_NAME_EXCEPTION();
+
+ if (!ParseIdentifierString(text_to_cstring(qname)))
+ ISNOT_QUALIFIED_SQL_NAME_EXCEPTION();
+
+ PG_RETURN_TEXT_P(qname);
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.SCHEMA_NAME
+ *
+ * Syntax:
+ * FUNCTION SCHEMA_NAME(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * Function verifies that input string is an existing schema
+ * name.
+ * Exception: 44001 Invalid schema name
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_schema_name(PG_FUNCTION_ARGS)
+{
+ Oid namespaceId;
+ AclResult aclresult;
+ text *sname;
+ char *nspname;
+ List *names;
+
+ if (PG_ARGISNULL(0))
+ INVALID_SCHEMA_NAME_EXCEPTION();
+
+ sname = PG_GETARG_TEXT_P(0);
+ if (EMPTY_STR(sname))
+ INVALID_SCHEMA_NAME_EXCEPTION();
+
+ nspname = text_to_cstring(sname);
+#ifdef GP_VERSION_NUM
+ names = stringToQualifiedNameList(nspname, "dbms");
+#else
+ names = stringToQualifiedNameList(nspname);
+#endif
+ if (list_length(names) != 1)
+ INVALID_SCHEMA_NAME_EXCEPTION();
+
+ namespaceId = GetSysCacheOid(NAMESPACENAME,
+ CStringGetDatum(strVal(linitial(names))),
+ 0, 0, 0);
+ if (!OidIsValid(namespaceId))
+ INVALID_SCHEMA_NAME_EXCEPTION();
+
+ aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ INVALID_SCHEMA_NAME_EXCEPTION();
+
+ PG_RETURN_TEXT_P(sname);
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.SIMPLE_SQL_NAME
+ *
+ * Syntax:
+ * FUNCTION SIMPLE_SQL_NAME(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * This function verifies that the input string is simple SQL
+ * name.
+ * Exception: 44003 String is not a simple SQL name
+ *
+ ****************************************************************/
+
+static bool
+check_sql_name(char *cp, int len)
+{
+ if (*cp == '"')
+ {
+ for (cp++, len -= 2; len-- > 0; cp++)
+ {
+ /* all double quotes have to be paired */
+ if (*cp == '"')
+ {
+ if (len-- == 0)
+ return false;
+ /* next char has to be quote */
+ if (*cp != '"')
+ return false;
+ }
+
+ }
+ if (*cp != '"')
+ return false;
+ }
+ else
+ {
+ /* Doesn't allow national characters in sql name :( */
+ for (; len-- > 0; cp++)
+ if (!isalnum(*cp) && *cp != '_')
+ return false;
+ }
+
+ return true;
+}
+
+Datum
+dbms_assert_simple_sql_name(PG_FUNCTION_ARGS)
+{
+ text *sname;
+ int len;
+ char *cp;
+
+ if (PG_ARGISNULL(0))
+ ISNOT_SIMPLE_SQL_NAME_EXCEPTION();
+
+ sname = PG_GETARG_TEXT_P(0);
+ if (EMPTY_STR(sname))
+ ISNOT_SIMPLE_SQL_NAME_EXCEPTION();
+
+ len = VARSIZE(sname) - VARHDRSZ;
+ cp = VARDATA(sname);
+
+ if (!check_sql_name(cp, len))
+ ISNOT_SIMPLE_SQL_NAME_EXCEPTION();
+
+ PG_RETURN_TEXT_P(sname);
+}
+
+
+/****************************************************************
+ * DBMS_ASSERT.OBJECT_NAME
+ *
+ * Syntax:
+ * FUNCTION OBJECT_NAME(str varchar) RETURNS varchar;
+ *
+ * Purpouse:
+ * Verifies that input string is qualified SQL identifier of
+ * an existing SQL object.
+ * Exception: 44002 Invalid object name
+ *
+ ****************************************************************/
+
+Datum
+dbms_assert_object_name(PG_FUNCTION_ARGS)
+{
+ List *names;
+ text *str;
+ char *object_name;
+ Oid classId;
+
+ if (PG_ARGISNULL(0))
+ INVALID_OBJECT_NAME_EXCEPTION();
+
+ str = PG_GETARG_TEXT_P(0);
+ if (EMPTY_STR(str))
+ INVALID_OBJECT_NAME_EXCEPTION();
+
+ object_name = text_to_cstring(str);
+#ifdef GP_VERSION_NUM
+ names = stringToQualifiedNameList(object_name, "dbms");
+#else
+ names = stringToQualifiedNameList(object_name);
+#endif
+
+ classId = RangeVarGetRelid(makeRangeVarFromNameList(names), true, true /*allowHcatalog*/);
+ if (!OidIsValid(classId))
+ INVALID_OBJECT_NAME_EXCEPTION();
+
+ PG_RETURN_TEXT_P(str);
+}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/assert.h
----------------------------------------------------------------------
diff --git a/contrib/orafce/assert.h b/contrib/orafce/assert.h
new file mode 100644
index 0000000..45c2fc7
--- /dev/null
+++ b/contrib/orafce/assert.h
@@ -0,0 +1,9 @@
+#ifndef __ASSERT__
+#define __ASSERT__
+
+#define ERRCODE_ORA_PACKAGES_INVALID_SCHEMA_NAME MAKE_SQLSTATE('4','4','0','0','1')
+#define ERRCODE_ORA_PACKAGES_INVALID_OBJECT_NAME MAKE_SQLSTATE('4','4','0','0','2')
+#define ERRCODE_ORA_PACKAGES_ISNOT_SIMPLE_SQL_NAME MAKE_SQLSTATE('4','4','0','0','3')
+#define ERRCODE_ORA_PACKAGES_ISNOT_QUALIFIED_SQL_NAME MAKE_SQLSTATE('4','4','0','0','4')
+
+#endif
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/builtins.h
----------------------------------------------------------------------
diff --git a/contrib/orafce/builtins.h b/contrib/orafce/builtins.h
new file mode 100644
index 0000000..a35bac8
--- /dev/null
+++ b/contrib/orafce/builtins.h
@@ -0,0 +1,193 @@
+
+#ifndef ORAFCE_BUILTINS
+#define ORAFCE_BUILTINS
+
+/* from aggregate.c */
+extern Datum orafce_listagg1_transfn(PG_FUNCTION_ARGS);
+extern Datum orafce_listagg2_transfn(PG_FUNCTION_ARGS);
+extern Datum orafce_listagg_finalfn(PG_FUNCTION_ARGS);
+extern Datum orafce_median4_transfn(PG_FUNCTION_ARGS);
+extern Datum orafce_median4_finalfn(PG_FUNCTION_ARGS);
+extern Datum orafce_median8_transfn(PG_FUNCTION_ARGS);
+extern Datum orafce_median8_finalfn(PG_FUNCTION_ARGS);
+
+/* from alert.c */
+extern Datum dbms_alert_register(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_remove(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_removeall(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_set_defaults(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_signal(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_waitany(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_waitone(PG_FUNCTION_ARGS);
+extern Datum dbms_alert_defered_signal(PG_FUNCTION_ARGS);
+
+/* from assert.c */
+extern Datum dbms_assert_enquote_literal(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_enquote_name(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_noop(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_qualified_sql_name(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_schema_name(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_simple_sql_name(PG_FUNCTION_ARGS);
+extern Datum dbms_assert_object_name(PG_FUNCTION_ARGS);
+
+/* from convert.c */
+extern Datum orafce_to_char_int4(PG_FUNCTION_ARGS);
+extern Datum orafce_to_char_int8(PG_FUNCTION_ARGS);
+extern Datum orafce_to_char_float4(PG_FUNCTION_ARGS);
+extern Datum orafce_to_char_float8(PG_FUNCTION_ARGS);
+extern Datum orafce_to_char_numeric(PG_FUNCTION_ARGS);
+extern Datum orafce_to_number(PG_FUNCTION_ARGS);
+extern Datum orafce_to_multi_byte(PG_FUNCTION_ARGS);
+
+/* from datefce.c */
+extern Datum next_day(PG_FUNCTION_ARGS);
+extern Datum next_day_by_index(PG_FUNCTION_ARGS);
+extern Datum last_day(PG_FUNCTION_ARGS);
+extern Datum months_between(PG_FUNCTION_ARGS);
+extern Datum add_months(PG_FUNCTION_ARGS);
+extern Datum ora_date_trunc(PG_FUNCTION_ARGS);
+extern Datum ora_date_round(PG_FUNCTION_ARGS);
+extern Datum ora_timestamptz_trunc(PG_FUNCTION_ARGS);
+extern Datum ora_timestamptz_round(PG_FUNCTION_ARGS);
+
+/* from file.c */
+extern Datum utl_file_fopen(PG_FUNCTION_ARGS);
+extern Datum utl_file_is_open(PG_FUNCTION_ARGS);
+extern Datum utl_file_get_line(PG_FUNCTION_ARGS);
+extern Datum utl_file_get_nextline(PG_FUNCTION_ARGS);
+extern Datum utl_file_put(PG_FUNCTION_ARGS);
+extern Datum utl_file_put_line(PG_FUNCTION_ARGS);
+extern Datum utl_file_new_line(PG_FUNCTION_ARGS);
+extern Datum utl_file_putf(PG_FUNCTION_ARGS);
+extern Datum utl_file_fflush(PG_FUNCTION_ARGS);
+extern Datum utl_file_fclose(PG_FUNCTION_ARGS);
+extern Datum utl_file_fclose_all(PG_FUNCTION_ARGS);
+extern Datum utl_file_fremove(PG_FUNCTION_ARGS);
+extern Datum utl_file_frename(PG_FUNCTION_ARGS);
+extern Datum utl_file_fcopy(PG_FUNCTION_ARGS);
+extern Datum utl_file_fgetattr(PG_FUNCTION_ARGS);
+extern Datum utl_file_tmpdir(PG_FUNCTION_ARGS);
+
+/* from others.c */
+extern Datum ora_nvl(PG_FUNCTION_ARGS);
+extern Datum ora_nvl2(PG_FUNCTION_ARGS);
+extern Datum ora_concat(PG_FUNCTION_ARGS);
+extern Datum ora_nlssort(PG_FUNCTION_ARGS);
+extern Datum ora_set_nls_sort(PG_FUNCTION_ARGS);
+extern Datum ora_lnnvl(PG_FUNCTION_ARGS);
+extern Datum ora_decode(PG_FUNCTION_ARGS);
+extern Datum orafce_dump(PG_FUNCTION_ARGS);
+
+/* from pipe.c */
+extern Datum dbms_pipe_pack_message_text(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_text(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_send_message(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_receive_message(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unique_session_name (PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_list_pipes (PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_next_item_type (PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_create_pipe(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_create_pipe_2(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_create_pipe_1(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_reset_buffer(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_purge(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_remove_pipe(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_date(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_date(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_timestamp(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_timestamp(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_number(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_number(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_bytea(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_bytea(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_record(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_unpack_message_record(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_integer(PG_FUNCTION_ARGS);
+extern Datum dbms_pipe_pack_message_bigint(PG_FUNCTION_ARGS);
+
+/* from plunit.c */
+extern Datum plunit_assert_true(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_true_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_false(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_false_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_null(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_null_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_null(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_null_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_equals(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_equals_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_equals_range(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_equals_range_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_equals(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_equals_message(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_equals_range(PG_FUNCTION_ARGS);
+extern Datum plunit_assert_not_equals_range_message(PG_FUNCTION_ARGS);
+extern Datum plunit_fail(PG_FUNCTION_ARGS);
+extern Datum plunit_fail_message(PG_FUNCTION_ARGS);
+
+/* from plvlec.c */
+extern Datum plvlex_tokens(PG_FUNCTION_ARGS);
+
+/* from plvstr.c */
+extern Datum plvstr_rvrs(PG_FUNCTION_ARGS);
+extern Datum plvstr_normalize(PG_FUNCTION_ARGS);
+extern Datum plvstr_is_prefix_text(PG_FUNCTION_ARGS);
+extern Datum plvstr_is_prefix_int(PG_FUNCTION_ARGS);
+extern Datum plvstr_is_prefix_int64(PG_FUNCTION_ARGS);
+extern Datum plvstr_lpart(PG_FUNCTION_ARGS);
+extern Datum plvstr_rpart(PG_FUNCTION_ARGS);
+extern Datum plvstr_lstrip(PG_FUNCTION_ARGS);
+extern Datum plvstr_rstrip(PG_FUNCTION_ARGS);
+extern Datum plvstr_left(PG_FUNCTION_ARGS);
+extern Datum plvstr_right(PG_FUNCTION_ARGS);
+extern Datum plvstr_substr2(PG_FUNCTION_ARGS);
+extern Datum plvstr_substr3(PG_FUNCTION_ARGS);
+extern Datum plvstr_instr2(PG_FUNCTION_ARGS);
+extern Datum plvstr_instr3(PG_FUNCTION_ARGS);
+extern Datum plvstr_instr4(PG_FUNCTION_ARGS);
+extern Datum plvstr_betwn_i(PG_FUNCTION_ARGS);
+extern Datum plvstr_betwn_c(PG_FUNCTION_ARGS);
+extern Datum plvstr_swap(PG_FUNCTION_ARGS);
+extern Datum plvchr_nth(PG_FUNCTION_ARGS);
+extern Datum plvchr_first(PG_FUNCTION_ARGS);
+extern Datum plvchr_last(PG_FUNCTION_ARGS);
+extern Datum plvchr_is_kind_i(PG_FUNCTION_ARGS);
+extern Datum plvchr_is_kind_a(PG_FUNCTION_ARGS);
+extern Datum plvchr_char_name(PG_FUNCTION_ARGS);
+extern Datum oracle_substr2(PG_FUNCTION_ARGS);
+extern Datum oracle_substr3(PG_FUNCTION_ARGS);
+
+/* from plvsubst.c */
+extern Datum plvsubst_string_array(PG_FUNCTION_ARGS);
+extern Datum plvsubst_string_string(PG_FUNCTION_ARGS);
+extern Datum plvsubst_setsubst(PG_FUNCTION_ARGS);
+extern Datum plvsubst_setsubst_default(PG_FUNCTION_ARGS);
+extern Datum plvsubst_subst(PG_FUNCTION_ARGS);
+
+/* from putline.c */
+extern Datum dbms_output_enable(PG_FUNCTION_ARGS);
+extern Datum dbms_output_enable_default(PG_FUNCTION_ARGS);
+extern Datum dbms_output_disable(PG_FUNCTION_ARGS);
+extern Datum dbms_output_serveroutput(PG_FUNCTION_ARGS);
+extern Datum dbms_output_put(PG_FUNCTION_ARGS);
+extern Datum dbms_output_put_line(PG_FUNCTION_ARGS);
+extern Datum dbms_output_new_line(PG_FUNCTION_ARGS);
+extern Datum dbms_output_get_line(PG_FUNCTION_ARGS);
+extern Datum dbms_output_get_lines(PG_FUNCTION_ARGS);
+
+/* from random.c */
+extern Datum dbms_random_initialize(PG_FUNCTION_ARGS);
+extern Datum dbms_random_normal(PG_FUNCTION_ARGS);
+extern Datum dbms_random_random(PG_FUNCTION_ARGS);
+extern Datum dbms_random_seed_int(PG_FUNCTION_ARGS);
+extern Datum dbms_random_seed_varchar(PG_FUNCTION_ARGS);
+extern Datum dbms_random_string(PG_FUNCTION_ARGS);
+extern Datum dbms_random_terminate(PG_FUNCTION_ARGS);
+extern Datum dbms_random_value(PG_FUNCTION_ARGS);
+extern Datum dbms_random_value_range(PG_FUNCTION_ARGS);
+
+/* from utility.c */
+extern Datum dbms_utility_format_call_stack0(PG_FUNCTION_ARGS);
+extern Datum dbms_utility_format_call_stack1(PG_FUNCTION_ARGS);
+
+#endif
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/convert.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/convert.c b/contrib/orafce/convert.c
new file mode 100644
index 0000000..47ab026
--- /dev/null
+++ b/contrib/orafce/convert.c
@@ -0,0 +1,392 @@
+#include "postgres.h"
+#include "fmgr.h"
+#include "lib/stringinfo.h"
+#include "mb/pg_wchar.h"
+#include "utils/builtins.h"
+#include "utils/numeric.h"
+#include "utils/pg_locale.h"
+
+#include "orafunc.h"
+#include "builtins.h"
+
+PG_FUNCTION_INFO_V1(orafce_to_char_int4);
+PG_FUNCTION_INFO_V1(orafce_to_char_int8);
+PG_FUNCTION_INFO_V1(orafce_to_char_float4);
+PG_FUNCTION_INFO_V1(orafce_to_char_float8);
+PG_FUNCTION_INFO_V1(orafce_to_char_numeric);
+PG_FUNCTION_INFO_V1(orafce_to_number);
+PG_FUNCTION_INFO_V1(orafce_to_multi_byte);
+
+Datum
+orafce_to_char_int4(PG_FUNCTION_ARGS)
+{
+ int32 arg0 = PG_GETARG_INT32(0);
+ StringInfo buf = makeStringInfo();
+
+ appendStringInfo(buf, "%d", arg0);
+
+ PG_RETURN_TEXT_P(cstring_to_text(buf->data));
+}
+
+Datum
+orafce_to_char_int8(PG_FUNCTION_ARGS)
+{
+ int64 arg0 = PG_GETARG_INT64(0);
+ StringInfo buf = makeStringInfo();
+
+ appendStringInfo(buf, INT64_FORMAT, arg0);
+
+ PG_RETURN_TEXT_P(cstring_to_text(buf->data));
+}
+
+Datum
+orafce_to_char_float4(PG_FUNCTION_ARGS)
+{
+ float4 arg0 = PG_GETARG_FLOAT4(0);
+ StringInfo buf = makeStringInfo();
+ struct lconv *lconv = PGLC_localeconv();
+ char *p;
+
+ appendStringInfo(buf, "%f", arg0);
+
+ for (p = buf->data; *p; p++)
+ if (*p == '.')
+ *p = lconv->decimal_point[0];
+
+ PG_RETURN_TEXT_P(cstring_to_text(buf->data));
+}
+
+Datum
+orafce_to_char_float8(PG_FUNCTION_ARGS)
+{
+ float8 arg0 = PG_GETARG_FLOAT8(0);
+ StringInfo buf = makeStringInfo();
+ struct lconv *lconv = PGLC_localeconv();
+ char *p;
+
+ appendStringInfo(buf, "%f", arg0);
+
+ for (p = buf->data; *p; p++)
+ if (*p == '.')
+ *p = lconv->decimal_point[0];
+
+ PG_RETURN_TEXT_P(cstring_to_text(buf->data));
+}
+
+Datum
+orafce_to_char_numeric(PG_FUNCTION_ARGS)
+{
+ Numeric arg0 = PG_GETARG_NUMERIC(0);
+ StringInfo buf = makeStringInfo();
+ struct lconv *lconv = PGLC_localeconv();
+ char *p;
+
+ appendStringInfoString(buf, DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(arg0))));
+
+ for (p = buf->data; *p; p++)
+ if (*p == '.')
+ *p = lconv->decimal_point[0];
+
+ PG_RETURN_TEXT_P(cstring_to_text(buf->data));
+}
+
+Datum
+orafce_to_number(PG_FUNCTION_ARGS)
+{
+ text *arg0 = PG_GETARG_TEXT_PP(0);
+ char *buf;
+ struct lconv *lconv = PGLC_localeconv();
+ Numeric res;
+ char *p;
+
+ buf = text_to_cstring(arg0);
+
+ for (p = buf; *p; p++)
+ if (*p == lconv->decimal_point[0] && lconv->decimal_point[0])
+ *p = '.';
+ else if (*p == lconv->thousands_sep[0] && lconv->thousands_sep[0])
+ *p = ',';
+
+ res = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(buf), 0, -1));
+
+ PG_RETURN_NUMERIC(res);
+}
+
+/* 3 is enough, but it is defined as 4 in backend code. */
+#ifndef MAX_CONVERSION_GROWTH
+#define MAX_CONVERSION_GROWTH 4
+#endif
+
+/*
+ * Convert a tilde (~) to ...
+ * 1: a full width tilde. (same as JA16EUCTILDE in oracle)
+ * 0: a full width overline. (same as JA16EUC in oracle)
+ */
+#define JA_TO_FULL_WIDTH_TILDE 1
+
+static const char *
+TO_MULTI_BYTE_UTF8[95] =
+{
+ "\343\200\200",
+ "\357\274\201",
+ "\342\200\235",
+ "\357\274\203",
+ "\357\274\204",
+ "\357\274\205",
+ "\357\274\206",
+ "\342\200\231",
+ "\357\274\210",
+ "\357\274\211",
+ "\357\274\212",
+ "\357\274\213",
+ "\357\274\214",
+ "\357\274\215",
+ "\357\274\216",
+ "\357\274\217",
+ "\357\274\220",
+ "\357\274\221",
+ "\357\274\222",
+ "\357\274\223",
+ "\357\274\224",
+ "\357\274\225",
+ "\357\274\226",
+ "\357\274\227",
+ "\357\274\230",
+ "\357\274\231",
+ "\357\274\232",
+ "\357\274\233",
+ "\357\274\234",
+ "\357\274\235",
+ "\357\274\236",
+ "\357\274\237",
+ "\357\274\240",
+ "\357\274\241",
+ "\357\274\242",
+ "\357\274\243",
+ "\357\274\244",
+ "\357\274\245",
+ "\357\274\246",
+ "\357\274\247",
+ "\357\274\250",
+ "\357\274\251",
+ "\357\274\252",
+ "\357\274\253",
+ "\357\274\254",
+ "\357\274\255",
+ "\357\274\256",
+ "\357\274\257",
+ "\357\274\260",
+ "\357\274\261",
+ "\357\274\262",
+ "\357\274\263",
+ "\357\274\264",
+ "\357\274\265",
+ "\357\274\266",
+ "\357\274\267",
+ "\357\274\270",
+ "\357\274\271",
+ "\357\274\272",
+ "\357\274\273",
+ "\357\277\245",
+ "\357\274\275",
+ "\357\274\276",
+ "\357\274\277",
+ "\342\200\230",
+ "\357\275\201",
+ "\357\275\202",
+ "\357\275\203",
+ "\357\275\204",
+ "\357\275\205",
+ "\357\275\206",
+ "\357\275\207",
+ "\357\275\210",
+ "\357\275\211",
+ "\357\275\212",
+ "\357\275\213",
+ "\357\275\214",
+ "\357\275\215",
+ "\357\275\216",
+ "\357\275\217",
+ "\357\275\220",
+ "\357\275\221",
+ "\357\275\222",
+ "\357\275\223",
+ "\357\275\224",
+ "\357\275\225",
+ "\357\275\226",
+ "\357\275\227",
+ "\357\275\230",
+ "\357\275\231",
+ "\357\275\232",
+ "\357\275\233",
+ "\357\275\234",
+ "\357\275\235",
+#if JA_TO_FULL_WIDTH_TILDE
+ "\357\275\236"
+#else
+ "\357\277\243"
+#endif
+};
+
+static const char *
+TO_MULTI_BYTE_EUCJP[95] =
+{
+ "\241\241",
+ "\241\252",
+ "\241\311",
+ "\241\364",
+ "\241\360",
+ "\241\363",
+ "\241\365",
+ "\241\307",
+ "\241\312",
+ "\241\313",
+ "\241\366",
+ "\241\334",
+ "\241\244",
+ "\241\335",
+ "\241\245",
+ "\241\277",
+ "\243\260",
+ "\243\261",
+ "\243\262",
+ "\243\263",
+ "\243\264",
+ "\243\265",
+ "\243\266",
+ "\243\267",
+ "\243\270",
+ "\243\271",
+ "\241\247",
+ "\241\250",
+ "\241\343",
+ "\241\341",
+ "\241\344",
+ "\241\251",
+ "\241\367",
+ "\243\301",
+ "\243\302",
+ "\243\303",
+ "\243\304",
+ "\243\305",
+ "\243\306",
+ "\243\307",
+ "\243\310",
+ "\243\311",
+ "\243\312",
+ "\243\313",
+ "\243\314",
+ "\243\315",
+ "\243\316",
+ "\243\317",
+ "\243\320",
+ "\243\321",
+ "\243\322",
+ "\243\323",
+ "\243\324",
+ "\243\325",
+ "\243\326",
+ "\243\327",
+ "\243\330",
+ "\243\331",
+ "\243\332",
+ "\241\316",
+ "\241\357",
+ "\241\317",
+ "\241\260",
+ "\241\262",
+ "\241\306",
+ "\243\341",
+ "\243\342",
+ "\243\343",
+ "\243\344",
+ "\243\345",
+ "\243\346",
+ "\243\347",
+ "\243\350",
+ "\243\351",
+ "\243\352",
+ "\243\353",
+ "\243\354",
+ "\243\355",
+ "\243\356",
+ "\243\357",
+ "\243\360",
+ "\243\361",
+ "\243\362",
+ "\243\363",
+ "\243\364",
+ "\243\365",
+ "\243\366",
+ "\243\367",
+ "\243\370",
+ "\243\371",
+ "\243\372",
+ "\241\320",
+ "\241\303",
+ "\241\321",
+#if JA_TO_FULL_WIDTH_TILDE
+ "\241\301"
+#else
+ "\241\261"
+#endif
+};
+
+Datum
+orafce_to_multi_byte(PG_FUNCTION_ARGS)
+{
+ text *src;
+ text *dst;
+ const char *s;
+ char *d;
+ int srclen;
+ int dstlen;
+ int i;
+ const char **map;
+
+ switch (GetDatabaseEncoding())
+ {
+ case PG_UTF8:
+ map = TO_MULTI_BYTE_UTF8;
+ break;
+ case PG_EUC_JP:
+#if PG_VERSION_NUM >= 80300
+ case PG_EUC_JIS_2004:
+#endif
+ map = TO_MULTI_BYTE_EUCJP;
+ break;
+ /*
+ * TODO: Add converter for encodings.
+ */
+ default: /* no need to convert */
+ PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+ }
+
+ src = PG_GETARG_TEXT_PP(0);
+ s = VARDATA_ANY(src);
+ srclen = VARSIZE_ANY_EXHDR(src);
+ dst = (text *) palloc(VARHDRSZ + srclen * MAX_CONVERSION_GROWTH);
+ d = VARDATA(dst);
+
+ for (i = 0; i < srclen; i++)
+ {
+ unsigned char u = (unsigned char) s[i];
+ if (0x20 <= u && u <= 0x7e)
+ {
+ const char *m = map[u - 0x20];
+ while (*m)
+ {
+ *d++ = *m++;
+ }
+ }
+ else
+ {
+ *d++ = s[i];
+ }
+ }
+
+ dstlen = d - VARDATA(dst);
+ SET_VARSIZE(dst, VARHDRSZ + dstlen);
+
+ PG_RETURN_TEXT_P(dst);
+}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/datefce.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/datefce.c b/contrib/orafce/datefce.c
new file mode 100644
index 0000000..0f1725f
--- /dev/null
+++ b/contrib/orafce/datefce.c
@@ -0,0 +1,875 @@
+#include "postgres.h"
+#include "mb/pg_wchar.h"
+#include "utils/date.h"
+#include "utils/builtins.h"
+#include "utils/nabstime.h"
+#include "utils/numeric.h"
+#include <sys/time.h>
+#include "orafunc.h"
+#include "builtins.h"
+
+#ifdef ENABLE_INTERNATIONALIZED_WEEKDAY
+
+typedef struct WeekDays
+{
+ int encoding;
+ const char *names[7];
+} WeekDays;
+
+static const WeekDays *mru_weekdays = NULL;
+/*
+ * { encoding, { "sun", "mon", "tue", "wed", "thu", "fri", "sat" } },
+ */
+static const WeekDays WEEKDAYS[] =
+{
+ /* Japanese, UTF8 */
+ { PG_UTF8, { "\346\227\245", "\346\234\210", "\347\201\253", "\346\260\264", "\346\234\250", "\351\207\221", "\345\234\237" } },
+ /* Japanese, EUC_JP */
+ { PG_EUC_JP, { "\306\374", "\267\356", "\262\320", "\277\345", "\314\332", "\266\342", "\305\332" } },
+#if PG_VERSION_NUM >= 80300
+ /* Japanese, EUC_JIS_2004 (same as EUC_JP) */
+ { PG_EUC_JIS_2004, { "\306\374", "\267\356", "\262\320", "\277\345", "\314\332", "\266\342", "\305\332" } },
+#endif
+};
+
+
+static int
+weekday_search(const WeekDays *weekdays, const char *str, int len)
+{
+ int i;
+
+ for (i = 0; i < 7; i++)
+ {
+ int n = strlen(weekdays->names[i]);
+ if (n > len)
+ continue; /* too short */
+ if (pg_strncasecmp(weekdays->names[i], str, n) == 0)
+ return i;
+ }
+ return -1; /* not found */
+}
+
+#endif /* ENABLE_INTERNATIONALIZED_WEEKDAY */
+
+/*
+ * A hash table to lookup days. This is faster than using strcmp(). Since we'll
+ * usually do this again and again in a query, we benefit from the speed.
+ */
+
+/* generated by generate_hash.c. This makes tdhfunc() a perfect hash. */
+typedef struct daybucket
+{
+ int8 dow; /* the day of the week, starting at zero for Sunday */
+ uint8 daylen; /* length of the field below, cached */
+ char *day; /* the day name */
+} daybucket;
+
+#define TDH_SIZE 15
+
+static const daybucket TDH[TDH_SIZE] = {
+ {1, 6, "monday"}, {-1, 0, ""},
+ {-1, 0, ""}, {-1, 0, ""},
+ {2, 7, "tuesday"}, {3, 9, "wednesday"},
+ {5, 6, "friday"}, {4, 8, "thursday"},
+ {-1, 0, ""}, {-1, 0, ""},
+ {-1, 0, ""}, {-1, 0, ""},
+ {0, 6, "sunday"}, {6, 8, "saturday"},
+ {-1, 0, ""}};
+
+/*
+ * The hash function itself. This is declared externally so that generate_hash.c
+ * can use it too.
+ */
+#include "tdhfunc.c"
+
+/*
+ * Throw an error for invalid day information. We usually receive our days as
+ * varlena strings, hence the need for the length argument.
+ */
+#define INVALID_DAY(_s, _l) \
+do \
+{ \
+ char *__day = palloc(_l + 1); \
+ strncpy(__day, _s, _l); \
+ __day[_l] = '\0'; \
+ ereport(ERROR,\
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT), \
+ errmsg("invalid day name \"%s\"", __day))); \
+} while(0)
+
+/* Given a string representing a day of the week (like `Sunday' or
+ * 'monday', return the numeric day of the weekend, counting from Sunday at
+ * zero.
+ *
+ * We support three letter representations (sun, Mon, ...) and the full length
+ * name. The seems strange but that's Redwood for you.
+ *
+ * This is English only.
+ */
+static int
+day2daynum(const char *s, int len)
+{
+ daybucket b;
+
+ if (len < 3)
+ {
+ INVALID_DAY(s, len);
+ }
+
+ int hash = tdhfunc(s);
+ if (hash < 0)
+ {
+ INVALID_DAY(s, len);
+ }
+
+ b = TDH[hash % TDH_SIZE];
+#ifdef DEBUG
+ elog(NOTICE, "size = %i, hash = %i, mod = %i, daynum = %i",
+ (int)TDH_SIZE, hash, (int)(hash % TDH_SIZE), daynum);
+#endif
+
+ /* three letter day names are permitted in Oracle */
+ if (b.dow < 0 ||
+ (len != 3 && len != b.daylen) ||
+ pg_strncasecmp(s, b.day, len) != 0)
+ {
+ INVALID_DAY(s, len);
+ }
+ return b.dow;
+}
+
+/*
+ * External (defined in PgSQL datetime.c (timestamp utils))
+ */
+
+extern PGDLLIMPORT char *days[];
+extern PGDLLIMPORT pg_tz *session_timezone;
+
+#define CASE_fmt_YYYY case 0: case 1: case 2: case 3: case 4: case 5: case 6:
+#define CASE_fmt_IYYY case 7: case 8: case 9: case 10:
+#define CASE_fmt_Q case 11:
+#define CASE_fmt_WW case 12:
+#define CASE_fmt_IW case 13:
+#define CASE_fmt_W case 14:
+#define CASE_fmt_DAY case 15: case 16: case 17:
+#define CASE_fmt_MON case 18: case 19: case 20: case 21:
+#define CASE_fmt_CC case 22: case 23:
+#define CASE_fmt_DDD case 24: case 25: case 26:
+#define CASE_fmt_HH case 27: case 28: case 29:
+#define CASE_fmt_MI case 30:
+
+char *date_fmt[] =
+{
+ "Y", "Yy", "Yyy", "Yyyy", "Year", "Syyyy", "syear",
+ "I", "Iy", "Iyy", "Iyyy",
+ "Q", "Ww", "Iw", "W",
+ "Day", "Dy", "D",
+ "Month", "Mon", "Mm", "Rm",
+ "Cc", "Scc",
+ "Ddd", "Dd", "J",
+ "Hh", "Hh12", "Hh24",
+ "Mi",
+ NULL
+};
+
+#define CHECK_SEQ_SEARCH(_l, _s) \
+do { \
+ if ((_l) < 0) { \
+ ereport(ERROR, \
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT), \
+ errmsg("invalid %s", (_s)))); \
+ } \
+} while (0)
+
+PG_FUNCTION_INFO_V1(next_day);
+PG_FUNCTION_INFO_V1(next_day_by_index);
+PG_FUNCTION_INFO_V1(last_day);
+PG_FUNCTION_INFO_V1(months_between);
+PG_FUNCTION_INFO_V1(add_months);
+PG_FUNCTION_INFO_V1(ora_date_trunc);
+PG_FUNCTION_INFO_V1(ora_date_round);
+PG_FUNCTION_INFO_V1(ora_timestamptz_trunc);
+PG_FUNCTION_INFO_V1(ora_timestamptz_round);
+
+/*
+ * Search const value in char array
+ *
+ */
+int ora_seq_search(const char *name, /*const*/ char **array, int max);
+
+int
+ora_seq_search(const char *name, /*const*/ char **array, int max)
+{
+ int i;
+
+ if (!*name)
+ return -1;
+
+ for (i = 0; array[i]; i++)
+ {
+ if (strlen(array[i]) == max &&
+ pg_strncasecmp(name, array[i], max) == 0)
+ return i;
+ }
+ return -1; /* not found */
+}
+
+/********************************************************************
+ *
+ * next_day
+ *
+ * Syntax:
+ *
+ * date next_day(date value, text weekday)
+ *
+ * Purpose:
+ *
+ * Returns the first weekday that is greater than a date value.
+ *
+ ********************************************************************/
+Datum
+next_day(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ text *day_txt = PG_GETARG_TEXT_PP(1);
+ const char *str = VARDATA_ANY(day_txt);
+ int len = VARSIZE_ANY_EXHDR(day_txt);
+ int off;
+ int d = -1;
+
+#ifdef ENABLE_INTERNATIONALIZED_WEEKDAY
+ /* Check mru_weekdays first for performance. */
+ if (mru_weekdays)
+ {
+ if ((d = weekday_search(mru_weekdays, str, len)) >= 0)
+ goto found;
+ else
+ mru_weekdays = NULL;
+ }
+
+ do
+ {
+ int i;
+ int encoding = GetDatabaseEncoding();
+
+ for (i = 0; i < lengthof(WEEKDAYS); i++)
+ {
+ if (encoding == WEEKDAYS[i].encoding)
+ {
+ if ((d = weekday_search(&WEEKDAYS[i], str, len)) >= 0)
+ {
+ mru_weekdays = &WEEKDAYS[i];
+ goto found;
+ }
+ }
+ }
+ } while(0);
+
+ CHECK_SEQ_SEARCH(-1, "DAY/Day/day");
+
+found:
+#endif
+
+ d = day2daynum(str, len);
+
+ off = d - j2day(day+POSTGRES_EPOCH_JDATE);
+
+ PG_RETURN_DATEADT((off <= 0) ? day+off+7 : day + off);
+}
+
+/* next_day(date, integer) is not documented in Oracle manual, but ... */
+Datum
+next_day_by_index(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ int idx = PG_GETARG_INT32(1);
+ int off;
+
+ /*
+ * off is 1..7 (Sun..Sat).
+ *
+ * TODO: It should be affected by NLS_TERRITORY. For example,
+ * 1..7 should be interpreted as Mon..Sun in GERMAN.
+ */
+ if (idx < 1 || idx > 7)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("invalid day offset \"%i\"", idx)));
+
+ /* j2day returns 0..6 as Sun..Sat */
+ off = (idx - 1) - j2day(day+POSTGRES_EPOCH_JDATE);
+
+ PG_RETURN_DATEADT((off <= 0) ? day+off+7 : day + off);
+}
+
+/********************************************************************
+ *
+ * last_day
+ *
+ * Syntax:
+ *
+ * date last_day(date value)
+ *
+ * Purpose:
+ *
+ * Returns last day of the month based on a date value
+ *
+ ********************************************************************/
+Datum
+last_day(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ DateADT result;
+ int y, m, d;
+ j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+ result = date2j(y, m+1, 1) - POSTGRES_EPOCH_JDATE;
+
+ PG_RETURN_DATEADT(result - 1);
+}
+
+static const int month_days[] = {
+ 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31
+};
+
+static int
+days_of_month(int y, int m)
+{
+ int days;
+
+ if (m < 0 || 12 < m)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range")));
+
+ days = month_days[m - 1];
+ if (m == 2 && (y % 400 == 0 || (y % 4 == 0 && y % 100 != 0)))
+ days += 1; /* February 29 in leap year */
+ return days;
+}
+
+/********************************************************************
+ *
+ * months_between
+ *
+ * Syntax:
+ *
+ * float8 months_between(date date1, date date2)
+ *
+ * Purpose:
+ *
+ * Returns the number of months between date1 and date2. If
+ * a fractional month is calculated, the months_between function
+ * calculates the fraction based on a 31-day month.
+ *
+ ********************************************************************/
+
+Datum
+months_between(PG_FUNCTION_ARGS)
+{
+ DateADT date1 = PG_GETARG_DATEADT(0);
+ DateADT date2 = PG_GETARG_DATEADT(1);
+
+ int y1, m1, d1;
+ int y2, m2, d2;
+
+ float8 result;
+
+ j2date(date1 + POSTGRES_EPOCH_JDATE, &y1, &m1, &d1);
+ j2date(date2 + POSTGRES_EPOCH_JDATE, &y2, &m2, &d2);
+
+ /* Ignore day components for last days, or based on a 31-day month. */
+ if (d1 == days_of_month(y1, m1) && d2 == days_of_month(y2, m2))
+ result = (y1 - y2) * 12 + (m1 - m2);
+ else
+ result = (y1 - y2) * 12 + (m1 - m2) + (d1 - d2) / 31.0;
+
+ PG_RETURN_DATUM(
+ DirectFunctionCall1(float8_numeric, Float8GetDatumFast(result)));
+}
+
+/********************************************************************
+ *
+ * add_months
+ *
+ * Syntax:
+ *
+ * date add_months(date day, int val)
+ *
+ * Purpose:
+ *
+ * Returns a date plus n months.
+ *
+ ********************************************************************/
+
+
+Datum
+add_months(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ int n = PG_GETARG_INT32(1);
+ int y, m, d;
+ int days;
+ DateADT result;
+ div_t v;
+ bool last_day;
+
+ j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+ last_day = (d == days_of_month(y, m));
+
+ v = div(y * 12 + m - 1 + n, 12);
+ y = v.quot;
+ if (y < 0)
+ y += 1; /* offset because of year 0 */
+ m = v.rem + 1;
+
+ days = days_of_month(y, m);
+ if (last_day || d > days)
+ d = days;
+
+ result = date2j(y, m, d) - POSTGRES_EPOCH_JDATE;
+
+ PG_RETURN_DATEADT (result);
+}
+
+/*
+ * ISO year
+ *
+ */
+
+#define DATE2J(y,m,d) (date2j((y),(m),(d)) - POSTGRES_EPOCH_JDATE)
+#define J2DAY(date) (j2day(date + POSTGRES_EPOCH_JDATE))
+
+
+static DateADT
+iso_year (int y, int m, int d)
+{
+ DateADT result, result2, day;
+ int off;
+
+ result = DATE2J(y,1,1);
+ day = DATE2J(y,m,d);
+ off = 4 - J2DAY(result);
+ result += off + ((off >= 0) ? - 3: + 4); /* to monday */
+
+ if (result > day)
+ {
+ result = DATE2J(y-1,1,1);
+ off = 4 - J2DAY(result);
+ result += off + ((off >= 0) ? - 3: + 4); /* to monday */
+ }
+
+ if (((day - result) / 7 + 1) > 52)
+ {
+ result2 = DATE2J(y+1,1,1);
+ off = 4 - J2DAY(result2);
+ result2 += off + ((off >= 0) ? - 3: + 4); /* to monday */
+
+ if (day >= result2)
+ return result2;
+ }
+
+ return result;
+}
+
+static DateADT
+_ora_date_trunc(DateADT day, int f)
+{
+ int y, m, d;
+ DateADT result;
+
+ j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+
+ switch (f)
+ {
+ CASE_fmt_CC
+ if (y > 0)
+ result = DATE2J((y/100)*100+1,1,1);
+ else
+ result = DATE2J(-((99 - (y - 1)) / 100) * 100 + 1,1,1);
+ break;
+ CASE_fmt_YYYY
+ result = DATE2J(y,1,1);
+ break;
+ CASE_fmt_IYYY
+ result = iso_year(y,m,d);
+ break;
+ CASE_fmt_MON
+ result = DATE2J(y,m,1);
+ break;
+ CASE_fmt_WW
+ result = day - (day - DATE2J(y,1,1)) % 7;
+ break;
+ CASE_fmt_IW
+ result = day - (day - iso_year(y,m,d)) % 7;
+ break;
+ CASE_fmt_W
+ result = day - (day - DATE2J(y,m,1)) % 7;
+ break;
+ CASE_fmt_DAY
+ result = day - J2DAY(day);
+ break;
+ CASE_fmt_Q
+ result = DATE2J(y,((m-1)/3)*3+1,1);
+ break;
+ default:
+ result = day;
+ }
+
+ return result;
+}
+
+static DateADT
+_ora_date_round(DateADT day, int f)
+{
+ int y, m, d, z;
+ DateADT result;
+
+ j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
+
+ switch (f)
+ {
+ CASE_fmt_CC
+ if (y > 0)
+ result = DATE2J((y/100)*100+(day < DATE2J((y/100)*100+50,1,1) ?1:101),1,1);
+ else
+ result = DATE2J((y/100)*100+(day < DATE2J((y/100)*100-50+1,1,1) ?-99:1),1,1);
+ break;
+ CASE_fmt_YYYY
+ result = DATE2J(y+(day<DATE2J(y,7,1)?0:1),1,1);
+ break;
+ CASE_fmt_IYYY
+ {
+ if (day < DATE2J(y,7,1))
+ {
+ result = iso_year(y, m, d);
+ }
+ else
+ {
+ DateADT iy1 = iso_year(y+1, 1, 8);
+ result = iy1;
+
+ if (((day - DATE2J(y,1,1)) / 7 + 1) >= 52)
+ {
+ bool overl = ((date2j(y+2,1,1)-date2j(y+1,1,1)) == 366);
+ bool isSaturday = (J2DAY(day) == 6);
+
+ DateADT iy2 = iso_year(y+2, 1, 8);
+ DateADT day1 = DATE2J(y+1,1,1);
+ /* exception saturdays */
+ if (iy1 >= (day1) && day >= day1 - 2 && isSaturday)
+ {
+ result = overl?iy2:iy1;
+ }
+ /* iso year stars in last year and day >= iso year */
+ else if (iy1 <= (day1) && day >= iy1 - 3)
+ {
+ DateADT cmp = iy1 - (iy1 < day1?0:1);
+ int d = J2DAY(day1);
+ /* some exceptions */
+ if ((day >= cmp - 2) && (!(d == 3 && overl)))
+ {
+ /* if year don't starts in thursday */
+ if ((d < 4 && J2DAY(day) != 5 && !isSaturday)
+ ||(d == 2 && isSaturday && overl))
+ {
+ result = iy2;
+ }
+ }
+ }
+ }
+ }
+ break;
+ }
+ CASE_fmt_MON
+ result = DATE2J(y,m+(day<DATE2J(y,m,16)?0:1),1);
+ break;
+ CASE_fmt_WW
+ z = (day - DATE2J(y,1,1)) % 7;
+ result = day - z + (z < 4?0:7);
+ break;
+ CASE_fmt_IW
+ {
+ z = (day - iso_year(y,m,d)) % 7;
+ result = day - z + (z < 4?0:7);
+ if (((day - DATE2J(y,1,1)) / 7 + 1) >= 52)
+ {
+ /* only for last iso week */
+ DateADT isoyear = iso_year(y+1, 1, 8);
+ if (isoyear > (DATE2J(y+1,1,1)-1))
+ if (day > isoyear - 7)
+ {
+ int d = J2DAY(day);
+ result -= (d == 0 || d > 4?7:0);
+ }
+ }
+ break;
+ }
+ CASE_fmt_W
+ z = (day - DATE2J(y,m,1)) % 7;
+ result = day - z + (z < 4?0:7);
+ break;
+ CASE_fmt_DAY
+ z = J2DAY(day);
+ if (y > 0)
+ result = day - z + (z < 4?0:7);
+ else
+ result = day + (5 - (z>0?(z>1?z:z+7):7));
+ break;
+ CASE_fmt_Q
+ result = DATE2J(y,((m-1)/3)*3+(day<(DATE2J(y,((m-1)/3)*3+2,16))?1:4),1);
+ break;
+ default:
+ result = day;
+ }
+ return result;
+}
+
+
+/********************************************************************
+ *
+ * ora_date_trunc|ora_timestamptz_trunc .. trunc
+ *
+ * Syntax:
+ *
+ * date trunc(date date1, text format)
+ *
+ * Purpose:
+ *
+ * Returns d with the time portion of the day truncated to the unit
+ * specified by the format fmt.
+ *
+ ********************************************************************/
+
+Datum
+ora_date_trunc(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ text *fmt = PG_GETARG_TEXT_PP(1);
+
+ DateADT result;
+
+ int f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
+ CHECK_SEQ_SEARCH(f, "trunc format string");
+
+ result = _ora_date_trunc(day, f);
+ PG_RETURN_DATEADT(result);
+}
+
+Datum
+ora_timestamptz_trunc(PG_FUNCTION_ARGS)
+{
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+ TimestampTz result;
+ text *fmt = PG_GETARG_TEXT_PP(1);
+ int tz;
+ fsec_t fsec;
+ struct pg_tm tt, *tm = &tt;
+ char *tzn;
+ bool redotz = false;
+ int f;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMPTZ(timestamp);
+
+ f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
+ CHECK_SEQ_SEARCH(f, "trunc format string");
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ tm->tm_sec = 0;
+ fsec = 0;
+
+ switch (f)
+ {
+ CASE_fmt_IYYY
+ CASE_fmt_WW
+ CASE_fmt_W
+ CASE_fmt_IW
+ CASE_fmt_DAY
+ CASE_fmt_CC
+ j2date(_ora_date_trunc(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday), f)
+ + POSTGRES_EPOCH_JDATE,
+ &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ redotz = true;
+ break;
+ CASE_fmt_YYYY
+ tm->tm_mon = 1;
+ CASE_fmt_Q
+ tm->tm_mon = (3*((tm->tm_mon - 1)/3)) + 1;
+ CASE_fmt_MON
+ tm->tm_mday = 1;
+ CASE_fmt_DDD
+ tm->tm_hour = 0;
+ redotz = true; /* for all cases >= DAY */
+ CASE_fmt_HH
+ tm->tm_min = 0;
+ }
+
+ if (redotz)
+ tz = DetermineTimeZoneOffset(tm, session_timezone);
+
+ if (tm2timestamp(tm, fsec, &tz, &result) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+/********************************************************************
+ *
+ * ora_date_round|ora_timestamptz_round .. round
+ *
+ * Syntax:
+ *
+ * date round(date date1, text format)
+ *
+ * Purpose:
+ *
+ * Returns d with the time portion of the day roundeded to the unit
+ * specified by the format fmt.
+ *
+ ********************************************************************/
+
+
+Datum
+ora_date_round(PG_FUNCTION_ARGS)
+{
+ DateADT day = PG_GETARG_DATEADT(0);
+ text *fmt = PG_GETARG_TEXT_PP(1);
+
+ DateADT result;
+
+ int f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
+ CHECK_SEQ_SEARCH(f, "round format string");
+
+ result = _ora_date_round(day, f);
+ PG_RETURN_DATEADT(result);
+}
+
+#define NOT_ROUND_MDAY(_p_) \
+ do { if (_p_) rounded = false; } while(0)
+#define ROUND_MDAY(_tm_) \
+ do { if (rounded) _tm_->tm_mday += _tm_->tm_hour >= 12?1:0; } while(0)
+
+
+Datum
+ora_timestamptz_round(PG_FUNCTION_ARGS)
+{
+ TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
+ TimestampTz result;
+ text *fmt = PG_GETARG_TEXT_PP(1);
+ int tz;
+ fsec_t fsec;
+ struct pg_tm tt, *tm = &tt;
+ char *tzn;
+ bool redotz = false;
+ bool rounded = true;
+ int f;
+
+ if (TIMESTAMP_NOT_FINITE(timestamp))
+ PG_RETURN_TIMESTAMPTZ(timestamp);
+
+ f = ora_seq_search(VARDATA_ANY(fmt), date_fmt, VARSIZE_ANY_EXHDR(fmt));
+ CHECK_SEQ_SEARCH(f, "round format string");
+
+ if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ /* tm->tm_sec = 0; */
+ fsec = 0;
+
+ /* set rounding rule */
+ switch (f)
+ {
+ CASE_fmt_IYYY
+ NOT_ROUND_MDAY(tm->tm_mday < 8 && tm->tm_mon == 1);
+ NOT_ROUND_MDAY(tm->tm_mday == 30 && tm->tm_mon == 6);
+ if (tm->tm_mday >= 28 && tm->tm_mon == 12 && tm->tm_hour >= 12)
+ {
+ DateADT isoyear = iso_year(tm->tm_year+1, 1, 8);
+ DateADT day0 = DATE2J(tm->tm_year+1,1,1);
+ DateADT dayc = DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday);
+
+ if ((isoyear <= day0) || (day0 <= dayc + 2))
+ {
+ rounded = false;
+ }
+ }
+ break;
+ CASE_fmt_YYYY
+ NOT_ROUND_MDAY(tm->tm_mday == 30 && tm->tm_mon == 6);
+ break;
+ CASE_fmt_MON
+ NOT_ROUND_MDAY(tm->tm_mday == 15);
+ break;
+ CASE_fmt_Q
+ NOT_ROUND_MDAY(tm->tm_mday == 15 && tm->tm_mon == ((tm->tm_mon-1)/3)*3+2);
+ break;
+ CASE_fmt_WW
+ CASE_fmt_IW
+ /* last day in year */
+ NOT_ROUND_MDAY(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday) ==
+ (DATE2J(tm->tm_year+1, 1,1) - 1));
+ break;
+ CASE_fmt_W
+ /* last day in month */
+ NOT_ROUND_MDAY(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday) ==
+ (DATE2J(tm->tm_year, tm->tm_mon+1,1) - 1));
+ break;
+ }
+
+ switch (f)
+ {
+ /* easier convert to date */
+ CASE_fmt_IW
+ CASE_fmt_DAY
+ CASE_fmt_IYYY
+ CASE_fmt_WW
+ CASE_fmt_W
+ CASE_fmt_CC
+ CASE_fmt_MON
+ CASE_fmt_YYYY
+ CASE_fmt_Q
+ ROUND_MDAY(tm);
+ j2date(_ora_date_round(DATE2J(tm->tm_year, tm->tm_mon, tm->tm_mday), f)
+ + POSTGRES_EPOCH_JDATE,
+ &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ redotz = true;
+ break;
+ CASE_fmt_DDD
+ tm->tm_mday += (tm->tm_hour >= 12)?1:0;
+ tm->tm_hour = 0;
+ tm->tm_min = 0;
+ redotz = true;
+ break;
+ CASE_fmt_MI
+ tm->tm_min += (tm->tm_sec >= 30)?1:0;
+ break;
+ CASE_fmt_HH
+ tm->tm_hour += (tm->tm_min >= 30)?1:0;
+ tm->tm_min = 0;
+ break;
+ }
+
+ tm->tm_sec = 0;
+
+ if (redotz)
+ tz = DetermineTimeZoneOffset(tm, session_timezone);
+
+ if (tm2timestamp(tm, fsec, &tz, &result) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/expected/dbms_output.out
----------------------------------------------------------------------
diff --git a/contrib/orafce/expected/dbms_output.out b/contrib/orafce/expected/dbms_output.out
new file mode 100644
index 0000000..0ae40d4
--- /dev/null
+++ b/contrib/orafce/expected/dbms_output.out
@@ -0,0 +1,1043 @@
+\set ECHO none
+DROP FUNCTION dbms_output_test();
+ERROR: function dbms_output_test() does not exist
+DROP TABLE dbms_output_test;
+ERROR: table "dbms_output_test" does not exist
+-- 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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+--------+--------
+ <NULL> | 1
+(1 row)
+
+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();
+ORAFCE
+orafce
+ABC
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORA
+F
+CE
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORAFCE
+ABC
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORA
+F
+CE
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+ ORAFCE TEST 2 | 0
+(2 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+ ORAFCE TEST 3 | 0
+ <NULL> | 1
+ <NULL> | 1
+(4 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+ ORA | 0
+ <NULL> | 1
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+ | 0
+ <NULL> | 1
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT REPLACE(buff, '
+', '<LF>') FROM dbms_output_test;
+ replace
+-------------------
+ ORAFCE TEST 1<LF>
+ ORAFCE TEST 2
+(2 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT regexp_replace(buff, E'\n', '<LF>', 'g') FROM dbms_output_test limit 1;
+ regexp_replace
+----------------
+ ORA<LF>F<LF>CE
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 3
+ ORAFCE TEST 2 | 3
+ ORAFCE TEST 3 | 3
+ <NULL> | 0
+(4 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 2
+ ORAFCE TEST 2 | 2
+ ORAFCE TEST 3 | 1
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 1
+ ORAFCE TEST 3 | 1
+ <NULL> | 0
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 1
+ ORA | 1
+ <NULL> | 0
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 1
+ | 1
+ <NULL> | 0
+(3 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT regexp_replace(buff, E'\n', '<LF>', 'g') FROM dbms_output_test limit 1;
+ regexp_replace
+----------------
+ ORA<LF>F<LF>CE
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+------+--------
+ ORA | 2
+ FCE | 2
+(2 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT buff FROM dbms_output_test;
+
buff
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
+ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAA
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+--------+--------
+ <NULL> | 1
+ <NULL> | 1
+ <NULL> | 1
+ | 0
+ <NULL> | 1
+(5 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+--------+--------
+ <NULL> | 0
+(1 row)
+
+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();
+ORAFCE TEST 1
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+------+--------
+(0 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+ ORAFCE TEST 2 | 0
+(2 rows)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 1
+(1 row)
+
+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();
+AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAA
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+SELECT * FROM dbms_output_test;
+ buff | status
+---------------+--------
+ ORAFCE TEST 1 | 0
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORAFCE TEST 2
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORAFCE TEST 2
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ dbms_output_test
+------------------
+
+(1 row)
+
+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();
+ORAFCE TEST 1
+ dbms_output_test
+------------------
+
+(1 row)
+
+DROP FUNCTION dbms_output_test();
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/expected/files.out
----------------------------------------------------------------------
diff --git a/contrib/orafce/expected/files.out b/contrib/orafce/expected/files.out
new file mode 100644
index 0000000..e1fe06e
--- /dev/null
+++ b/contrib/orafce/expected/files.out
@@ -0,0 +1,126 @@
+\set ECHO none
+INSERT INTO utl_file.utl_file_dir(dir) VALUES(utl_file.tmpdir());
+CREATE OR REPLACE FUNCTION gen_file(dir text) RETURNS void AS $$
+DECLARE
+ f utl_file.file_type;
+BEGIN
+ f := utl_file.fopen(dir, 'regress_orafce.txt', 'w');
+ PERFORM utl_file.put_line(f, 'ABC');
+ PERFORM utl_file.put_line(f, '123'::numeric);
+ PERFORM utl_file.put_line(f, '-----');
+ PERFORM utl_file.new_line(f);
+ PERFORM utl_file.put_line(f, '-----');
+ PERFORM utl_file.new_line(f, 0);
+ PERFORM utl_file.put_line(f, '-----');
+ PERFORM utl_file.new_line(f, 2);
+ PERFORM utl_file.put_line(f, '-----');
+ PERFORM utl_file.put(f, 'A');
+ PERFORM utl_file.put(f, 'B');
+ PERFORM utl_file.new_line(f);
+ PERFORM utl_file.putf(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]', '1', '2', '3', '4', '5');
+ PERFORM utl_file.new_line(f);
+ PERFORM utl_file.put_line(f, '1234567890');
+ f := utl_file.fclose(f);
+END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION read_file(dir text) RETURNS void AS $$
+DECLARE
+ f utl_file.file_type;
+BEGIN
+ f := utl_file.fopen(dir, 'regress_orafce.txt', 'r');
+ FOR i IN 1..11 LOOP
+ RAISE NOTICE '[%] >>%<<', i, utl_file.get_line(f);
+ END LOOP;
+ RAISE NOTICE '>>%<<', utl_file.get_line(f, 4);
+ RAISE NOTICE '>>%<<', utl_file.get_line(f, 4);
+ RAISE NOTICE '>>%<<', utl_file.get_line(f);
+ RAISE NOTICE '>>%<<', utl_file.get_line(f);
+ EXCEPTION
+ -- WHEN no_data_found THEN, 8.1 plpgsql doesn't know no_data_found
+ WHEN others THEN
+ RAISE NOTICE 'finish % ', sqlerrm;
+ RAISE NOTICE 'is_open = %', utl_file.is_open(f);
+ PERFORM utl_file.fclose_all();
+ RAISE NOTICE 'is_open = %', utl_file.is_open(f);
+END;
+$$ LANGUAGE plpgsql;
+SELECT gen_file(utl_file.tmpdir());
+ gen_file
+----------
+
+(1 row)
+
+SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt');
+ fexists
+---------
+ t
+(1 row)
+
+SELECT utl_file.fcopy(utl_file.tmpdir(), 'regress_orafce.txt', utl_file.tmpdir(), 'regress_orafce2.txt');
+ fcopy
+-------
+
+(1 row)
+
+SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce2.txt');
+ fexists
+---------
+ t
+(1 row)
+
+SELECT utl_file.frename(utl_file.tmpdir(), 'regress_orafce2.txt', utl_file.tmpdir(), 'regress_orafce.txt', true);
+ frename
+---------
+
+(1 row)
+
+SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt');
+ fexists
+---------
+ t
+(1 row)
+
+SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce2.txt');
+ fexists
+---------
+ f
+(1 row)
+
+SELECT read_file(utl_file.tmpdir());
+NOTICE: [1] >>ABC<<
+NOTICE: [2] >>123<<
+NOTICE: [3] >>-----<<
+NOTICE: [4] >><<
+NOTICE: [5] >>-----<<
+NOTICE: [6] >>-----<<
+NOTICE: [7] >><<
+NOTICE: [8] >><<
+NOTICE: [9] >>-----<<
+NOTICE: [10] >>AB<<
+NOTICE: [11] >>[1=1, 2=2, 3=3, 4=4, 5=5]<<
+NOTICE: >>1234<<
+NOTICE: >>5678<<
+NOTICE: >>90<<
+NOTICE: finish no data found
+NOTICE: is_open = t
+NOTICE: is_open = f
+ read_file
+-----------
+
+(1 row)
+
+SELECT utl_file.fremove(utl_file.tmpdir(), 'regress_orafce.txt');
+ fremove
+---------
+
+(1 row)
+
+SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt');
+ fexists
+---------
+ f
+(1 row)
+
+DROP FUNCTION gen_file(text);
+DROP FUNCTION read_file(text);
+DELETE FROM utl_file.utl_file_dir;