OWB 11gR2 – JDBC Helper Utility

One of the common queries when importing the tables via JDBC with 11gR2 is determining why the import wizard doesn’t display the tables that you think it should. I often just use the script below to dump out the schemas, tables and columns that the JDBC driver is returning. This is useful in a few areas;

  • to figure out what the schema name is returned to double check with the schema name you have used in the location (this is used in the DatabaseMetaData.getTables API call within the basic JDBC metadata import.
  • to figure out the data types returned from the JDBC driver when you see columns skipped because of no datatype supported messages.
  • also…I can do it via scripting and don’t need to recompile classes and stuff :-)

Edit the tcl script and set the JDBC driver, the connection URL and the username and password (they are at the bottom of the script), the script then calls a basic tcl procedure which writes to standard out the schemas, tables and columns with various properties. For example I executed it using the XML JDBC driver from ODI over a simple customers XML file and it writes the following metadata;

image

You can add more details as you need and execute from the OMBPlus panel within OWB.

Download the sample tcl jdbc script here

There is a bunch of really useful stuff on OTN documenting this area (start with the white paper here) that is worth checking out all related to the OWB SDK covering everything from platform definitions, custom metadata importers, application adapters, code templates etc. You can find a bunch of goodies on the OWB SDK here.

Comments:

The tip to start using the XML JDBC driver from ODI over a simple customers XML file for writing the metadata is great thanks. The download was good too!

Posted by guest on May 27, 2011 at 06:23 PM PDT #

A question... if the result of the script is only the Schema of the DB how can see the tables in the Schema names. thanks...

Posted by guest on June 23, 2011 at 07:52 AM PDT #

Which JDBC driver are you using? It may be the driver does not support the % filter for table names.

Cheers
David

Posted by David on June 23, 2011 at 08:33 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today