orafce

When migrating DBMS_ALERT from Oracle to orafce, be aware of these behavioral differences:

1. Signal Serialization

• Oracle: DBMS_ALERT.SIGNAL is serialized. If multiple sessions signal the same alert, each signal waits until the preceding one is committed.
• PostgreSQL: DBMS_ALERT.SIGNAL is not serialized. A signal can be sent even if the preceding signal is not yet committed; the first one committed is reported.

2. Message Retention (Multiple Signals)

• Oracle: If multiple signals occur between a REGISTER and a WAIT call, only the last message is received. Earlier messages are discarded.
• PostgreSQL: If multiple signals occur between a REGISTER and a WAIT call, the first message is received. Subsequent messages are retained and not discarded.

Recommendation: If your application logic depends on receiving every single alert, ensure you use DBMS_ALERT.REMOVE or DBMS_ALERT.REMOVEALL to clean up alerts when they are no longer needed to prevent session buildup.

Orafce provides VARCHAR2 and NVARCHAR2 to emulate Oracle string types.

VARCHAR2

Implements BYTE semantics by default.

• Supports the bytes unit of type modifier.
• Unlike standard PostgreSQL varchar, implicit casts to VARCHAR2 do not truncate whitespace over the declared maximum length.
• Note: It does not impose the Oracle 4000-byte limit; it behaves like PostgreSQL varchar (up to ~10MB).

NVARCHAR2

Implements CHARACTER semantics.

• Uses the database's character set/encoding.
• Use this type if character-based semantics are preferred over byte-based semantics.
• Also does not impose the 4000-character limit.

Null-Safe Concatenation

-- Enable null-safe concatenation (similar to Oracle behavior)
SET orafce.varchar2_null_safe_concat TO true;

-- With concatenation enabled, NULLs in the chain are treated as empty strings
SELECT NULL || 'hello'::varchar2 || NULL;

There are behavioral differences when using subqueries within DML (Data Manipulation Language) statements, particularly regarding how concurrent sessions interact with data that is being modified by a subquery.

• Oracle: May detect that the result of a subquery has changed due to concurrent transactions and may rollback or rerun the statement to maintain consistency.
• PostgreSQL: May not detect the change in the subquery's result set in the same way, leading to different row counts being affected by concurrent deletes or updates.

Best Practice: To ensure consistent behavior across both Oracle and PostgreSQL, avoid using subqueries in DML statements and in SELECT ... FOR UPDATE queries.

-- Example of DML with Subquery difference
create table dml(a int, b int);
insert into dml values(1, 1), (2,2);

-- session 1: 
begin; 
delete from dml where a in (select min(a) from dml); 

-- session 2:  
delete from dml where a in (select min(a) from dml); 

-- session 1:  
commit;

-- Oracle: 0 rows in dml at last.
-- PostgreSQL: 1 row in dml at last.

When using orafce, some functions and data types behave differently than standard PostgreSQL to maintain Oracle compatibility. If public or oracle are in your search_path, the following differences apply:

Data Types

• DATE: In standard PostgreSQL, this stores date only. In orafce, it stores both date and time.

Functions

• LENGTH: For CHAR types, orafce includes trailing spaces in the length, whereas standard PostgreSQL does not.
• SUBSTR:
  • If 0 is specified as the start position, extraction starts from the beginning of the string.
  • If a negative value is specified, extraction starts from the position counted from the end of the string.
• LPAD / RPAD:
  • For CHAR types, orafce pads the value without removing trailing spaces.
  • The result length is based on string width: fullwidth characters count as 2, and halfwidth characters count as 1.
• LTRIM / RTRIM / BTRIM: For CHAR types, orafce removes the value without removing trailing spaces.
• TO_DATE: Returns a TIMESTAMP in orafce, whereas standard PostgreSQL returns a DATE.

Features requiring oracle in search_path

The following features cannot be used with the default PostgreSQL configuration and require oracle to be added to the search_path before pg_catalog:

• Functions: SYSDATE, DBTIMEZONE, SESSIONTIMEZONE, TO_CHAR (for date/time values).
• Operators: Datetime operators.

The DBMS_PIPE package enables inter-session communication in PL/pgSQL. It uses a local buffer (approx. 8 KB) to pack and send messages.

Pipe Types

Core Workflow

1. Create a pipe: PERFORM DBMS_PIPE.CREATE_PIPE('pipe_name', max_messages, private_boolean);.
2. Pack messages: Use PACK_MESSAGE(data) to add items to the local buffer. Supported types include Character, Integer (converted to NUMERIC), NUMERIC, DATE, TIMESTAMP (converted to TIMESTAMP WITH TIME ZONE), BYTEA, and RECORD.
3. Send/Receive: Use SEND_MESSAGE to push the buffer to a pipe, or RECEIVE_MESSAGE to pull from one.
4. Unpack: Use UNPACK_MESSAGE_TEXT, UNPACK_MESSAGE_NUMBER, etc., to extract data from the local buffer after receiving.
5. Cleanup: Use PURGE('pipe_name') to empty a pipe or REMOVE_PIPE('pipe_name') to delete an explicit pipe.

Important Constraints

• A single instance can use up to 50 pipes concurrently.
• Memory Warning: Repeatedly creating and removing Private pipes can lead to memory exhaustion because creator information persists even after removal. Use Public pipes for frequent creation/removal cycles.
• If a timeout occurs during RECEIVE_MESSAGE on an implicit pipe, the pipe is not removed.

PERFORM DBMS_PIPE.CREATE_PIPE('P01', 100, FALSE);
PERFORM DBMS_PIPE.PACK_MESSAGE('Message Test001');
PERFORM DBMS_PIPE.SEND_MESSAGE('P01');