Oracle Database (via JDBC) - Import - 7.1

Talend Data Catalog Bridges

author
Talend Documentation Team
EnrichVersion
7.1
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
EnrichPlatform
Talend Data Catalog

Bridge Specifications

Vendor Oracle
Tool Name Oracle Database
Tool Version 6i to 12c
Tool Web Site http://www.oracle.com/database
Supported Methodology [Relational Database] Multi-Model, Data Store (Physical Data Model, Stored Procedure Expression Parsing), (Expression Parsing) via JDBC API
Incremental Harvesting
Multi-Model Harvesting
Remote Repository Browsing for Model Selection
Data Profiling

BRIDGE INFORMATION
Import tool: Oracle Oracle Database 6i to 12c (http://www.oracle.com/database)
Import interface: [Relational Database] Multi-Model, Data Store (Physical Data Model, Stored Procedure Expression Parsing), (Expression Parsing) via JDBC API from Oracle Database (via JDBC)
Import bridge: 'JdbcOracle' 10.1.0

BRIDGE DOCUMENTATION
IMPORTING FROM AN ORACLE DATABASE USING JDBC.

This bridge establishes a JDBC connection with a physical database in order to extract the physical metadata. It is critical that the parameters are filled correctly to satisfy the local connection requirements on the client workstation that runs the bridge. Please refer to the individual parameter's tool tips for more detailed examples.

Important note about permissions:
The user should have permission to create a session as well as role SELECT_CATALOG_ROLE assigned.
Alternatively, the user may have SELECT permission granted for every individual system table that the bridge queries metadata from:
grant create session to [user];
grant select on all_users to [user];
grant select on dba_objects to [user];
grant select on dba_tab_comments to [user];
grant select on dba_external_locations to [user];
grant select on dba_directories to [user];
grant select on dba_mviews to [user];
grant select on dba_clu_columns to [user];
grant select on dba_tab_columns to [user];
grant select on dba_col_comments to [user];
grant select on dba_constraints to [user];
grant select on dba_cons_columns to [user];
grant select on dba_indexes to [user];
grant select on dba_ind_columns to [user];
grant select on dba_procedures to [user];
grant select on dba_synonyms to [user];
grant select on dba_views to [user];
grant select on dba_source to [user];
grant select on dba_triggers to [user];
grant select on dba_arguments to [user];
grant select on dba_sequences to [user];
grant select on dba_dependencies to [user];
grant select on V_$INSTANCE to [user];
grant select on v_$database to [user];
grant select on dba_type_attrs to [user];

Important note about Cursors:
Importing the metadata structure of a database is a highly recursive activity that is an unusual type of access for a database. Hence, most Oracle databases are not tuned for this type of access. The result is often a message like:

ORA-01000: maximum open cursors exceeded
The recommended solution is to contact the Oracle DBA and request an increase in the setting for open_cursors in init.ora.

Driver details:
- driver name: Oracle Database 12c Release 1 JDBC Drive (oracle.jdbc.driver.OracleDriver)
- driver version / build: 12c / 12.1.0.2.0
- file name: ojdbc7.jar (3,698,857 bytes)
- file location: ${MODEL_BRIDGE_HOME}/java/Jdbc/oracle


Bridge Parameters

Parameter Name Description Type Values Default Scope
Driver location This parameter is optional.

By default, all necessary JARs will be loaded from:
'${MODEL_BRIDGE_HOME}/java/Jdbc/oracle'

However, you may use this parameter to specify a different location. All JARs found in the specified folder will be added to the CLASSPATH.

The 'oracle.jdbc.driver.OracleDriver' JDBC driver with the 'Oracle JDBC driver' name is required.
DIRECTORY      
Host Host name or IP address where Oracle database server is running. Please, check examples 1 and 2.
OR
Full qualified JDBC connection string for Oracle JDBC driver. Please, check example 3.

Example #1:
someservername.com

Example #2:
192.169.10.20

Example #3:
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver2)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver3)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))
STRING   localhost Mandatory
Port Oracle database engine service port number. Default value is 1521. NUMERIC   1521 Mandatory
Service Oracle service name. STRING     Mandatory
User The database username on whose behalf the connection is being made.
The user should have permission to create a session as well as role SELECT_CATALOG_ROLE assigned.
Alternatively, the user may have SELECT permission granted for every individual system table that the bridge queries metadata from:
grant create session to [user];
grant select on all_users to [user];
grant select on dba_objects to [user];
grant select on dba_tab_comments to [user];
grant select on dba_external_locations to [user];
grant select on dba_directories to [user];
grant select on dba_mviews to [user];
grant select on dba_clu_columns to [user];
grant select on dba_tab_columns to [user];
grant select on dba_col_comments to [user];
grant select on dba_constraints to [user];
grant select on dba_cons_columns to [user];
grant select on dba_indexes to [user];
grant select on dba_ind_columns to [user];
grant select on dba_procedures to [user];
grant select on dba_synonyms to [user];
grant select on dba_views to [user];
grant select on dba_source to [user];
grant select on dba_triggers to [user];
grant select on dba_arguments to [user];
grant select on dba_sequences to [user];
grant select on dba_dependencies to [user];
grant select on V_$INSTANCE to [user];
grant select on v_$database to [user];
STRING      
Password The database user's password on whose behalf the connection is being made PASSWORD      
Schema You can specify a list of database schemas (users) to import and skip.
When the list is empty all available schemas are imported except 'system' schemas (see below).
The list can have one or more schema names separated by semicolons (e.g. schema1; schema2).
You can specify schema name patterns using SQL LIKE or Oracle Regular expression syntax.
Patterns support inclusions and exclusions.

