Oracle 19c with APPX 6.2.1 on Windows
Set up Oracle as an APPX file system using FMS type 5. Start with one file, then save a record in APPX and check it in Oracle.
APPX 6.2.1 · 64-bit engine Oracle 19c · Instant Client 19.28What to install
This example was tested with the 64-bit APPX 6.2.1 engine on Windows 11 Pro 25H2, Oracle Database 19c Enterprise Edition (19.3), and Oracle Instant Client 19.28 Basic Light. APPX and Oracle ran on the same host. If Oracle already runs elsewhere, install Instant Client on the APPX server and use the Oracle host in the connection alias.
| Component | Download |
|---|---|
| APPX server | Install the 64-bit Windows engine. APPX 6.2.1 Windows server installer · Installation instructions |
| APPX Desktop Client | Use the 6.2.1 Desktop Client to open the APPX session. Windows Desktop Client installer · Other desktop platforms |
| Oracle Database | For a new database server, Oracle supplies WINDOWS.X64_193000_db_home.zip. Check the platform and release update requirements before installing. Oracle Database 19c for Windows x64 · Oracle installation guide |
| Oracle Instant Client | Install the 64-bit packages on the APPX server. The test used Basic Light and SQL*Plus 19.28. 19.28 Basic Light ZIP · 19.28 SQL*Plus ZIP · Oracle Instant Client downloads |
The public Database Home download is the 19.3 base image. Oracle supports Windows 11 starting with Release Update 19.13. For a new installation, use a release update Oracle supports for your Windows version. The 19.3 combination above describes the existing lab used for this guide. Oracle’s Windows operating system checklist
Choose a database and create a login
Ask your DBA for the listener host and port, the service name for the database, and the tablespace for APPX tables. On a local server, a DBA can check the service names with lsnrctl status.
| Example database | Database character set | Use |
|---|---|---|
APPXMB | AL32UTF8 | Multibyte database used in this walkthrough. |
APPXSB | WE8MSWIN1252 | Separate single-byte database for an application that needs that character set. |
Connect as a DBA to the APPXMB database in SQL*Plus. The Windows lab uses separate databases. If you use a container database, connect to the service for the intended pluggable database (PDB) before creating the user. Substitute a password for your test schema. This example uses the USERS tablespace and a small quota.
SELECT value FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';
CREATE USER APPX_ORA_DEMO IDENTIFIED BY "DemoOra19_42"
DEFAULT TABLESPACE USERS QUOTA 25M ON USERS;
GRANT CREATE SESSION, CREATE TABLE TO APPX_ORA_DEMO;
Use the same database service for the schema and the APPX connection. In this example the character set query returns AL32UTF8. The record test below uses ASCII text. Oracle CREATE USER
Set up Oracle Instant Client
- On the APPX server, install the x64 Microsoft Visual C++ Redistributable for Oracle Instant Client 19c and unpack the 64-bit Basic Light and SQL*Plus ZIPs into the same directory. The ZIPs create
instantclient_19_28. This example usesC:\Oracle\instantclient_19_28. Oracle Instant Client installation and runtime requirements - Put
tnsnames.orain a directory the APPX engine can read, such asC:\Appx\oracle-net. Define the alias below. Change the host when Oracle runs elsewhere. The service name in the descriptor must match the service registered with the listener.
APPXMB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = appxmb))
)
For a separate single-byte database, use an APPXSB alias with that database’s service name, such as appxsb.
- Set these values in the APPX engine’s
appx.env. Use the path where you unpacked Instant Client.
APPX_OCI_DIR=C:\Oracle\instantclient_19_28
APPX_OCI_LIB=oci.dll
TNS_ADMIN=C:\Appx\oracle-net
For a PowerShell test, set TNS_ADMIN and run the SQL*Plus executable from the Instant Client directory.
$env:TNS_ADMIN = 'C:\Appx\oracle-net'
& 'C:\Oracle\instantclient_19_28\sqlplus.exe' -L APPX_ORA_DEMO@APPXMB
Enter the schema password at the prompt. Confirm the connection goes to the right service before opening APPX.
SELECT USER, SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM dual;
Set up the FMS group and identity
- Open a new APPX Desktop Client session. Choose System Administration → File Management → FMS Groups.
- Add
APPX-ORACLEand enter FMS type 5. Click FMS Group Attributes. - Enter these values and save the group options. Leave the other options at their defaults for this example.
| FMS option | Value |
|---|---|
| Server Name | APPXMB |
| Table Naming Scheme | APPX_$db_$appl_$file |
| Tablespace | USERS |
- On the group row, click Identities. Leave APPX User blank for the default identity. Press Enter to accept the blank key and again to open the details. To set credentials for just one APPX user, enter that user’s ID instead.
- Enter
APPX_ORA_DEMOin User Id and leave Realm blank. Click Change Password, enter the schema password twice, and click Change Password again. When APPX reports Password Changed, END to Exit, close the password dialog and save the identity. - Open a new APPX session before testing. APPX can keep the earlier settings for the life of a session.
FMS type 5 uses Oracle row locks. No proxy database ID is needed. In the test, a second Oracle session could read the Canada row while APPX held it in Change mode. A request to lock that row with FOR UPDATE NOWAIT returned ORA-00054 until APPX released it.
Assign one file and create the table
Use a test database and a file you can create empty. This example uses the Demo Application’s COUNTRY file.
- Open System Administration → Runtime Configuration → Database Definitions. Add
TSTwith the company name Oracle Test. Set the startup application toDMO, version00. Save and accept the report defaults. - For
TST, open Related Applications. AddDMOin Live Operations. Select theDMOrow and click Database Management. - In File Selection, click Unselect All, then Select. Enter
COUNTRYin Name and accept the selection. Return to Data File Management and check that it says 1 of 11 Files Currently Selected. - Open File Specifications and set FMS to 5 and FMS Group to
APPX-ORACLEforCOUNTRY. Save and return. - Choose Create Files. The result for
COUNTRYshould say Created.
Oracle stores the table as APPX_ORA_DEMO.APPX_TST_DMO_COUNTRY. APPX also created a unique index on COUNTRY_NAME.
To move an existing APPX/IO file, back it up and export it before changing its FMS type. Rehearse the move on a copy. Assign the Oracle FMS group, create the target table and import the export. Compare the record count and check a few keys in APPX before using the converted file. Move an existing APPX/IO file into Oracle
Field types in Oracle
See APPX field type mappings for the column types APPX created. In SQL*Plus, run DESC APPX_TST_DMO_COUNTRY for a quick check. Query USER_TAB_COLUMNS for character length semantics, numeric precision, and scale.
Save and check a record
- From the APPX main menu, choose Run an Application and execute database
TST. In the demo’s top menu, choose Setup → Countries. - In Country Address Format Rules, choose Add. Enter
CANADAfor Country Name,PROVINCEfor Region Label, and2for # Words In Postal Code. Leave Postal Code Location at AFTER REGION. Click Save. - Open a fresh Desktop Client session, run
TST, and go back to Setup → Countries. In Inquire mode, look upCANADAand check that the saved values come back.
With only COUNTRY created, the demo may report Cannot Access Required File for PROSPECT. Click Cancel on that message to continue the Countries test. Create the full demo database if you want to run the rest of the application.
In SQL*Plus, connect as APPX_ORA_DEMO through APPXMB and check the same key.
SELECT RTRIM(country_name) AS country_name,
RTRIM(country_region_label) AS region_label, country_no_words
FROM appx_tst_dmo_country
WHERE country_name = 'CANADA';
The lab returned one row with CANADA, PROVINCE, and 2.
Troubleshooting
Check the APPX engine
This guide uses FMS type 5 (Oracle) through the Oracle Call Interface (OCI).
In APPX Release Information, record Engine Release and Compile Date. The application release numbers on the same screen identify the APPX system applications. Match the database client or ODBC driver to the bitness of the engine that runs the operation. Check the settings on the APPX server and under the Windows or Linux account that runs the engine.
Test the connection outside APPX
On the APPX server, use SQL*Plus with the same Oracle alias and database login as APPX. Check TNS_ADMIN and the alias for ORA-12154. For ORA-12514, check the service name with lsnrctl status on the database server and confirm the PDB is open.
If APPX cannot initialize Oracle, check APPX_OCI_DIR, APPX_OCI_LIB, and oci.dll in the 64-bit Instant Client installation. Confirm the required x64 Microsoft Visual C++ Redistributable is installed.
Check the FMS group and Identity
Confirm the FMS group’s Server Name matches the alias that worked in SQL*Plus.
Under Identities, look for an active, unexpired entry for the APPX user running the operation. That entry takes precedence over the blank default. Check User Id and re-enter the database password with Change Password. Save the Identity and open a fresh APPX session. APPX can cache connection and Identity settings for the life of the session.
For ORA-01017, check the password and confirm the user exists in the PDB behind the alias.
Check where APPX creates the table
If the connection succeeds, read the Create Files result or open View Log for the database error. Check the database, login, and target schema or database with the same connection settings as APPX. Compare the APPX database ID, application ID, file name, and Table Naming Scheme with the SQL table name.
Check the PDB, the Oracle user’s current schema, CREATE TABLE, and the schema’s quota on the selected tablespace. ORA-01950 points to a quota problem.
Capture one failing operation
Make a copy of the
appx.envunder the effectiveAPPXPATHof the engine that runs the process. Create a private log directory that the engine account can write to, then temporarily set the variables below. Keep$(pid)as written. The SQL logger replaces it with the engine process ID.APPX_SQL_CMD=C:\APPX\Logs\appx-rdbms-$(pid).log APPX_DBG_CODE=0x1FThe mask includes generated SQL, return statuses, fetch attempts, and engine and connection operations. Use
0xFFFFFFFFfor one attempt if support needs more detail. The engine reads these settings on startup.APPX_ODBC_LOGandAPPX_ODBC_CODEapply to the driver used by external programs to read APPX data.- Keep the Oracle client alias and FMS group you want to diagnose. Use the APPX SQL log and the Oracle error from the failing operation. APPX Oracle debugging
- Open a fresh APPX session and reproduce the failure once. For Create Files, select only the intended test file. For a failed
READwithHOLD, note the APPX error and flags before the nextWRITE. Try a plainREADof the same key in another fresh session. If the log is missing, checkAPPXPATH, the effective variables, and the engine account’s access to the log directory. - Exit the test sessions and restore the previous
appx.envsettings.
If an insert follows a failed held read, keep the first error. A later duplicate-key error indicates that the key existed when the insert ran. Use the insert branch only when the read explicitly reports that the record was absent. Handle lock and database errors before trying another write.
Share the SQL statement, fetch or lock result, and the first SQLSTATE, native error number, and message when available. Include the APPX flags, file and operation, timestamp and time zone, APPX engine release, build date and bitness, operating system, driver name and version, and database versions before and after an upgrade. Keep the SQL structure and error codes intact and replace passwords and record values with placeholders. The APPX SQL log can contain the engine environment and full connection strings. APPX environment settings