Skip to content
Documentation menu

Oracle

Connecting Oracle databases: connection, read-only user, current schema, dialect, procedures and REF CURSOR, ORA- errors.

Oracle is the database of most ERPs Prynt connects to. This guide covers the connection, the database user, the Oracle specifics of datasets and the errors you may meet. The general concepts are in Data sources and Datasets.

Connect an Oracle database

Create a data source of type Oracle and configure a connection for each environment. Prynt connects with the managed ODP.NET driver and builds the connection descriptor from these fields: you never paste a connection string or a TNS alias.

Hoststringrequired
The database host. With a gateway, as the gateway sees it (erp-db.local, 10.0.0.12); direct, a public DNS name.
Portinteger
The listener port, default 1521 (TCPS listeners often use 2484). Direct connections allow 1521–1526, 2483, 2484.
Service namestringrequired
The service, for example ORCLPDB1 or the PDB of the ERP. Connections by SID are not supported: use the service name.
Username, Passwordcredentialsrequired
A dedicated read-only user (below).
Current schemastring
The owner of the ERP tables, to write FROM CUSTOMERS instead of ERP.CUSTOMERS (below).
Schemas in the explorerstring[]
The owners the database explorer lists. Empty = every schema the user can see, except the ones maintained by Oracle.
TLSmode
Disabled (only behind a gateway), or TCPS: Required, Verify CA, Verify certificate and host.
  • On a private network (the usual case for an ERP), connect through the Prynt Gateway: nothing to open in the firewall, and the credentials are encrypted for the gateway.
  • Oracle in a public cloud (OCI, AWS RDS, Azure) can use a direct connection over TCPS.

Save connection tests the connection: Connected · 31 ms · Oracle 19c · user PRYNT_READER. The test also reads the session's privileges and roles and warns about DBA or ANY privileges, CREATE privileges, write grants or owned tables.

A read-only user

Prynt only reads, but give it a user that cannot write: a dedicated user with CREATE SESSION and READ (or SELECT) on the tables and views your templates need, and EXECUTE only on read-only routines used by Function datasets. The connection test offers this script when the user has more privileges than needed:

Oracle · read-only user
-- Oracle: a dedicated read-only user for Prynt (run as DBA)CREATE USER PRYNT_READER IDENTIFIED BY "<a strong password>";GRANT CREATE SESSION TO PRYNT_READER;-- READ (12.1.0.2+) allows SELECT but not SELECT … FOR UPDATE; repeat for each table or view:GRANT READ ON ERP.CUSTOMERS TO PRYNT_READER;-- or, for every table of the owner:BEGIN  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'ERP') LOOP    EXECUTE IMMEDIATE 'GRANT READ ON ERP."' || t.table_name || '" TO PRYNT_READER';  END LOOP;END;/-- Routines used by Function datasets (read-only routines only):GRANT EXECUTE ON ERP.PKG_PRINT TO PRYNT_READER;

New tables after an ERP update

Grants are per object: a table added by an ERP update needs its own grant. Without it, queries fail with ORA-00942.

Owners and current schema

The read-only user usually does not own the ERP tables: the owner is, for example, ERP. You can write the owner in every query (FROM ERP.CUSTOMERS) or set the owner as the connection's current schema: Prynt runs ALTER SESSION SET CURRENT_SCHEMA when it opens the session, and FROM CUSTOMERS resolves to ERP.CUSTOMERS. With a current schema, the same SQL works at every installation, whatever the owner is called there, and function names without an owner resolve there too. Unquoted names are upper case: customers = CUSTOMERS.

A gateway can restrict the schemas it reads with AllowedSchemas in prynt-gateway.json.

TLS (TCPS)

  • With TLS enabled Prynt connects to a TCPS listener. Verify certificate and host also matches the server certificate's DN (optionally against the server certificate DN you enter); Required and Verify CA use TCPS without the DN match.
  • Connections are wallet-less: the server certificate must be trusted by the operating system's certificate store of the machine that connects (the gateway host, or Prynt for direct connections). Client certificates and wallets are not supported.
  • Direct connections must use TLS. Behind a gateway, on your own network, TLS may be disabled; the test warns about it.

Writing queries for Oracle

Dataset LINES
SELECT r.LINE_NO, r.ITEM, a.DESCRIPTION, r.QTY, r.PRICE  FROM DDT_LINES r  JOIN ITEMS a ON a.COMPANY = r.COMPANY AND a.CODE = r.ITEM WHERE r.COMPANY = :DITTA   AND r.DDT_ID  = :DOCUMENT_ID   AND (:ONLY_ACTIVE = 0 OR a.ACTIVE = 1) ORDER BY r.LINE_NO
  • A dataset is a single SELECT or WITH; a trailing ; is removed. No PL/SQL blocks, and no WITH FUNCTION: for PL/SQL logic use a Function dataset.
  • Bind variables are written :NAME, as in Oracle. The system values :DATE and :USER are Oracle reserved words: Prynt renames them for you.
  • Oracle SQL as usual: FETCH FIRST 10 ROWS ONLY, NVL/COALESCE, ||, FROM DUAL, SYSDATE, TRUNC(date), alternative quoting q'[it's]'.
  • Empty text is NULL in Oracle: WHERE col = '' is never true and an empty text parameter arrives as NULL. Optional filter: (:CUSTOMER IS NULL OR c.CODE = :CUSTOMER).
  • Yes/no: before Oracle 23ai SQL has no boolean, so a boolean parameter arrives as 1 or 0. ERP flags such as NUMBER(1) or CHAR(1) 'S'/'N' arrive as numbers or texts: compare them explicitly in expressions.
  • Sorting text can follow the session's linguistic rules; for binary order use ORDER BY NLSSORT(col, 'NLS_SORT=BINARY').

