ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native Oracle Connector
  • Overview
  • Main remarks
  • Required versions
  • Unicode on Oracle
  • The lock of a data file (table) is performed in a transaction
  • Managing the automatic identifier in write mode: using an Oracle sequence
  • Format of dates
  • Record number
  • List of tables accessed by Native Oracle Connector according to the SYS diagram
  • Managing synonyms
  • Default values of items
  • Managing the calculated items
  • Main specific features
  • Analysis options
  • Empty strings and NULL value
  • Array items
  • Referential integrity constraints
  • Locking records
  • Using a query (with the hQueryWithoutCorrection constant) with HAdd, HModify or HDelete
  • Specific features regarding HFSQL and SQL functions
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Native Oracle Connector: Specific features and remarks
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
The following paragraphs present the main aspects and specific features of the Native Oracle Connector.
Main remarks

Required versions

Caution: This version of Native Oracle Connector operates with WINDEV 27 and WEBDEV 27.

Unicode on Oracle

  • To manage Unicode on Oracle, the client layer version 9i (or later) must be installed.
  • If the following conditions are fulfilled, the unicode memo is truncated to 4000 characters:
    • Using the selection condition LIKE with the ESCAPE clause,
    • Unicode memo,
    • Server in version 9.

The lock of a data file (table) is performed in a transaction

HLockFile starts a transaction and locks the table in Exclusive mode. HUnlockFile validates the transaction and unlocks the table.

Managing the automatic identifier in write mode: using an Oracle sequence

The Automatic Identifier type is not available for Oracle: this type of item is not created when importing an Oracle table.
However, the "4-byte integer" item and/or "8-byte integer" item can be modified in the data model editor in order to be defined as automatic identifier. In this case, these automatic identifiers will be managed by the Native Oracle connector (in addition or in modification) via an Oracle "Sequence" named "WINDEV_SEQ". This sequence is automatically created by Native Oracle Connector if it does not exist.
To add or modify records with automatic identifier, you must:
  • modify the analysis. Indeed, a "4-byte integer" item or a "8-byte integer" item can be defined as "Automatic identifier". In this case, this item will be managed like an automatic identifier by Native Oracle Connector.
  • create the "sequence" object named "WINDEV_SEQ" in the Oracle database.
  • grant the "CREATE SEQUENCE" privilege to the user.

Format of dates

When inserting or updating a date in a query run by SQLExec, the default format for Native Oracle Connector is as follows: "YYYYMMDDHHmmSS".
In WINDEV 5.5, the format of the dates depended on the parameters specified during the connection (default configuration settings for the Oracle client layer).
In WINDEV, to modify the default format of the dates, display the description window of the connection used and select "Default connection format" in the "Date Format" combo box. A custom date format can also be specified in this combo box. In this case, this format must be compatible with insertion queries (INSERT statement) and modification queries (UPDATE statement).
Remark: Empty dates correspond to strings completed by 0.

Record number

You cannot specify a record number other than zero in HWrite and HRead. Indeed, "zero" corresponds to the current record number.
Similarly, HLockRecNum and HUnlockRecNum can only be used on the current record.

List of tables accessed by Native Oracle Connector according to the SYS diagram

The list of tables to which Native Oracle Connector tries to access according to the SYS diagram is as follows:
  • V$VERSION
  • ALL_OBJECTS
  • ALL_IND_COLUMNS
  • SYS.ALL_TABLES
  • SYS.ALL_VIEWS
  • Optional: SYS.ALL_SOURCE
By default, these tables are locked in read-only by Oracle. It may be required to allow SELECT on these tables.

Managing synonyms

The Native Oracle Connector manages the synonyms:
  • You have the ability to access a file via its name or via its synonym.
  • The synonyms are listed with the views by HListFile.
  • The synonyms are imported as file description into the analysis.
Caution: HCreation used on a file description found in the analysis always creates a table, regardless of its origin (table, view or synonym).

