OracleSQL
- Daniel Lupascu (Unlicensed)
- wiki
- Former user (Deleted)
- Connect to OracleSQL
- Disconnect from OracleSQL
- Add User Access to Table
- Backup Database
- Delete Row(s) from Table
- Insert Row(s) into Table
- Remove User Access from Table
- Restore Database
- Run Query
- Select Row(s) from Table
- Export from Database
- Import into Database
NOTE: for the activities, ORACLE Database 11g Express Edition is used. By default, it creates a database called XE when installing, which will be used for all of the examples below.
Connect to OracleSQL
This activity connects to a OracleSQL Host. The Output of this activity should be used as input for all Connection parameters used for activities placed under OracleSQL category.
Activity Parameters
The Connect to OracleSQL activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Data Source (text box editor type) (type: string): the database name to connect to. In the screenshot above the default XE database is used on the specified server.
- Password (password editor type) (type: string): the password for the provided username.
- Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the Oracle server. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal).
- Port (text box editor type) (type: int32): the port number.
- Server Name (text box editor type) (type: string): the name or IP of the Oracle Server to connect to. If Oracle is installed on the same server where the activity is executed, then the field can remain blank. This field is recommended to be used when accessing an Oracle database created on a different server.
- Username (text box editor type) (type: string): the the username of the Oracle Server Administrator. It can be used any user created for the database (for example, the user(s) used when creating a Workspace in Oracle Application Express).
The Read Only Output variable are the possible output values that the activity will provide:
- Connection (type: object): outputs an OracleL connection. The Output of this activity should be used as input for all Connection parameters used for activities placed under OracleSQL category.
Disconnect from OracleSQL
This activity disconnects from a OracleSQL Host.
Activity Parameters
The Disconnect from OracleSQL activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Connection (text box editor type) (type: object): the output from a Connect to OracleSQL activity.
Add User Access to Table
This activity adds privileges for an user to a database table.
Activity Parameters
The Add User Access to Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Connection (text box editor type) (type: object): the connection object that was obtained as output from a Connect To OracleSQL activity.
- Table Name (text box editor type) (type: string): the database table that the rights will be granted on. This activity will grant permissions per table.
- UserNameForAccess (text box editor type) (type: string): the name of the user that will receive permissions.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
Execution: the activity will search for the user and for the table. If found, will attempt to grant permissions over the table to the provided user:
Backup Database
This activity creates an .dmp file backup for the selected database.
Activity Parameters
The Backup Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Backup Path (text box editor type) (type: string): the path where the backup file will be created. The path can be manually written or selected by clicking the
button. - BackupName (text box editor type) (type: string): the name of the backup file that will be created. Should be a .dmp extension file (E.G. XE.dmp, where XE is the name of the current database).
- exp.exe Path (text box editor type) (type: string): the the path of exp.exe (the Oracle export utility), or select it with the File Browser. By default, exp.exe is located in C:\oraclexe\app\oracle\product\11.2.0\server\bin\ folder, where C:\oraclexe\app\oracle\product\11.2.0\server\ is also the value for the ORACLE_HOME environment variable.
- Password (password editor type) (type: string): the password for the provided username. The password should be the same as the one given for the Connect to OracleSQL activity. The Backup Database activity creates a connection string based on the user provided at the connection activity and the password provided in the backup activity.
- Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the Oracle server. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal).
- SID (text box editor type) (type: string): the Oracle SID (Oracle System Identifier, which is used to distinguish Oracle database instances). By default it will be set to XE, which is also the Oracle's default database name.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: string): outputs the path of the backup file.
Execution: the activity will launch exp.exe with the given parameters and will create a .dmp file in the provided location:
The file can be seen in the specified location:
Delete Row(s) from Table
This activity deletes Row(s) from an Oracle Server database table.
Activity Parameters
The Delete Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Table (text box editor type) (type: string): the table name where the row(s) will be deleted.
- Where Statement (text box editor type) (type: string): the Where statement to filter the row(s) that will be deleted. For example: ID>2, where all rows with an ID higher than 2 will be deleted.
Execution: the activity will delete all the rows from a table according to the given Where statement:
- the initial table content:
- the table content after deletion:
Insert Row(s) into Table
This activity inserts Row(s) on an Oracle Server database table.
Activity Parameters
The Insert Row(s) into Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. For example: ID,Name,Surname,Company.
- Table (text box editor type) (type: string): the table name where the row(s) will be inserted.
- Values (text box editor type) (type: string): the values for specified fields in Fields parameter. If you enter more than one you should separate them with ','. If you have more values each should be placed in the same order as you add the fields.For example: 3,May,James,Amazon Prime.
The Read Only Output variable are the possible output values that the activity will provide:
- Output Last ID (type: string): outputs the last row inserted ID.
Execution: the activity will insert the new row into the selected table. In the database, the row can be seen with the ID 3:
Remove User Access from Table
This activity removes privileges for an user to a database table.
Activity Parameters
The Remove User Access from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Table Name (text box editor type) (type: string): the database table where the rights will be removed from.
- Username to be Removed (text box editor type) (type: string): the name of the user that will have denied permissions.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
Restore Database
This activity restores a Database from a backup .dmp file.
Activity Parameters
The Restore Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Backup File Path (text box editor type) (type: string): the path of the backup file or select it with the File Browser. The .dmp file path can be manually written or selected by clicking the
button. In this example the backup file has the same name as the database's. - imp.exe Path (text box editor type) (type: string): the the path of imp.exe (the Oracle import utility), or select it with the File Browser. By default, imp.exe is located in C:\oraclexe\app\oracle\product\11.2.0\server\bin\ folder, where C:\oraclexe\app\oracle\product\11.2.0\server\ is also the value for the ORACLE_HOME environment variable.
- Password (password editor type) (type: string): the password for the provided username. The password should be the same as the one given for the Connect to OracleSQL activity. The Restore Database activity creates a connection string based on the user provided at the connection activity and the password provided in the restore activity.
- Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the Oracle server. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal).
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
Run query
This activity runs a query on an Oracle Server database.
Activity Parameters
The Run Query activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Query (text box editor type) (type: string): the SQL query string. For example: "SELECT Name, Company FROM FLOWSTERNAMESs where ID>1 and ID<4 order by name ASC"
- Script (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: collection<collection<string>>): outputs a collection of returned rows.To use the elements of the collection, use ForEachFactory activity.
- Output Columns (type: int32): outputs the number of columns returned.
- Output Rows (type: int32): outputs the number of rows returned.
Execution: In the Tracking Data there can be seen the returned results:
The same result can be seen when executing the same SQL Select query from Oracle Application Express:
Select Row(s) from Table
This activity selects and returns Row(s) from an Oracle Server database table.
Activity Parameters
The Select Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Distinct (combo box control) (type: string): whether the returned values should be distinct or not.
- Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. If all fields needs to be returned, then just use "*". The " * " has the same role as used in the SQL select all statement: select * from table
- Order By (text box editor type) (type: string): the column header that you want to result to be ordered by. For example, if "ID" is inserted, then the values returned will be ordered depending on the value set in the ID column. If no value is provided, the activity will return the values in the same order as taken from the table.
- Order by Direction (combo box control) (type: string): choose to order the resulted rows in Ascending or Descending direction. It will be ignored if Order By is not set.
- Table (text box editor type) (type: string): the table name on which to run the select.
- WHERE (text box editor type) (type: string): the Where statement. For example: ID>2, where ID is a column header.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: collection<collection<string>>): outputs a collection of collections of strings containing the selected rows.To use the elements of the collection, use Get Element From Collection activity.
- Output Columns (type: int32): outputs the number of columns returned.
- Output Rows (type: int32): outputs the number of rows returned.
A more complex example, with fields, orders and statements can be seen in the image below:
Execution: In the Tracking Data there can be seen the returned results:
The same result can be seen when executing the same SQL Select query from Oracle Application Express:
Export from Database
This activity exports data from a Database into an XML or a CSV file.
Activity Parameters
The Export from Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- Destination Path (combo box control) (type: string): the path where the export (csv or xml) file will be created.
- File Name (text box editor type) (type: string): the name of the XML file or CSV file that will be created. E.g. 'XE_ExportFile'.
- File Type (combo box control) (type: string): the type of the file that will be created. For each of them a different set of parameters will be displayed.
- Overwrite (combo box control) (type: string): whether to overwrite the file if it already exists.
- Root Name (text box editor type) (type: string): the name of the root for the .xml file. This field should be completed only when XML is selected in the File Type field. As syntax, for example, xml_root can be used.
- Table (text box editor type) (type: string): the table from where the export will be made.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
Execution: the activity will create an export file of the table settled in the Table field, in the specified folder:
Import into Database
This activity imports data into a Database from an XML or a CSV file.
Activity Parameters
The Import into Database activity parameters can be provided by manual inserted values or via IN arguments or variables:
Parameters:
- File Type (combo box control) (type: string): the type of the file that will be imported from. Flowster Studio provides by default CSV and XML types.
- First Row Header (combo box control) (type: string): whether the CSV file includes the header as a first row or not. If, for example, it is used a CSV file obtained via the Export From Database activity, then leave True for this parameter (the Export From Database activity also exports the column names on the first row of the file).
- Import File Path (text box editor type) (type: string): the path where the file will be selected from, or choose it with the file browser, by clicking the
button. - Table (text box editor type) (type: string): the table name where the file will be imported. If left empty, it will take the 'Name' of the CSV File or the 'Root Name' from the XML File.
The Read Only Output variable are the possible output values that the activity will provide:
- Output (type: boolean): outputs True or False if the execution was successful or not.
Execution: the activity will import the lines found in the given file:
- the initial table content:
- the table content after import:
