Oracle
Oracle
This plugin is designed to execute various operations on Oracle database.
This plugin has been tested with Oracle 12c on Windows OS.
Prerequisites:
- Oracle 12c environment on Windows.
- Sufficient privileges for operation to be done (preferably sysbda privileges/Dba user).
- The pluggable database (PDB) should be open before performing any action on PDB.
- ojdbc6.jar must be present in lib folder of Process Studio as well as AE agent.
- For connecting to the Oracle Server, OracleOraDB12homeTNSListener & OracleServiceSID should be started
Create User
Description
This plugin is used to create a new oracle user.
Prerequisites:
- Oracle 11g/12c environment on Windows
- Oracle 12c environment on Linux.
- Sufficient privileges to create the tablespace(preferably sysbda privileges).
- ojdbc6.jar must be present in lib folder of Process Studio as well as AE agent.
- While testing on Oracle 12c Windows/Linux the pluggable database should be open before performing any action.
- For connecting to the Oracle Server, OracleOraDB12homeTNSListener & OracleServiceORCL should be started
Limitations:
On Oracle 12C database the plugin works for only CDB (Container Database). It does not work for PDB (Pluggable Database).
Configurations
No.
Field Name
Description
1
Step Name
Name of the step. This name has to be unique in a single workflow.
Connection:
2
Server Name/IP
The IP or fully qualified domain name of the Oracle database machine.
This field is mandatory.
3
Port Number
The port number of the Oracle database. Port number should be a positive integer. This field is mandatory.
4
SID/ Service name
Unique database name or service name. This field is mandatory.
5
Username
The username having permission to create new User (Ex: - sysdba). This field is mandatory.
Password:
6
Accept Values from fields
Enable this checkbox to accept Password value from a field in the previous steps of the stream using a drop down list.
7
Password
The password for the Oracle username to authenticate. This field is mandatory.
Password is entered using a widget called SecuredComboVar. The widget handles both Text Var(static value or environment variable) and Combo(drop down containing values from previous steps). This field is mandatory.
If checkbox above is enabled Password field appears as Text Var. Else if checkbox above is disabled Password field appears as a drop down to select fields from previous steps.
Button:
8
Test Connection
Verifies whether the connection is established or not.
NOTE: Test Connection button does not work for previous step variables. Test Connection button only works with static values and environment variables.
Input Tab:
No.
Field Name
Description
Input Fields:
1
New User
Username which is to be created. This field is mandatory.
2
New Password
Password for the new user to be created. This field is mandatory.
Advanced Input Tab:
No.
Field Name
Description
Advanced Input Fields:
1
Default Table space
Name of the Default table space.
If quota (K/M) is set then Default Tablespace is mandatory.
2
Temporary Table space
Name of the Temporary table space.
3
Quota Size
Provide a Quota size. Quota should be a positive integer. This field is mandatory.
4
Size Unit(K/M)
Provide a size unit for the quota value provided above. It can be K or M. This is visible in a tool tip as well. This field is mandatory.
NOTE: The create user plugin executes successfully, for any combination of Temporary Tablespabe, Default tablespace and Quota fields” except when,
- Quota is provided and Default tablespace is missing.
- A popup window with error message “Invalid combination of Temporary Tablespabe, Default tablespace and Quota fields” is shown.
Output Tab:
No.
Field Name
Description
Output Fields:
1
Result
Output field to hold the result of the successful plugin execution
Delete User
Description
The step is used to delete oracle user specified.
Limitations:
On Oracle 12C database the plugin works for only CDB (Container Database). It does not work for PDB (Pluggable Database).
Configurations
No.
Field Name
Description
1
Step Name
Name of the step. This name has to be unique in a single workflow.
Connection:
2
Server Name/IP
The Hostname/serve name/IP of the Oracle database.
This field is mandatory.
3
Port Number
The port number of the Oracle database. Port number should be a positive integer. This field is mandatory.
4
SID/ Service name
Unique database name or service name.
5
Username
The username having permission to Delete User (Ex:- sysdba). This field is mandatory.
Password:
6
Accept Values from fields
Enable this checkbox to accept Password value from a field in the previous steps of the stream using a drop down list.
7
Password
The password for the Oracle username to authenticate. This field is mandatory.
Password is entered using a widget called SecuredComboVar. The widget handles both Text Var(static value or environment variable) and Combo(drop down containing values from previous steps). This field is mandatory.
If checkbox above is enabled Password field appears as Text Var. Else if checkbox above is disabled Password field appears as a drop down to select fields from previous steps.
Button:
8
Test Connection
Verifies whether the connection is established or not.
NOTE: Test Connection button does not work for previous step variables. Test Connection button only works with static values and environment variables.
Input Tab:
No.
Field Name
Description
Input Fields:
1
Username to delete
Username which is to be deleted from the database. This field is mandatory.
Output Tab:
No.
Field Name
Description
Output Fields:
1
Result
Output field to hold the result of the successful plugin execution
Create Tablespace
Description
This plugin is designed to create a Tablespace into Oracle Database installed on Windows Server and Linux Server OS. The Tablespace is created based on the below input criteria provided by the user.
- SID/Service name of the Oracle Database.
- Type of Tablespace.
- Name of Tablespace.
- Name and Size of Datafile.
Prerequisites
- Oracle 11g/12c environment on Windows
- Oracle 12c environment on Linux.
- Sufficient privileges to create the tablespace(preferably sysbda privileges).
- ojdbc6.jar must be present in lib folder of Process Studio as well as AE agent.
- While testing on Oracle 12c Windows/Linux the pluggable database should be open before performing any action.
- For connecting to the Oracle Server, OracleOraDB12homeTNSListener & OracleServiceORCL should be started
Configurations
No.
Field Name
Description
1
Step Name
Name of the step. This name has to be unique in a single workflow.
Connection:
2
Server Name/IP
The IP or fully qualified domain name of the Oracle database machine. This field is mandatory.
3
Port Number
The port number of the Oracle database. Port number should only be a positive integer. This field is mandatory.
4
SID/ Service name
The SID/Service name of the Oracle database that uniquely identifies the database instance. This field is mandatory.
5
Username
The username of user having permission to create new Tablespace (Ex:- sysdba). This field is mandatory.
Password:
6
Accept Values from fields
Enable this checkbox to accept Password value from a field in the previous steps of the stream using a drop down list.
7
Password
The password for the Oracle username to authenticate. This field is mandatory.
Password is entered using a widget called SecuredComboVar. The widget handles both Text Var(static value or environment variable) and Combo(drop down containing values from previous steps). This field is mandatory.
If checkbox above is enabled Password field appears as Text Var. Else if checkbox above is disabled Password field appears as a drop down to select fields from previous steps.
Button:
8
Test Connection
Verifies whether the connection is established or not.
NOTE: Test Connection button does not work for previous step variables. Test Connection button only works with static values and environment variables.
Input Tab:
No.
Field Name
Description
Input Fields:
All input fields are mandatory.
1
Tablespace Type
The Type of Tablespace can be selected from options: Permanent/Temporary/Undo. This is visible in a tooltip.
2
Tablespace Name
The unique name of the tablespace to be created. Datafile Size and AutoExtend are mandatory.
3
Datafile Name
The unique datafile name.
It is not required to mention the extensions of datafile in the plugin. It will be handled internally depend upon the type of tablespaces. Datafile size should not be Lesser than or equal to Zero. This field is mandatory.
4
Datafile size
The size of datafile greater than Zero (0) and should be entered.
This field is mandatory.
Size Unit(K/M/G/T)
A size unit such K, M, G or T should be provided in the Size Unit field for the Datafile size chosen above. This is visible in a tool tip as well. This field is mandatory.
5
AutoExtend
Automatically extends the size of datafile if set to ‘ON’. It should be entered as On or Off.
This is visible in a tool tip. This field is mandatory.
6
Tablespace status
The status of Tablespace can be ‘Online/Offline’
The default value is “Online”.
This is visible in a tool tip. It should be entered as Online or Offline.
Output Tab:
No.
Field Name
Description
1
Output Fields:
2
Result
Output field to hold the result returned from oracle.
Common Buttons:
No.
Field Name
Description
Buttons:
1
OK
On click of this button. It will check the field values. If any required field values are missing then it will display validation error message.
If all the required field values are provided then it will save the field values.
2
Cancel
On click of this button, it will cancel the window and do not save any values
Extend Tablespace
Description
This plugin is designed to extend a Tablespace by adding datafile or tempfile into Oracle Database located on Windows Server. The Tablespace is created based on the below input criteria provided by the user.
- SID/Service name of the Oracle Database.
- Name of Tablespace.
- Name and Size of Datafile.
- AutoExtend
Prerequisites:
- Oracle 11g/12c environment on Windows
- Oracle 12c environment on Linux.
- Sufficient privileges to create the tablespace(preferably sysbda privileges).
- ojdbc6.jar must be present in lib folder of Process Studio as well as AE agent.
- While testing on Oracle 12c Windows/Linux the pluggable database should be open before performing any action.
- For connecting to the Oracle Server, OracleOraDB12homeTNSListener & OracleServiceORCL should be started
Configurations
No.
Field Name
Description
1
Step Name
Name of the step. This name has to be unique in a single workflow.
Connection:
Server Name/IP
The server name/IP of the Oracle database.
Server Name is mandatory.
3
Port Number
The port number of the Oracle database. Port number should only be positive integer. Port Number is mandatory.
4
SID/ Service name
The SID of the Oracle database that uniquely identifies the database instance. SID is mandatory.
5
Username
The username having permission to create new Tablespace (Ex:- sysdba). Username is mandatory.
Password:
6
Accept Values from fields
Enable this checkbox to accept Password value from a field in the previous steps of the stream using a drop down list.
7
Password
The password for the Oracle username to authenticate. This field is mandatory.
Password is entered using a widget called SecuredComboVar. The widget handles both Text Var(static value or environment variable) and Combo(drop down containing values from previous steps). This field is mandatory.
If checkbox above is enabled Password field appears as Text Var. Else if checkbox above is disabled Password field appears as a drop down to select fields from previous steps.
Button:
8
Test Connection
Verifies whether the connection is established or not.
NOTE: Test Connection button does not work for previous step variables. Test Connection button only works with static values and environment variables.
Input Tab:
No.
Field Name
Description
Input Fields:
1
Tablespace Name
The unique datafile name. Tablespace Name is mandatory.
2
Datafile Name
The size of datafile greater than Zero (0M). It is not required to mention the extensions of datafile in the plugin. It will be handled internally depend upon the type of tablespaces. Datafile size should not be Lesser than or equal to Zero.
Datafile Name is mandatory.
3
Datafile Size
Size of datafile automatically extends if Autoextend for datafile if set to ‘ON’ in the database.
In this plugin Datafile size should not be Lesser than or equal to Zero. The size of datafile should be greater than Zero (0). Datafile Size is mandatory.
4
Size Unit(K/M/G/T)
A size unit such K, M, G or T should be provided in the Size Unit field for the Datafile size chosen above.
This is visible in a tool tip as well. This field is mandatory.
5
AutoExtend
Automatically extends the size of datafile if set to ‘ON’. It should be entered as On or Off.
This is visible in a tool tip. AutoExtend is mandatory.
Output Tab:
No.
Field Name
Description
Output Fields:
1
Result
Output field to hold the result of the successful plugin execution
Common Buttons:
No.
Field Name
Description
Buttons:
1
OK
On click of this button. It will check the field values. If any required field values are missing then it will display validation error message.
If all the required field values are provided then it will save the field values.
2
Cancel
On click of this button, it will cancel the window and do not save any values
Reset User Password
Description
This step resets the password of Oracle user on the given Oracle database.
Limitations:
On Oracle 12C database the plugin works for only CDB (Container Database) and not for PDB (Pluggable Database).
Configurations
No.
Field Name
Description
1
Step Name
Name of the step. This name has to be unique in a single workflow.
Connection:
2
Server Name/IP
The Hostname/serve name/IP of the Oracle database.
This field is mandatory.
3
Port Number
The port number of the Oracle database. Port number should be a positive integer. This field is mandatory.
4
SID/ Service name
Unique database name or service name.
5
Username
The username having permission to Delete User (Ex:- sysdba). This field is mandatory.
Password:
6
Checkbox: Accept Value as variable/static
Leave checkbox unchecked to accept Password value from a field in the previous steps of the stream using a drop down list.
Else enable checkbox for Password field to appear as Text box.
7
Password
The Password to the username to authenticate to Oracle database. Password is entered using a widget. The widget handles both Text Var(static value or environment variable) and Combo(drop down containing values from previous steps). This field is mandatory.
If checkbox above is enabled Password field appears as Text box. Else if checkbox above is disabled Password field appears as a drop down to select fields from previous steps.
Button:
8
Test Connection
Verifies whether the connection is established or not.
NOTE: Test Connection button does not work for previous step variables. Test Connection button only works with static values and environment variables.
Input Tab:
No.
Field Name
Description
Input Fields:
1
Username for password reset
Username to reset password.
2
New Password
Provide the new password to be set.
Output Tab:
No.
Field Name
Description
Output Fields:
1
Result
Output field to hold the result of the successful plugin execution. Refer Appendix 1: Error Handling in IT Plugins