ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native Oracle Connector
  • Overview
  • Main remarks
  • Required versions
  • Unicode on Oracle
  • Locking data files (tables) in a transaction
  • Managing the automatic identifier in write mode: using an Oracle sequence
  • Date format
  • Record number
  • List of tables accessed by the Native Oracle Connector according to the SYS schema
  • Managing synonyms
  • Default values of items
  • Handling 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 the Native Oracle Connector is only supported in WINDEV and WEBDEV 2024.

Unicode on Oracle

  • To manage Unicode on Oracle, the client library 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.

Locking data files (tables) 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 ID type is not available in Oracle. This type of item is not created when importing an Oracle table.
However, the "4-byte integer" and "8-byte integer" items can be modified in the data model editor in order to be defined as automatic identifiers. In this case, these automatic identifiers will be managed by the Native Oracle Connector (when adding or updating records) via an Oracle "Sequence" named "WINDEV_SEQ". This sequence is automatically created by the Native Oracle Connector if it does not exist.
To add or modify records with automatic identifiers, you must:
  • modify the analysis. "4-byte integer" and "8-byte integer" items can be defined as "Automatic identifiers". In this case, the Native Oracle Connector will manage these items as automatic identifiers.
  • create a "sequence" named "WINDEV_SEQ" in the Oracle database.
  • grant the "CREATE SEQUENCE" privilege to the user.

Date format

When inserting or updating a date in a query run by SQLExec, the default format of the Native Oracle Connector is: "YYYYMMDDHHmmSS".
In WINDEV 5.5, the date format depended on the parameters specified during the connection (default settings of the Oracle client library).
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. You can also specify a custom date format. In this case, this format must be compatible with INSERT and UPDATE statements.
Remark: Empty dates correspond to strings completed by zeros.

Record number

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

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

The Native Oracle Connector tries to access the following tables according to the SYS schema:
  • V$VERSION
  • ALL_OBJECTS
  • ALL_IND_COLUMNS
  • SYS.ALL_TABLES
  • SYS.ALL_VIEWS
  • Optional: SYS.ALL_SOURCE
By default, Oracle locks these tables in read-only mode. It may be necessary to grant permission to perform SELECT statements on these tables.

Managing synonyms

The Native Oracle Connector manages synonyms:
  • You can access a file using its name or synonym.
  • HListFile lists synonyms with the views.
  • Synonyms are imported as file descriptions 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.

Handling 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

  • Whether the "Case sensitive", "Accent sensitive", "Punctuation sensitive" options are checked or not in the data model editor, the search will always be case, accentuation and punctuation sensitive.
  • 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 index 1 in the array will be handled by HFSQL functions (read/write).
To manage this type of item, the structure of your data files must be modified in order to replace the indexed 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 an indexed item:
nItemIndex is int
FOR nItemIndex = 1 TO 10
Queue.ArrayItem[nItemIndex] = "Value"
END
Becomes with items whose name contains a number:
nItemIndex is int
FOR nItemIndex = 1 TO 10
{"File.ArrayItem" + nItemIndex, 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 database modifies the values of the record, use HRead to retrieve the new 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