Here is an example of inclusion syntax, "A%; %B; %C%; D" that tries to get schema names that:
- start with A or
- end with B or
- contain C or
- equal D

Note: when a pattern has special characters, like spaces, enclose it in single quote marks (e.g. "'two wo%'; onewo%;").

To exclude a pattern, prefix it with "NOT". Here is an example of exclusion syntax:
"A%; %B; NOT %SYS; NOT 'SYS%'"
that contributes to the following SQL filter:
"where (name like A% or name like %B) and (name not like %SYS) and (name not like 'SYS%')"

You can filter schema names using the regular expressions syntax supported by the Oracle's REGEXP_LIKE() function. The '^Ste(v|ph)en$' pattern returns schemas with a name of Steven or Stephen
(where name begins with Ste and ends with en and in between is either v or ph).
Here is an example using the regular expression filter REGEXP_LIKE:
'^Ste(v|ph)en$'.
It contributes to the following SQL filter:
"WHERE REGEXP_LIKE (name, '^Ste(v|ph)en$'"
System users (e.g. SYS) and all objects they own are ignored by default.
Oracle 12c depicts with Y in DBA_USERS.ORACLE_MAINTAINED users that it manages (system/internal schemas).
Oracle 10g and 11g define a list of all system users in http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_user_accounts.htm#TDPSG20030.
See Predefined Administrative and Predefined Non-Administrative User Accounts, but not Predefined Sample Schema User Accounts.

A schema is 'internal' when it uses 'SYSTEM' or 'SYSAUX' default tablespaces or has a name in the list:

'ANONYMOUS','BI','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','ODM','ODM_MTR','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','OWBRT_SYS','OASPUBLIC','OLAPSYS','PM','REP_OWNER','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TRACESRV','TSMSYS','WMSYS','WEBSYS','WK_PROXY','WKSYS','WK_TEST','XDB','FLOWS_FILES','WKSYS','WKPROXY','OWBSYS','XS$NULL','WK_TEST','ORACLE_OCM','SH','OE','IX','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','APEX_PUBLIC_USER','HR','TECP_SYS','SYSBACKUP','SYSKM','AUDSYS','OJVMSYS'

The above list includes demo or system schemas that come with the database and not usually created by users. When you need to import a schema in the list or define the list manually you can use an exclusion pattern.
REPOSITORY_SUBSET      
Tables, Views Description: you can specify a list of database tables to import.
When the list is empty all available tables are imported.
The list can have one or more table names separated by semicolons (e.g. table1; table2).
You can specify table name patterns by using SQL LIKE or Oracle Regular expressions syntax.
Patterns support inclusions and exclusions.
Here is an example of inclusion syntax, "A%; %B; %C%; D" that tries to get table names that: - start with A or
- end with B or
- contain C or
- equal D

Note: when a pattern has special characters, like spaces enclose it in single quote marks (e.g. "'two wo%'; onewo%;").

To exclude a pattern prefix it with 'NOT'. Here is an example of exclusion syntax, "A%; %B; NOT %SYS; NOT 'SYS%'" that
contributes to the following SQL filter: "where (name like A% or name like %B) and (name not like %SYS) and (name not like 'SYS%')"

