Special Command Tags
[OCI] Connect to Oracle Server
   
Format OPEN (chan[fileopt])"[OCI]sid[fileopt]"

Where:

[OCI] File tag clause to inform ProvideX 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 below)
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.
   
Description The [OCI] tag is used as a prefix in an OPEN statement to denote that ProvideX is to route all file I/O requests to an external (Windows, not ProvideX) 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 ProvideX OCI activation (available for Windows, Redhat, HP UX, Sun Solaris, and AIX). Refer to the ProvideX website for platform specifics.
  [OCI] OPT= Parameters

The OPEN options for connecting to an Oracle server are listed below:

"AUTO_INDEX=" Used to include hints and index numbers to SELECT statements
"DATEFMT=" Date format mask applying to all date fields in table. This can be a combination of Y M D with any other characters; e.g., to convert dates to 4-character year, month and day: DATEFMT=YYYYMMDD.Other characters are inserted as is; e.g., DATEFMT=YY/MM/DD with a date of March 1, 2004 would be returned as 04/03/01.
"DEBUGIT=" 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. For example, "--" is the comment identifier for Microsoft SQL Server; anything after "--" is ignored by SQL Server when compiling the SQL statement.
"EXEC_SPRNO=" Not applicable to Oracle at this time.
"EXTROPT=" Controls the format of the SELECT statement used to process an EXTRACT. By default, PVX generates a SELECT*FROMtable 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=" Identifies a column that contains a sequential number starting at 0. This is used to emulate an indexed file.
"IOPROG=" Emulates the embedded I/O program logic available with a true ProvideX file.
"KEY=" Identifies fields that make up the key(s). For named keys enter *NAME:keyname.

For Example: OPEN(chan)"[OCI]sid;table;KEY=field,field,*NAME:keyname"

"KEYDATA=" 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.
"NOSTRIP" Keeps trailing spaces (Default).
"NULLPADKEY=" Set to 1, Y or y to force keys to be padded to full length with the null character, $00$.
"ORACLE=" 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=" Set to 1, Y or y to use prepared statements. Prepared statements are pre-compiled SQL that may improve performance. Default is N.
"PSWD=" Specifies password
"REC=" Provides the column names, type, and size. This is typically done to improve performance. If this information is not provided, then ProvideX must query the database for this information. For more information see ODB/OCI Variant Record Processing.
"RECDATA=" 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
"TOP=" 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 (TOPnot supported).
"TSQL=" Not applicable to Oracle at this time.
"TYP=" Sets identifier for different variant records. For more information see ODB/OCI Variant Record Processing.
"UNIQUE=" 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=" Specifies login name.
   
See Also READ Directive,
READ RECORD Directive,
SELECT Directive,
WRITE Directive,
WRITE RECORD Directive
OPEN Directive
   
ODB/OCI Variant Record Processing In order to emulate multi-record type files (variant records) the database record must contain all possible columns; i.e., 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 2nd and 3rd 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’ 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.
WRITE(chan)"XEF",50.5 would insert a new record into the database consisting of "XEF",0,50.5.