Oracle

learn how to connect engini to oracle using engini’s oracle activities, you can create individual records or batches of records delete records execute customized sql queries execute stored procedures retrieve records directly from your oracle erp to get started with the oracle app, create an account at https //www oracle com/cloud/free/ , and ensure you have access to oracle’s admin area or an active access token getting started with oracle prerequisites an active oracle account connecting engini to oracle enter your engini account at https //engini io/ navigate to connections page by clicking on the connections on the left sidebar or by clicking https //app engini io/connections click on the “new integration” option located at the topbar choose the oracle option from the available applications enter the following details in the “add connection” form and press save connection name enter a unique and descriptive name for this connection this name will help you easily identify and manage the oracle connection within your engini account host specify the host or address of your oracle database this is the server location where the oracle database is hosted port provide the port number used by your oracle database for communication by default, the port is set to 1521, but you can adjust it based on your setup service input the name of the oracle service to which you want to connect this field corresponds to the service provided by the oracle database username enter the username associated with your oracle database make sure this user has the appropriate permissions to access and interact with the database password provide the password for the specified username ensure the password is correct to establish a secure connection communication channel select the appropriate communication channel for this connection cloud if you are connecting to a database hosted in a cloud environment, select “cloud” opa if your database is on premises and you are using an on premises agent (opa) for the connection, select “opa” in this case, an additional field will appear on prem agent choose the specific on premises agent that you want to use for this connection if you have multiple agents configured save settings once all required fields are completed, click save to establish the connection after clicking “save”, a window will prompt you to select database objects from available tables/views/stored procedures you can select the databases you want to use by marking the corresponding checkboxes of the tables/view/stored procedures you intend to utilize subsequently, you can perform various activities within your workflow and access the data stored within these selected tables/views/stored procedures actions create record table click on the empty field to display a tooltip listing all available tables in your oracle database from this list, select the table where you want to create the new record ensure that the selected table matches the data structure for the record you intend to insert get records table/view in this field, select the table or view from which you want to retrieve records click on the empty field, and a tooltip will appear showing the available tables and views in your oracle database choose the appropriate one to proceed with the data retrieval top specify the number of rows to retrieve from the table or view if left blank, the default is set to retrieve 100 rows this field allows you to limit the number of rows returned by the query offset enter the number of rows to skip before starting to retrieve data by default, the offset is set to 0, meaning data retrieval will start from the first row unless you specify a different value add filter click this option to add conditions to filter the records that will be retrieved this allows you to refine the data extraction to only include records that meet specific criteria add sorting click this option to define how the retrieved records should be sorted you can sort the data by one or more columns in ascending or descending order based on your needs update record table select the table where the record you want to update resides click on the empty field to display a tooltip showing the available tables in your oracle database, and choose the appropriate table where the record will be updated update records table select the table where the records you want to update are located click on the empty field to display a list of available tables in your oracle database choose the table where the records will be updated add filter click this option to define filter criteria for selecting the records you wish to update filters help narrow down the records that will be modified, ensuring only the records meeting the specified conditions are updated delete records table click on the empty field to open a tooltip displaying all available tables in your oracle database from the list, select the table from which you wish to delete records ensure that the table selection corresponds to where the records you intend to delete are located add filter click “add filter” to specify criteria for which records should be deleted from the selected table filters allow you to define conditions to delete specific records, ensuring only the intended data is removed without a filter, all records in the table might be deleted create batch of records data list in this field, enter the list of data items you want to insert into the oracle table you can input up to 1000 records at a time, as the maximum batch size is 1000 items make sure the data format corresponds to the table structure where the records will be inserted table click on the empty field, and a tooltip will appear showing all the available tables in your oracle database you can then select the specific table where you wish to create the new batch of records get records batch data list in this field, input the list of data items to be retrieved in a batch the maximum batch size is 1000 items, which means you can retrieve up to 1000 records at a time ensure the data format matches the structure of the table you are working with table select the table from which the batch of records will be retrieved click on the empty field to display a tooltip listing all available tables in your oracle database, and choose the appropriate one top specify the number of rows to retrieve in the batch if left blank, the default is set to 100 rows you can adjust this number to retrieve more or fewer rows based on your requirements add filter click this option to apply filters to the batch of records being retrieved adding filters allows you to narrow down the results by specifying criteria that the records must meet to be included in the batch add sorting click this option to sort the retrieved batch of records sorting can be applied to one or more columns in either ascending or descending order, depending on your needs update batch of records data list in this field, input the list of data items to be updated in a batch the maximum batch size is 1000 items, meaning you can update up to 1000 records at once ensure the data matches the structure of the table where the records will be updated table select the table where the batch of records will be updated click on the empty field to display a tooltip listing all available tables in your oracle database, and choose the appropriate one where the updates will be applied add filter click this option to specify the filter criteria for the records you wish to update by adding filters, you can narrow down the selection and only update records that match the specified conditions, ensuring you modify the correct data execute customized sql sql in this field, enter your custom sql query to execute on the oracle database this allows you to run specific sql commands such as select, insert, update, delete, or any other custom query ensure that the sql query is correctly formatted and valid for the oracle database you are working with, as incorrect queries may result in errors or unintended changes execute procedure procedure name in this field, enter the name of the stored procedure you want to execute in your oracle database the procedure must already exist in the database, and it can perform various tasks such as data manipulation or calculations ensure that the procedure name is spelled correctly and that you provide any required parameters when executing the procedure after selecting a procedure from the procedure name field, the “add fields” option will appear add fields this option allows you to specify and provide input parameters required by the selected procedure depending on the procedure, it might need several inputs (fields) to be executed correctly by clicking “add fields,” a list of necessary fields or parameters will appear, where you can fill in the corresponding values this ensures the procedure runs with the correct inputs as defined in your oracle database