Icon: 
Function: upsertOCI
Property window:
Short description:
Insert/update/Delete some rows inside an Oracle database using the OCI drivers.
Long Description:
Please refer to the section 5.2.26. for more informations about the usage of the OCI drivers to connect Anatella to Oracle databases.
Before using this action, you first need to download the additional Oracle OCI components (it’s a 196MB download): i.e. Click the blue URL (parameter P22) to automatically download and install the Oracle OCI components.
The parameter P1 (named "Connection String") can be a reference to the file "TNSNAMES.ORA", or it can be directly the contents of the file "TNSNAMES.ORA". For example, a "Connection String" such as this one is perfectly valid:
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.1) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE))) |
The parameter P13 represents the number of rows that are injected "in one block" to the database. A higher value is better because it sends larger "blocks of rows" and thus minimizes round trips of network data transfers (and thus the data injection runs faster). Do not put a value too high neither, otherwise the Oracle drivers may crash (and it also consumes a little more RAM).
If you get an undescribed error with very little information about why there is an error, set the parameter P13 to “1” and run again the 
upsertOCI Action. You should now have a more detailed explanation of the error. Once the error is fixed, don’t forget to put back the parameter P13 to “1000” to have a high processing speed.
If you get an error while executing the 
upsertOCI Action that looks like that:
FAILED: OCIEnvCreate()
OCI FAILED: Cannot execute the SQL code(1): □ □
…this means that you used the 
readODBC action or the 
upsertODBC action to access Oracle. As soon as you use an ODBC-based connection to access data inside Oracle, all OCI-based connections stop working (and the other way around is also true). You cannot mix the two types of Oracle connections (ODBC and OCI) inside the same graph.
The solution is: To regain access to Oracle, close the Anatella windows (click the 
button in the top-right corner) and re-open Anatella. Once Anatella is re-open you can re-start using OCI again.
Regarding the parameter P9 and the way Oracle handles Dates
A time-related column inside a table can contain:
•Some Time (hours+minutes+seconds)
•Some Date (year+month+dayOfMonth)
•Some Time Stamp (year+month+dayOfMonth + hours+minutes+seconds)
Inside Oracle SQL, when you create a new table, you can define a time-related column …
•..as the SQL type “DATE” or
•..as the SQL type “TIMESTAMP”.
Oracle does NOT enforce any type of content inside a SQL “DATE”-type column or inside a SQL “TIMESTAMP”-type column. This means that, inside these columns, you can find any kind of content: you’ll find randomly “Time”, “Date” and “Time Stamp” informations.
Despite this terrible decision from Oracle, you can still decide on your own to declare your column’s SQL types in a consistant way. If you made sure to declare a column with..
•…the SQL “DATE” type when it actually really contains some actual Dates or Times.
•..the SQL “TIMESTAMP” type when it actually really contains some actual Time Stamps.
..then you can use the parameters P9 to P12 to easily inject all your time-related data inside Oracle. Here are more details on how to use these parameters P9 to P12: When Anatella sends a new data row to Oracle, Anatella looks at the SQL type of the columns inside Oracle:
•if the SQL-type of the column X is “DATE”, this means that the columns X is more susceptible to contain either a Date (year+month+dayOfMonth) or a Time (hours+minutes+seconds).
To decide between the two contents (Date or Time), Anatella uses the parameter P11:
oif the column X is not inside the list given inside parameter P11, then the column X is a simple Date and its format is specified inside the parameter P9.
oif the column X is inside the list given inside parameter P11, then the column X is a Time and its format is specified inside the parameter P12.
•If the SQL-type of the column X is “TIMESTAMP”, this means that the columns X is more susceptible to contain a Time Stamp (year+month+dayOfMonth + hours+minutes+seconds) and its format is specified inside the parameter P10.
The syntax used inside the parameters P9, P10, P12 (to describe the time-format) originates from Oracle. It is explained on this page: https://www.techonthenet.com/oracle/functions/to_date.php
The default setting for the Parameter P9 is “YYYYMMDD”.
If the dataset to inject into Oracle comes from the 
OracleOCI action (with all the default settings) then, you should change the Parameter P9 to “YYYYMMDD HH24:MI:SS”. If you forgot to do that change, you will get an error from Oracle telling your that your date-formatting is erroneous.
If the dataset to inject into Oracle comes from the 
OracleOCI action AND, inside the 
OracleOCI action, you also changed the parameter “Extract DATE column as” to the value “YYYYMMDD” (which is the recommended setting if you decide to be consistent in the declaration of your SQL columns), then you can keep the default value for the Parameter P9 (that is “YYYYMMDD”) inside the 
upsertOCI Action.
To summarize:
* if you are consistent in the declaration of your SQL columns, set the parameter “Extract DATE column as” to the value “YYYYMMDD” inside the 
OracleOCI action.
* if you are inconsistent in the declaration of your SQL columns, set the parameter Parameter P9 to “YYYYMMDD HH24:MI:SS” inside the 
upsertOCI action.