Jump to: navigation, search

Difference between revisions of "HowTo:Connect To Oracle With A Workflow"

(Created page with "Before being able to connect to an oracle database it is necessary to configure the connection to the server on your local environment. UBIK uses OLEDB to connecti to Oracle v...")
 
(→‎Example)
 
(29 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
Before being able to connect to an oracle database it is necessary to configure the connection to the server on your local environment. UBIK uses OLEDB to connecti to Oracle via TNS (Transparent Network Substrate).
+
{{UBIK}} uses OLEDB to connect to Oracle via TNS (Transparent Network Substrate), therefore it is necessary to configure the connection to the server on your local environment prior to connecting to an Oracle database.
−
1. After installing the oracle client you have to configure the oracle server in the TNS Names:
+
  
−
In [ORACLESOFTWARE]\network\admin\ you can find "tnsnames.ora".
+
= Instructions =
−
Configure the connection and call it (like in this example TESTDB):  
+
<ol>
−
 
+
<li>Configure the Oracle server in the TNS Names of your Oracle client:
−
<code>
+
* Locate the file ''tnsnames.ora'' in the directory '''[ORACLESOFTWARE]\network\admin\'''
−
MY_CONNECTION_NAME=
+
* Configure the connection and call it (like in this example TESTDB):<br/>
 +
<source lang="text">
 +
TESTDB=
 
  (DESCRIPTION =
 
  (DESCRIPTION =
 
   (ADDRESS_LIST =
 
   (ADDRESS_LIST =
Line 15: Line 16:
 
  )
 
  )
 
)
 
)
−
</code>
+
</source>
−
 
+
</li>
−
 
+
<li>Use the [[Activity:CreateOLEOracleScope (Activity)]] activity to configure the connection via the arguments:</li>
−
2. Use a "CreateOLEOracleScope" Activity to configure the connection.
+
#* the Oracle database '''DataSource'''
−
You have to configure the connection to the Oracle database DataSource, the InitialCatalog and you need to fill out UserName and Password.
+
#* the '''InitialCatalog'''
−
<br/><br/>
+
#* '''UserName''' and '''Password'''
−
3. Use a "CreateOLETableScope" to define the table you want to fetch data from.
+
<li>Use the [[Activity:CreateOLETableScope (Activity)]] activity or a [[Activity:CreateOLEQueryScope (Activity)]] to define the table you want to fetch data from. Set the tablename in the '''DisplayName''' argument.</li>
−
Configure the tablename in DisplayName.
+
<li>Use a '''ForEach<DataRow>''' activity to iterate over the records fetched from the table.</li>
−
<br/><br/>
+
<li>A column of the recordset (table) can be accessed by its index.</li>
−
4. Use a ForEach<DataRow> Activity to iterate over the records in the table.
+
</ol>
−
<br/><br/>
+
−
5. Access the columns of the record by using the index of the column.
+
−
<br/><br/>
+
−
 
+
−
 
+
−
 
+
−
 
+
−
 
+
−
 
+
−
 
+
  
 +
=Example=
 +
Connecting to an Oracle database and fetching data with the [[Activity:CreateOLETableScope (Activity)]]<br />
 +
{{FileLink|oratest.uwf|Download Workflow example}}
 +
[[File:ora_example_worfklow.JPG|440px|border|alt=Example Workflow|Example Workflow]]<br />
  
 +
Connecting to oracle DB and reading data with a CreateOLEQueryScope
 +
{{FileLink|example_workflow_sql_statement.uwf|Download Workflow example using a SQL Statement}}
  
−
[[Category:How-To]]
+
[[Category:How-To|Connect To Oracle With A Workflow]]
−
[[Category:Workflow]]
+
[[Category:OLE|Connect To Oracle With A Workflow]]
−
[[Category:UBIK Studio]]
+

Latest revision as of 09:17, 20 May 2015

UBIK® uses OLEDB to connect to Oracle via TNS (Transparent Network Substrate), therefore it is necessary to configure the connection to the server on your local environment prior to connecting to an Oracle database.

[edit]

Instructions

  1. Configure the Oracle server in the TNS Names of your Oracle client:
    • Locate the file tnsnames.ora in the directory [ORACLESOFTWARE]\network\admin\
    • Configure the connection and call it (like in this example TESTDB):
    TESTDB=
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(Host =AUGTESTORACLE.cloudapp.net)(Port = 1521))
       )
     (CONNECT_DATA =
       (SERVICE_NAME = UBIKTEST)
     )
    )
  2. Use the CreateOLEOracleScope (Activity) activity to configure the connection via the arguments:
      • the Oracle database DataSource
      • the InitialCatalog
      • UserName and Password
  3. Use the CreateOLETableScope (Activity) activity or a CreateOLEQueryScope (Activity) to define the table you want to fetch data from. Set the tablename in the DisplayName argument.
  4. Use a ForEach<DataRow> activity to iterate over the records fetched from the table.
  5. A column of the recordset (table) can be accessed by its index.

Example

Connecting to an Oracle database and fetching data with the CreateOLETableScope (Activity)
IC FileLink Download.png Download Workflow example Example Workflow

Connecting to oracle DB and reading data with a CreateOLEQueryScope IC FileLink Download.png Download Workflow example using a SQL Statement

Retrieved from "https://wiki.augmensys.com/index.php?title=HowTo:Connect_To_Oracle_With_A_Workflow&oldid=15636"