To connect to your Oracle database you will first need to install the Oracle Connector from the forge and then add it as a dependency in your project. Once that is done, the Oracle Connector is very easy to use. Please follow the steps below
You will need to gather the information necessary to connect to your database. This information could consist of the database connection name that was created in Service Center, or the host/user/password information required to connect to the database. If you choose to enter the connection information in to Service Center please remember to apply it to the factory before attempting to use it with the Oracle Connector component.
Here is an example connection configuration in Service Center:
Once a connection is available we can set our project up with the structures and variables that will be required for proper operation. In order to perform this task we will need to know what input parameters are required by our procedure and in what format the result(s) will be delivered.
For this example we will be using the following procedure and table definitions.
create or replace PROCEDURE RETURNEMPLOYEESCONTAININGSTRING
(
SEARCHSTRING IN VARCHAR2 ,
OUTCURSOR IN OUT SYS_REFCURSOR
) AS
BEGIN
open OUTCURSOR FOR
Select * from employees
where first_name like '%' || SEARCHSTRING || '%'
or last_name like '%' || SEARCHSTRING || '%';
END RETURNEMPLOYEESCONTAININGSTRING;
CREATE TABLE employees
employee_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 108
PRIMARY KEY,
first_name VARCHAR( 255 ) NOT NULL,
last_name VARCHAR( 255 ) NOT NULL,
email VARCHAR( 255 ) NOT NULL,
phone VARCHAR( 50 ) NOT NULL ,
hire_date DATE NOT NULL ,
manager_id NUMBER( 12, 0 ) ,
job_title VARCHAR( 255 ) NOT NULL,
CONSTRAINT fk_employees_manager
FOREIGN KEY( manager_id )
REFERENCES employees( employee_id )
ON DELETE CASCADE
);
Based on this procedure we can define a structure to hold the input parameter(s). In this case we will need a structure that contains one attribute used to hold the SEARCHSTRING input parameter. We can make this in Service Studio and it will look like this.
Based on the table definition we know that we will need to create a Structure in Service Studio that includes attributes for each of the defined fields. Remember to map every field of the response to the expected data type of the response, for example, VARCHAR to Text, Number to Decimal and others. Please see this article for more information Mapping types from an external database to OutSystems). That structure should look similar to this (all attributes have a data type of "Text" other than HIRE_DATE, which is of type "Date Time") :
For the Input Parameter Local Variable, which in this example is named "InputParameter", we need the Data Type defined as Structure_Made_To_Hold_Your_Input_Parameter Record. In this example the structure is named "EmployeeSearchInputParameters" so our Data Type for the "InputParameter" Local Variable must be set to "EmployeeSearchInputParameters Record", as seen here:
The stored procedure could be executed in a number of ways. For our example I right-clicked on my screen and chose to "Fetch Data from Other Sources", which created a new Data Action, that I named "GetEmployeesMatchingSearch"
The Local Variable will be used to temporarily hold the result(s) that were retrieved and the Output Parameter will be used to provide access to the result(s) outside of this Data Action.
Since the Output Parameter and Local Variable for the Data Action will be managing the same data, the result(s) returned by the procedure, they will both have the same Data Type. Since these objects will be used to hold the result set, which in this case is a "List" of "Records" of our return structure type, we need the Data Type for both of these objects defined as Structure_Made_To_Hold_Your_Result Record List. In our example, since our output structure is of type "Employees", this would lead to a Data Type of "Employees Record List", as seen below:
Drag the desired function onto the design surface in Service Studio from the OracleConnector extension. In our example we will be using the ExecuteSP function.
Once the ExecuteSP component is dragged into your flow, select it. The properties can then be configured as shown below:
When the stored procedure returns the result(s), the Local Variable will be filled by the Oracle Connector with the result(s) of the executed procedure. This can be seen in the value of the property named "OutputList" when the "ExecuteSP" component is selected. An example of this can be seen in the image above with the Oracle Connector properties. Notice that the "OutputList" property is set to the Local Variable we created called "ResultTemp".
We now need to get the result(s) to be available outside of this Data Action. For this we will use the Output Parameter, named "Output", that we created earlier. The Output Parameter will need to have the returned result(s) assigned to it so it can be used to provide access to the returned result(s) outside of this action. This is done through an "Assign" statement as seen below:
Now that we have configured the Oracle Connector ExecuteSP component, we are ready to use our data. In this example I dragged the "Output" Output Parameter found in the "GetEmployeesMatchingSearch" Data Action into the "Main Content" area of the screen. This created a table automatically that will display my results.
Now it is time to run your application, trigger the execution of your stored procedure, and verify your results. After entering a search term of "iz" and pressing the "Search" button, I am presented with the expected result set.