You can filter table names using regular expressions syntax supported by the Oracle's REGEXP_LIKE() function. The '^Ste(v|ph)en$' pattern
returns tables with a name of 'Steven' or 'Stephen' (where name begins with Ste and ends with en and in between is either v or ph).
Here is an example of regular expression filter REGEXP_LIKE '^Ste(v|ph)en$'.
It contributes to the following SQL filter: "WHERE REGEXP_LIKE (name, '^Ste(v|ph)en$'"
STRING      
Stored procedure details Specify what stored procedure details you would like to include

- 'Signature' - name and parameters of stored procedures (default)
- 'Code, signature' - above plus code
- 'Lineage, code, signature' - above plus data lineage derived from the code
- 'None' - stored procedures are not included
ENUMERATED
None
Signature
Code, signature
Lineage, code, signature
Signature  
Import indexes Specifies if the Indexes should be imported or not.

- 'False' : Indexes are not imported(default)
- 'True' : Indexes are imported
BOOLEAN
False
True
False  
Miscellaneous Specify miscellaneous options identified with a -letter and value.

For example, -m 4G -f 100 -j -Dname=value -Xms1G

-f the database driver fetch size in number of rows (e.g. -f 100)

-zip exportFile to store DDL create tables statements (e.g -zip c:/temp/ddl/export.zip)

-m the maximum Java memory size whole number (e.g. -m 4G or -m 2500M ).
-s import system objects that are skipped by default (e.g. -s)
-v set environment variable(s) (e.g. -v var1=value -v var2="value with spaces").
-j the last option that is followed by Java command line options (e.g. -j -Dname=value -Xms1G).

-diff difference detection level (e.g. -diff ikm for disabling checking indexes, keys, classifier and feature maps)

-synonyms disable import of all synonyms

-sl import synonyms features over dblinks by running an additional data query (may requires additional time to import and user permittions to access referenced objects)

-v$instance.instance_name use instance name as database name (applicable for Oracle Real Application Clusters (RAC) databases)

-package.filter database package name filter that uses the same syntax as the "Tables, Views" parameter.

-package.cleanup cleanup packages code after parsing to optimize memory using
STRING      

 

Bridge Mapping

Meta Integration Repository (MIR)
Metamodel
(based on the OMG CWM standard)
"Oracle Database (via JDBC)"
Metamodel
JdbcOracle
Mapping Comments
     
Name Name  
Argument Procedure Column The stored procedure parameters are stored in the arguments
Description Comments on the column  
Kind Type The kind of parameter
Name Name  
Association Exported Keys  
Aggregation   True if all the attributes in the Exported Key a+C105re in the Primary Key
AssociationRole Exported Keys  
ExtraConstraint Update Rule, Delete Rule  
Multiplicity   Nullable property of the columns of the Exported Key
Source   Based on the multiplicity of each role
AssociationRoleNameMap Exported Keys The rolename map is created if the columns in the primary and foreign keys are different
Attribute Table Column  
Comment Comments on the column  
InitialValue Default value  
Name Name  
Optional Based on the nullable property  
PhysicalName Name  
Position Position If position is not provided, the order in which the attributes are retrieved is used.
BaseType Types  
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Maximum scale  
CandidateKey Index, Primary Key  
Name Name  
PhysicalName Name  
UniqueKey Non-Unique property  
Class Table of type "TABLE"
Comment Comments on the table  
CppClassType   Set to ENTITY
CppPersistent   Set to True
Name Name  
PhysicalName Name  
ClassDiagram Schema A class diagram is created for each package and contains all the elements of the package
DerivedType Column Table column, stored procedure column SQL View column or type
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Decimal digits  
UserDefined   True for Type
DesignPackage Schema A Package is created for each retrieved schema. If there is no schema a default package is created.
Name Name Set to "Schema" if there is no schema or the schema has no name.
ForeignKey Exported Keys  
Name Name  
PhysicalName Name  
Index Index, Primary Key, Exported Keys  
Clustered Index type true if index type is tableIndexClustered
Name Name  
PhysicalName Name  
Unique   true if index type is unique
IndexMember Index Member, Key Member  
Position Position in the Index or key  
SortOrder Ascending/descending order  
SQLViewAttribute View Column  
Comment Comments on the column  
Name Name  
PhysicalName Name  
Position Ordinal position  
SQLViewEntity Table of type "VIEW"
Comment Comments on the table  
Name Name  
PhysicalName Name  
StoreModel Catalog The model is built using the elements contained in the catalog (The default catalog used for Oracle)
Name Name Set to "Catalog".
StoredProcedure Stored Procedure The stored procedures are connected to the procedure Owner name and the Parent name.
Name Name  
Synonym Table Synonym  
Name Name