Oracle Database
Select database Category
- There are 4 new stages in the database
- That is in 8 version
- I way Enterprise
- Classic Federation
- Netezza Enterprise
- ODBC
To read the data from Oracle
We have 4 options in Reading Method
Read Method = Table /user-defined SQL / Auto generated / SQL builder generated SQL
Oracle Data set
Oracle properties
Source Read Method = Table (Select)
Table = EMP (Specify)
Connection
DB options = { user = <<user>>, password = <<password>>}
DB options Mode = Auto generate
Password = Tiger
User = Scott
- Click on connection
- Click on Remote Server
- Remote Server = Oracle
- Click on columns
Load
Table Definitions
Import
Plug in Metadata definition
Select the Driver that is Oracle 9
ok
Data 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 Stage
Data 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
ok0
Ok
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 DEPT
Rename the sheet1, sheet2
save then with .xls extension
Creating DSN for excel Driver:-
Start à control panel administrative Tools
Data source
system DSN
AddMicrosoft 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
import
ODBC Table Definition
Select the DSN : Chaladil user name = admin password = phil
Ok
- 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)