Choice of Implementation

[Harish Vembu]

Dear members and Ranchers,

I have a requirement as follows

I need to read a tab delimited file from a directory and parse it and load into the oracle database.

The application process may be run as a cron job

The data is to be loaded onto different tables based on what each field data in the text file represents.The tables are related based on different columns in the tables.(I have the ER diagram and know the relationship)

I had seen some posts here and elsewhere that SQLLoader is a way to do it through a Java application. Also being mentioned that its better in performance comparison to using JDBC application.

My first choice as per above would be to go for SQLLoader but since I have to load different columns in seperate tables in a schema I am having a doubt that SQL Loader may not serve the purpose here.

If anyone gives me a suggestion/better idea/choice/working example/tutorial, I would feel better before I take up this task.


Any quick help would be much appreciated.


Thanks & Best Regards

Harish

[Jeanne Boyarsky]

Harish,
SQLLoader can handle multiple tables. See the sample control file in the Oracle FAQ.

[Harish Vembu]

Dear Members and Ranchers,

I am here again with another query

Thanks Jeanne for the reply.

Nice to know about loading to mutliple tables. Can we have SQL join queries executed as a part of SQL Loader data insertion/updation.

I am looking for a smart way of loading data based on SQL join performed on mutiple tables.

In short what I need to know is

I have load data from a tab delimited file after performing SQL join on mutiple tables which satisfy certain conditions. Is it possible with SQL Loader.

Can it work as part of standalone Java application?

Best Regards

Harish

[Paul Campbell]

Harish,

Are you asking if you can update/insert to columns where the columns exist in different tables in a single SQL statement by joining the tables?

[Harish Vembu]

Dear Ranchers,

Yes Paul,I am lookng for insertion/updation of columns present in different Oracle tables by SQL Join through Oracle SQL Loader?

The main point is that I want to achieve it through SQL Loader and Java

I hope this is clear

If so can you give me a sample code?

Thanks & Best Regards

Harish

[Paul Campbell]

Harish,

If you want to keep it simple, do your update via two sql statements.

If you have some reason that you must do it (update columns in different tables in a single SQL statement, you can only do that via an updatable view (as long as it meets the criteria to be an updatable view. These are the rules (for Oracle anyway, but will be similar elsewhere):

The following notes apply to updatable views:

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

*

Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

*

The view must not contain any of the following constructs:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

*

In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
*

If you want a join view to be updatable, then all of the following conditions must be true:
o

The DML statement must affect only one table underlying the join.
o

For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
o

For an UPDATE statement, the view must not be created WITH CHECK OPTION, and all columns updated must be extracted from a key-preserved table.
*

For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.