Dates, numbers and NLS

  • Prynt does not change the session's NLS settings. Parameters are bound with their types (a date parameter arrives as an Oracle DATE at midnight), so they never depend on NLS_DATE_FORMAT.
  • Avoid implicit conversions in SQL, such as TO_CHAR(SYSDATE) without a format or comparing a date with a text: their result depends on the session's NLS settings. Write the format (TO_DATE('2026-09-27', 'YYYY-MM-DD')) or, better, return dates and numbers and let the template format them.
  • To include a whole day: d.DOC_DATE >= :FROM AND d.DOC_DATE < :TO + 1.
  • An Oracle DATE always has a time: give the field a format such as dd/MM/yyyy, or 00:00 prints too.

Data types

NUMBER(p,0), p ≤ 18integer
Integer columns.
NUMBER, FLOAT, BINARY_FLOAT/DOUBLEdecimal
Other numbers, also NUMBER without precision, COUNT(*), SUM and calculations.
VARCHAR2, CHAR, NVARCHAR2, CLOB, NCLOB, LONGstring
Also intervals, XMLTYPE and JSON as text. LOBs are read whole.
DATE, TIMESTAMPdateTime
A DATE always has a time.
TIMESTAMP WITH (LOCAL) TIME ZONEdateTime
Converted to UTC.
RAW, LONG RAW, BLOBbinary
Images from a field.
BOOLEAN (23ai)boolean
Native SQL booleans.

Object types, collections, BFILE, VECTOR and nested cursors are not supported as columns (SQL_TYPE_NOT_SUPPORTED): convert them in the query.

Functions, procedures and REF CURSOR

A dataset with the Function source calls a routine: choose it from the list (OWNER.FUNCTION or OWNER.PACKAGE.FUNCTION) to see its arguments, and bind each IN argument, in order. Names without an owner resolve in the current schema and in synonyms; routines over a database link are refused.

PIPELINED functionTABLE(…)
Or a function returning a collection of an object type: one row per element, one column per attribute.
Function → SYS_REFCURSORcursor
The rows of the cursor.
Procedure with OUT SYS_REFCURSORcursor
Exactly one OUT cursor, the other arguments IN: the rows of the cursor.
Function → valueFROM DUAL
One row with one column named after the function.
Function → objectone row
One column per attribute.
How the engine calls routines
-- A procedure with one OUT SYS_REFCURSOR: the engine runsBEGIN ERP.PKG_PRINT.DDT_LINES(:DITTA, :CURSOR, :DOCUMENT_ID); END; -- A pipelined function (or one returning a collection):SELECT * FROM TABLE(ERP.PKG_PRINT.DDT_LINES(:DOCUMENT_ID))
  • Arguments are converted to their declared types (no TO_DATE needed). Trailing arguments with a default can be omitted.
  • Not usable: functions with OUT arguments, procedures without an OUT cursor or with other OUT arguments, routines returning PL/SQL-only types (BOOLEAN, RECORD).
  • Routines must only read: they run in a read-only transaction with commits disabled, so a write fails (ORA-01456, ORA-14551) and so does a COMMIT (ORA-00034). Procedures that fill work tables must be replaced, for example by a pipelined function.
  • The columns of cursors are known only by running them: Run, then Refresh fields.

Troubleshooting ORA- errors

Database errors show the Oracle code (ORA-00904), with line and column for syntax and name errors. Messages come in the language of the database server.

ORA-00942SQL
Table or view does not exist: the name does not resolve (write the owner, or set the current schema) or the user has no grant on it.
ORA-00904SQL
Invalid identifier: a column name is wrong, or a lower-case quoted name.
ORA-01722SQL
Invalid number: an implicit conversion of a text that is not a number.
ORA-01013timeout
The query was cancelled at its timeout (SQL_TIMEOUT): add filters or indexes, or raise the dataset timeout.
ORA-01017login
Invalid username or password (also ORA-28000 account locked, ORA-28001 password expired). The data source is unavailable until fixed.
ORA-12514listener
The listener does not know the service name: check the service (not the SID).
ORA-12541, ORA-12170network
No listener at host and port, or connect timeout: check host, port and firewall from the gateway's server.
ORA-01435schema
The current schema does not exist: check its spelling in the connection.
ORA-01456, ORA-00034read-only
A routine tried to write or commit: data sources are read-only.

Network and login errors (ORA-12xxx, ORA-01017, ORA-28000, instance down) make renders fail with 503 DATA_SOURCE_UNAVAILABLE; query errors with 422 DATASET_FAILED. Test connection on the data source shows the full reason.

See also

PostgreSQL for PostgreSQL databases, Gateway for private networks.