# Special File Command Tags

[OCI] | Connect to Oracle Server

## Format

OPEN (chan[fileopt])"[OCI]sid[fileopt]"

Where:

[OCI] | File tag clause to inform PxPlus that it will be opening an Oracle database.
chan | Channel or logical file number to open.
fileopt | File options. Supported options include:
BSZ=num | Block size
ERR=stmtref | Error transfer
IOL=iolref | Default IOList
NBF=num | Dedicated number of buffers
OPT=string$ | Open parameters (see [OCI] OPT= Parameters)
REC=string$ | Record prefix (REC=VIS(string$) can also be used)
sid | Oracle System ID of file to open. If not supplied, then the value of the environment variable ORACLE_SID is used. String expression. A name of the table may follow a sid with a ; (semi-colon) as a separator to open a specific table. If the table name is not supplied, then SQL statements sent to the database must be created by the application (see Using SQL Directly Within PxPlus). Example: To open a table called people on an Oracle System DEMO, a sid value of "DEMO;people" would be used.

#### Important Note:
To use the OCI interface, you must have either the Oracle Server or the Oracle client installed on a workstation, and the path to the oci.dll file must be locatable by the PATH environment variable.
When a workstation is connecting to a remote server, the IP address of the server is included in the sid value in the following format: "ip_address/ORACLE_SID;table".

## Description

The [OCI] tag is used as a prefix in an OPEN statement to denote that PxPlus is to route all file I/O requests to an external (Windows, not PxPlus) Oracle database file. (OCI is an acronym for Oracle Call Interface.) Once you open a channel for [OCI] use, you can use it just like any other channel (i.e. for file I/O). It remains open until you close it.

#### Note:
This feature requires PxPlus OCI activation (available for Windows, Redhat, HP UX, Sun Solaris, and AIX). Refer to the PxPlus website www.pvxplus.com for platform specifics.

## [OCI] OPT= Parameters

The OPEN options for connecting to an Oracle server are:

AUTO_INDEX=Y | N | Used to include hints and index numbers to SELECT statements.
DATEFMT=format | Date format mask applying to all date fields in table. This can be a combination of Y M D with any other characters. Example: To convert dates to 4-character year, month and day: DATEFMT=YYYYMMDD Other characters are inserted as is. DATEFMT=YY/MM/DD with a date of March 1, 2004 would be returned as 04/03/01.
DEBUGIT=xxxx | String to append to SQL statement along with program name and line number for debugging purposes. This must indicate the comment character(s) appropriate to the database. Example: "--" is the comment identifier for Microsoft SQL Server; anything after "--" is ignored by SQL Server when compiling the SQL statement.
EXTROPT=xxxx | Controls the format of the SELECT statement used to process an EXTRACT. By default, PVX generates a SELECT * FROM table FOR UPDATE WHERE... When EXTROPT=text, then text is substituted in place of FOR UPDATE. In addition, if the first character of text is $, then the remaining characters of text are placed at the end of the SELECT statement rather than after the filename. This allows for different variations of SQL to be supported.
IND=fld | Identifies a column that contains a sequential number starting at 0. This is used to emulate an indexed file.
IOPROG=progname | Emulates the embedded I/O program logic available with a true PxPlus file.
KEY=fld, fld | Identifies fields that make up the key(s). For named keys, enter *NAME:keyname. OPEN(chan)"[OCI]sid;table;KEY=field,field,*NAME:keyname" Use the :D option to indicate that the key segment is to be sorted in descending order. KEY=KeyFld1,KeyFld2:D,KeyFld3
KEYDATA=fld | Identifies a column that represents the key. This is used to emulate an external key where the data is not duplicated in data.
MAS90DATE | Reformats the contents of a date column to and from the Sage MAS 90 date format.
MAS90SET | Sets flags for Sage MAS 90 emulation, such as turning on the MAS90DATE conversion.
NONULLS= | Inserts zero-length strings rather than nulls into the target database and does not generate WHERE clauses checking for IS NULL or IS NOT NULL. (INI Supported) Set to 1, Y or y to enable or 0, N or n to disable. If the application does not work correctly when moving from Version 5 or lower, then set NONULLS=P to indicate that keys are handled the same as pre-Version 6.
NOSTRIP | Keeps trailing spaces. (Default)
NULLPADKEY=Y | N | Set to 1, Y or y to force keys to be padded to full length with the null character, $00$.
ORACLE= Y | N | Indicates if the database uses ORACLE SQL sequence (either Y or N). If ORACLE= and TOP= are used, then SELECT commands are generated as SELECT * FROM (SELECT * FROM TABLE) WHERE ROWNUM < 1. Default is Y.
PREPARE= Y | N | Set to 1, Y or y to use prepared statements. Prepared statements are pre-compiled SQL that may improve performance. Default is N.
PSWD=xxxx. | Specifies password.
REC=fld, fld, ... | Provides the column names, type, and size. This is typically done to improve performance. If this information is not provided, then PxPlus must query the database for this information. See ODB/OCI/DB2 Record Processing.
RECDATA=fld | Identifies a column to return as full record. This can be used for variant records that use complex rules to identify the record type.
SHARED | Sets all tables to share a single connection to the Oracle database. (Default)
STDDATE | Overrides the above formatting on individual columns.
STRIP | Removes trailing spaces from fields
TEXTMAX=nnn | Overrides maximum size for text fields. (Default is 8192 bytes.)
TOP=nnn | Specifies use of the TOP clause in SELECT statements (limits the number of rows to return in a result set). If TOP=n is non-zero, then the KEF( ) / KEL( ) functions issue a SELECT TOP 1...SQL statement, which improves system performance. If TOP=n > 0, then PVX issues SELECT TOP n to reduce the data transferred. TOP=-1 indicates the driver supports SELECT TOP, but normal reading should not use it. Default is 0 (TOP not supported).
TYP=xxxx | Sets identifier for different variant records. See ODB/OCI Variant Record Processing.
UNIQUE=Y | N | Set to 1, Y or y to have new OPENs be on a new unique connection to the database. Default is N (shared connection).
USER=uuu | Specifies login name.
VALIDATE=Y | N | Set to 1, Y or y to validate the data. This option can be included in the respective INI section by database type. Date validation for database connections will only be included if the database OPEN specifies a date format using the DATEFMT= or MAS90DATE option. For additional data validation information, see IOLIST directive. (The VALIDATE option was added in PxPlus 2017.)

