Click on connection
Click on Remote Server
Remote Server = Oracle
Click on columnsLoadTable DefinitionsImportPlug in Metadata definition Select the Driver that is Oracle 9 okData source Name = oracle/User id = Scot/ Password = TigeràNextà coroner list = Scott Table Next Select EMP import Table Definition plugin ORACLE 9 EMP
IN columns

[change the SQL type of Hire date from Data to time stamp ]

Now view Data

To Select Required fields

Read Method = user-defined SQL

SQL Query = Select e no, e name, Sal from emp where dept no = 10

↓

(click on columns on the right side, to view all the column names of EMP tables

That is e no, e name Sal, job, Hire data,……)

Read Method = Auto-generated SQL

Oracle enterprise − Dataset

↓

First head method = Table load and then go for Auto-generated SQL

Properties Read Method = Auto-generated SQL Read Method = user-defined SQL

SQL Query = Select emp no, e name, job from emp where dept no = 10;

Now go to columns:-
Delete the remaining fields other than e no, e name, job
Ok
Now view data

Read Method = SQL builder generated SQL

Now go to file

Now

Oracle Enterprise — Dataset

↓

Properties Read Method = SQL builder generated SQL.

SQL Query = <<SQL builder Query>>

Password = Tiger

User = Scott click on build SQL

Remote Server = Oracle à Build new Query (oracle 9 syntax)

Select Table

Table Definition – plug in – ORACLE 9- Scott –Select EMP, DEPT

Here double click on the required columns from both the table.

Select columns

Required columns will be displayed

construct filter expression (WHERE Clause)

Predicates expression editor

↓ ↓

Comparison in link null emp. Dept no = 10 add

Click on Add
Click on SQL
Ok
Now the query will be displayed in SQL query
View data

Data Connection

In version 7.5 * 2, stage property values cannot be reused.
In version 0.1, stage property values cannot be reused.

Note :

The data connection is only in the 8 versions.

It is a technique for saving and reusing stage property values.

The first way of creating a Data connection

Suppose that we have 3 different oracle enterprises, and in 1 oracle enterprise We create a data connection, then it automatically other 2 records enterprise Should the username, password.

(i) Oracle enterprise Data set

↓ ↓

Properties User = Scott

Password = Tiger

Remote Server = Oracle StageData connection Browse Save data connection data connection Name = stark Save Data connection as Table Definition Star Save ok

Oracle enterprise dataset

Properties stage data connection = load data connection Table Definition star open ok (it that is oracle enterprise automatically gets user id, pwd, a remote server)

The second way of creating Data connection:- (BY default to all oracle enterprise)

Go to file New other (click on data connection) ok (click on data connection name = Shilpa) click ok parameters browse stage types parallel database oracle enterprise open Server = oracle user-id = Scott password = Tiger ok0Ok Save in Table Definition ok
Now, we have created a Data connection with the name “Shilpa”
Now, oracle enterprise à properties à stage à Data connection à click on Shilpa à ok
Now again click on properties à now, user id, password, remote server

will be automatically displayed.

Deleting Data connection

View Repository Table Definition select Data connection Right-click delete

ODBC Enterprise

ODBC enterprise — Dataset

Properties Read method = Table

Table = EMP

Connection Data source = chaladi

Click on connection user = Scott password = tiger

Columns à load à view data

Creation of Data source

Start control panel Administrate Tools Data source (ODBC) System DSN Add Microsoft ODBC for oracle finish Data source = chaladi user = Scott Server = oracle ok

[Data source Name is created with the Name “chaladi”]

To overcome the limitations of the ODBC enterprise we have an ODBC connector.

ODBC Enterprise

5 *2 version
Cannot list the DSV
Cannot list the connection
Reads Sequentially writes parallel.
Very poor performance
If a data type mismatch is there, we can’t view data.

ODBC Connector

0.1 version
List the DSN
Can test the connection (either good or not )
Reads parallel, writes parallel
Good performance
Schema Reconstitution (we can view data, even if a mismatch is there)

Automatically handles the conflict between Source data type and Data stage datatypes.

ODBC connection

ODBC Connector —– Data set

Double click on ODBC connector it lists the DSN Names Select the Data Source (that is chaladi) ok username = Scott password = tiger Click on test display the connection is good SQL Select Statement = select * from EMP click on view Data

Note :

Oracle enterprise

Version dependent

It works only with the versions that are associated with Dataset(that is it can access Oracle 9 i but not oracle10g and 11g). To overcome this, we have a substitute that is ODBC connector

Good performance

Oracle enterprise supports reject link at target but not at the source

ODBC connector

Version Independent à depends on the operating system To connect the Oracle database.
Poor performance
ODBC connector supports reject links both at source and target.

In oracle enterprise à while loading àIn import à, we are using plugin Metadata

Then we need to change Data Datatype to Time Stamp.

But while import à use or Orchestrate.

It automatically takes à time stamp Best Results.

To read Excel data with ODBC

Xls filename is known as Workbook
Filename = Workbook à user

Sheet, sheet 2, …

While reading Data with ODBC

Sheet 1 becomes Table 1

Sheet 1 becomes Table 2

Starts programs micro soft excel create excel sheet EMP DEPTRename the sheet1, sheet2 save then with .xls extension

Creating DSN for excel Driver:-

Start à control panel administrative Tools Data source system DSNAddMicrosoft excel Driver (* .xls) à Data source Name = chaladil Select Workbook D:/shilpa à Double click dept .xls emp .xls Select Emp.xls ok Select chaladil

ODBC enterprise — Dataset

Properties Read Method = Table

Data source = chaladil

User = admin system user id and password

Password = phil

Columns load Table Definition importODBC Table Definition Select the DSN : Chaladil user name = admin password = philOk
Select filter include System Tables Ok
Select EMP Import
Table Definitions
ODBC
Chaladil
EMP
Ok
Ok

Now, in properties

Table = “EMP$” (always table name should be in double quotes followed with $ symbol)