Default values of items

The Native Oracle Connector supports default item values. The default values can be:
  • a value,
  • an SQL formula. In this case, the SQL expression must be recognized by the HFSQL engine and by the Oracle database.

Managing the calculated items

Calculated items can be used in the data files.
The management of calculated items is available from Oracle version 11g (and later).
Main specific features

Analysis options

  • No matter whether the options ("Case sensitive", "Accent sensitive" and "Punctuation sensitive") are checked in the data model editor, the search will always be sensitive to the case, to the accented characters and to the punctuation.
  • The Native Oracle Connector does not support the full-text index options of the analysis.
  • The Native Oracle Connector does not support the options for partial indexing of text memos.

Empty strings and NULL value

The "Empty string" values are interpreted by Oracle as the NULL value. When performing an ascending browse, the ORDER BY command rejects all the NULL values at the end of the browse.

Array items

The Native Oracle Connector does not support Array items. If you are using an array item, only the value of subscript 1 in the array will be handled by the HFSQL functions (in read/write).
To manage this type of item, the structure of your data files must be modified in order to replace the subscripted items by several items.
Remark: In programming, you will have the ability to use the indirection to handle these items in a generic way.
The following code with a subscripted item:
nItemSubscript is int
FOR nItemSubscript = 1 TO 10
File.ArrayItem[nItemSubscript] = "Value"
END
Becomes with items whose name contains a number:
nItemSubscript is int
FOR nItemSubscript = 1 TO 10
{"File.ArrayItem" + nItemSubscript, indItem} = "Value"
END

Referential integrity constraints

Referential integrity constraints described in the analysis are not automatically created on the database with HCreation.
Referential integrity constraints must be described in the database accessed.

Locking records

The lock options (hLockWrite and hLockReadWrite constants) that can be used with the HFSQL functions have the same effect: lock in write mode. Oracle does not support read-only locks.

Using a query (with the hQueryWithoutCorrection constant) with HAdd, HModify or HDelete

If the hQueryWithoutCorrection constant is used in a query, you must start a transaction before using this query with HAdd, HModify or HDelete. This transaction is used to avoid the error "ORA2002: Extraction out of sequence" when browsing the query.
Example:
S1 is Data Source
SQLTransaction(sqlStart, MyConnection)
HExecuteSQLQuery(S1, MyConnection, hQueryWithoutCorrection, "select * from orders")
HReadFirst(S1)
WHILE NOT HOut(S1)
HReadSeekFirst(Customer, CUUniqueKey, S1.CUUniqueKey)
IF HFound(Customer) THEN
Customer.NbOrder = Order.NbOrder
HModify(Customer)
END
HReadNext(S1)
END
SQLTransaction(sqlCommit, MyConnection)
Specific features regarding HFSQL and SQL functions
The specific features for using the HFSQL functions with Native Oracle Connector are as follows:
Specific featuresAffected functions
The hAffectBrowse constant is ignored. At the end of the operation, the browse continues from the record that was the current record before the addition or the modification.HAdd, HModify
The hDistinct constant is ignored: all the records (including the duplicates) will be browsed.HForward, HBackward,
HReadPrevious, HReadNext
HPrevious, HNext
Multi-file query.
HAdd, HModify and HDelete are not supported.
HAdd,
HModify,
HDelete
HSeek*, HFirst and HNext are used to position on a record without reading it.
In this version, these functions also read the record. This problem will be fixed in a forthcoming version.
HSeek,
HFirst,
HNext
If a duplicate error occurs on HModify, HRead (with the hCurrentRecNum constant) does not read the requested record.
This problem will be fixed in a forthcoming version.
HRead
After HAdd, the record is not re-read by the Native Connector. If the values of the record are modified by the database (trigger on Insert, ...), HRead must be used to retrieve the modified values.HAdd
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 09/05/2023

Send a report | Local help