Use of Global String Variables as Option Values

In database connection options, you can provide the value following the = (equals sign) in global string variables. When opening the connection to the database, the system will check to see if the value following the = (equals sign) starts with a % and ends with a $ and is the name of a valid global string variable. If so, the value is replaced with the contents of that global variable.

For example, the option USER=%UserName$ would be dynamically changed by the EXE on the OPEN to replace the text %UserName$ with whatever is in that global variable.

(Support to allow the use of global variables for defining database Link/Prefix files was added in PxPlus 2023 Update 1.)

## See Also

READ Read Data from File
READ RECORD Read Record from File
SELECT Select/Query From ... Where
WRITE Add/Update Data in File
WRITE RECORD Write Record
OPEN Open a File for Processing
IOLIST Specify Variable List
[ADO] Microsoft SQL Server Interface
[DB2] DB2 Support
[MYSQL] Open MySql Native Database Link
[ODB] Open Database
Database Export Utility

## ODB/OCI/DB2 Record Processing

The REC= phrase is used to control the formatting of the data record as viewed by the PxPlus application. The format consists of a series of field descriptors and/or literals, each separated by either a , (comma) or a + (plus sign).

The simple format is:

> REC=fieldspec{ , | + } fieldspec …

Where:

> fieldspec contains the name of the field and optional format, length and scale.

Fields are separated by either a , (comma) or + (plus sign). When comma-separated, then a field delimiter is inserted. When plus-separated, then the field is padded to full size, and no separator is inserted. Literals may be included if enclosed in apostrophes.

Example:

> REC=CST_ID,NAME,ADDRESS

This results in a record with three fields, each separated by a field separator:

> REC=CST_ID+NAME+ADDRESS

This results in a record consisting of three fields with each one padded to its full length and no intervening field separator. If CST_ID is 6 characters long and NAME and ADDRESS are both 30, then the record would be 67 characters long, including the record terminator.

Any column name can be followed by an optional colon and format specification. This format specification consists of a data type (if not numeric or string) followed by the field length. If the field is numeric, the length includes a decimal point followed by the number of decimal positions.

The possible data types are:

| P | Packed (BIN) data
| H | Data is stored in Hex
| B | Data is a Binary field
| D | Field is a Date

Example:

| CST_ID:7 |
| OWING:8.2 | (8 digits with 2 decimal places)
| AMOUNT:P4.2 | (4 bytes containing BIN value scaled by 100)
| NAME:B30 | (30 byte binary field)

It is a good idea to include the field descriptions for all fields since this prevents PxPlus from having to read the table's data dictionary to determine field sizes and types.

Hex and Binary values can be used to store non-printable and/or binary data that would cause problems otherwise when passed in a SQL statement.

Binary fields (type P) can be used to define numeric data that has been packed into a string using the BIN( ) and DEC( ) functions. If specified, the scale indicates the number of implied decimal places that the value contains.

Literals may be inserted within the record layout to insert padding where a field or column is not presently used but space has been reserved for it. Literals should be enclosed with apostrophes and separated by a comma or plus sign.

## ODB/OCI Variant Record Processing

To emulate multi-record type files (variant records), the database record must contain all possible columns. If record type 1 consists of the fields Prefix and Value when Prefix="ABC", and record type 2 consists of the fields Prefix and Percentage when the second and third characters of Prefix="EF", the database record would contain three columns Prefix, Value and Percentage.

TYP= specifies the field(s) that determine the record type. Using a ? in the REC= clause defines the value. Special masking options for ? include:

| . | Any one character (i.e. wildcard character)
| [abc] | Any one of bracketed characters
| [0-9] | Any character from 0 to 9
| [ ] | Indicates end-of-field
| ^ | Indicates records that don't match

Example:

> TYP=Prefix;REC=?"ABC",Prefix,Value,?".EF",Prefix,Percentage

If the table contains two records:

> "ABC",9,0
> "AEF",0,99.99

Using the statement read(chan)A$,B:

> On the 1st READ, A$="ABC",B=9.
> On the 2nd READ, A$="AEF",B=99.99.

The following would insert a new record into the database consisting of "XEF",0,50.5:

> write (chan)"XEF",50.5