This is a helpful step by step guide of How to configure and create an Oracle Service Instance. It can be a bit tricky to setup but after this guide you should be able to create SmartObjects on tables views and stored procedures.
Prerequisites:
- Download the Oracle Express Edition 11g R2 and install. This will contain, among other things, the OracleService instance and the OracleListener, which are useful for refreshing the db and refreshing the listener after making modifications in tnsnames.ora, respectively.
- Download the ODAC(Oracle Data Access Component) 64-bit version – it has to be the version compatible with Oracle 11g 64-bit:
Extract the files from the .zip archive and go to that folder where the files where the installation is made from command mode with this command:
=> Install.bat all <location_to_install_odac> odac
- Download Oracle SQL Developer for testing purposes, like establishing a connection to the database, querying that database, etc.:
After installation, the sqlplus utitily is going to be available as well, for testing purposes.
- As an example, just for using an existing database that comes with the installation of Oracle 11g R2 Express Edition, we will use the HR database. After the installation, we have to check that the Oracle Service instance and Oracle Service Listener are running in services:
Configuration
Now that we have all the prerequisites installed we now need to configure our service objects
- By default, the hr user will be locked, so we will have to unlock it in sqlplus.Open command line, and use this command first, to connect to the db with the sysdba user:
sqlplus / as sysdba;
- Then use this command to effectively unlock the hr user:
alter user hr identified by hr account unlock;
- In order to test that we can connect to this database, go to Oracle SQL Developer and select the following
=> File
=> New
=> Database Connection
Connection Name: XE
Username and password are: hr
Hostname: localhost
Port: 1521
SID: xe
- Once we have made a successful connection we then have to test the connection to see if it’s working. If it does, we then connect to that database and just run a simple query to display data from one of the database tables as per below:
Creating K2 Oracle Service Instances and SmartObjects
Now that the configuration and testing of the Oracle is complete we can create Oracle Service Instance and SmartObjects
- We have to start by creating a new Oracle Service Instance. We have 2 possibilities to achieve that:
=> Create it in SmartObjects Tester utility OR
=> Create it in K2 Workspace
Create Oracle Service Instance with Workspace
In the K2 Workspace we create a new Oracle Service Instance in by going to:
K2.denallix.com:5555 – SmartObjects – Services – Add:
- Observation: The Owner List has to be the username that is used to connect to that database.
- In this example the Owner List will be hr. We can setup the db items that we need displayed in the Oracle Service Instance (like tables, views, stored procedures, functions etc) by setting this fields to the value ‘True’.
- The connection string is formed from these attributes:
=> User Id
=> Password
=> Data Source
- The User Id and Password are the credentials of the user that connects to the Oracle Database; The Data Source can be found in the tnsnames.ora file, which is stored in this location:
=> C:<ORACLE_HOME>apporacleproduct11.2.0server
etworkADMIN
Create Oracle Service Instance with Workspace
- The other way we can create the Oracle Service Instance is by using the SmartObjects Tester utility using the following steps.
=> Right click 'Oracle Service' and select 'Register ServiceInstance
=> Configure it with the following settings:
==> Service Type: OracleService
==>Authentication Mode: Impersonate
==> Owner List - hr
==> Connection String – as described above
- The Oracle DB contents(tables, views etc) should be now available and be manipulated by creating SmartObjects and using their methods.
You should now me able to Create Smart Objects on tables views and stored procedures.
