Subject: No documentation exists about ecpg ORACLE comptaible mode

From: "[email-masked]" <[email-masked]>
Date: Tue, 7 Jun 2022 08:28:55 +0000

Hi. 
(CCing Michael Meskes)

One of my customers asked me about what oracle compatibility mode in ecpg means, 
and I noticed current documentation does not explain ecpg ORACLE compatible mode  (ecpg -C ORACLE) in detail.
I myself learned the feature of "-C ORACLE"  from regression test (src/interfaces/ecpg/test/compat_oracle) and source code. 

Is lacking documentation intentional or do commit 3b7ab4380 and 3b9b7516f just forget adding the explanation?

As for as I know, "ecpg -C ORACLE" is written briefly in ecpg command reference and PostgreSQL 11 release note,
but they are not enougn information for users to understand feature of "ecpg -C ORACLE".

https://www.postgresql.org/docs/current/app-ecpg.html

-------------------------------
-C mode
Set a compatibility mode. mode can be INFORMIX, INFORMIX_SE, or ORACLE.
-------------------------------

https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.21.5.9
-------------------------------
Add an ecpg mode to enable Oracle Pro*C-style handling of char arrays.

This mode is enabled with -C.
-------------------------------

On the other hand, informix compatible mode is written in detail.
https://www.postgresql.org/docs/current/ecpg-informix-compat.html

Best regards,
Takeshi Ideriha

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 9 Jul 2022 21:15:52 -0400

> https://www.postgresql.org/docs/current/app-ecpg.html

This is a very good point.  I have studied the issue and created the
attached patch to document Oracle-compatibility mode.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 7f8b4dd5c0..999b7ca0ae 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -1890,7 +1890,8 @@ EXEC SQL SELECT b INTO :val :val_ind FROM test1;
 </programlisting>
     The indicator variable <varname>val_ind</varname> will be zero if
     the value was not null, and it will be negative if the value was
-    null.
+    null.  (See <xref linkend="ecpg-oracle-compat"/> to enable
+    Oracle-specific behavior.)
    </para>
 
    <para>
@@ -9799,6 +9800,42 @@ risnull(CINTTYPE, (char *) &i);
   </sect2>
  </sect1>
 
+ <sect1 id="ecpg-oracle-compat">
+  <title><productname>Oracle</productname> Compatibility Mode</title>
+  <para>
+   <command>ecpg</command> can be run in a so-called <firstterm>Oracle
+   compatibility mode</firstterm>. If this mode is active, it tries to
+   behave as if it were Oracle <productname>Pro*C</productname>.
+  </para>
+
+  <para>
+   Specifically, this mode changes <command>ecpg</command> in three ways:
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      Pad character arrays receiving character string types with
+      trailing spaces to the specified length
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      Zero byte terminate these character arrays, and set the indicator
+      variable if truncation occurs
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      Set the null indicator to <literal>-1</literal> when character
+      arrays receive empty character string types
+     </para>
+    </listitem>
+   </itemizedlist>
+  </para>
+ </sect1>
+
  <sect1 id="ecpg-develop">
   <title>Internals</title>

---

From: Michael Meskes <[email-masked]>
Date: Tue, 12 Jul 2022 10:20:08 +0200

On Sat, Jul 09, 2022 at 09:15:52PM -0400, Bruce Momjian wrote:
> ...

Looks good to me. Thanks a bunch Bruce.

I wonder if we should also mention somewhere that some other Oracle
compatibility features are enable in all modes, like a slightly different
CONNECT syntax.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De
Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 12 Jul 2022 14:46:14 -0400

> Looks good to me. Thanks a bunch Bruce.

Great, thanks.

> compatibility features are enable in all modes, like a slightly different

Good question.  We support syntax from other databases, only sometimes
with documentation.  I think the rules we use are that we document
compatibility-only behavior when it is useful and obviously if it is
turned in by an option.

In contrast, look at this from backend/parser/gram.y:

	/* INOUT is SQL99 standard, IN OUT is for Oracle compatibility */
	arg_class:  IN_P                                { $$ = FUNC_PARAM_IN; }
	            | OUT_P                             { $$ = FUNC_PARAM_OUT; }
	            | INOUT                             { $$ = FUNC_PARAM_INOUT; }
	            | IN_P OUT_P                        { $$ = FUNC_PARAM_INOUT; }
	            | VARIADIC                          { $$ = FUNC_PARAM_VARIADIC; }

We don't document "IN OUT" anywhere, we just support it silently for
Oracle compatibility, and I am guessing your ecpg connection syntax is
similar.  I think this is done so we don't confuse people with syntax
that has not value unless they are coming from another database. 
Therefore, I don't think we should document it, but I would be
interested to hear if anyone disagrees.

---

From: "[email-masked]" <[email-masked]>
Date: Thu, 14 Jul 2022 02:41:45 +0000

Bruce Momjian <[email-masked]> wrote:

> This is a very good point.  I have studied the issue and created the attached patch to document Oracle-compatibility mode.

Hi Bruce, thank you for writing the document.
I checked it and I'm very glad!

Regards,
Takeshi Ideriha

---

From: 'Bruce Momjian' <[email-masked]>
Date: Thu, 14 Jul 2022 16:20:34 -0400

> I checked it and I'm very glad!

Patch applied to PG 11, where the feature was added, and later.