Re: A Question

From: Tim X <timx_at_nospam.dev.null>
Date: Sat, 30 May 2009 13:15:46 +1000
Message-ID: <878wkfl1r1.fsf_at_lion.rapttech.com.au>

Wudong <wudong.liu_at_gmail.com> writes:

> Dear All:
> I have a question on Oracle.
> Basically I have a file with each row is an ID to a row in the
> database. I need to retrieve all the rows with the ID in that file.
> What will be the best way to do that? I'm wondering if Oracle have a
> tool that could possibly help the process because I feel it's quite
> common task..:)
>
> many thanks.
> Wudong

Your choice depends somewhat on what version of Oracle your running, which you forgot to specify.

One relatively easy way to do this would be to define the file as an external table. You can then just do a join between the external table and the internal table that you want to extract rows from.

How well this will work depends on

1. Size of the file
2. Format/consistency of data in the file
3. Version of Oracle
4. Complexity of query and structure of tables, indexes etc.
5. Environment - OS, access restrictions affecting your ability to put the data file in an accessible directory, defining the external table access etc.

Other alternatives include using simple perl script and perl DBI, using simple script with sqlplus, using java and jdbc etc. You can even define a simple web service or even just a basic http gateway that would allow you to upload the data file.

There are many possible solutions, but not enough detail to give a definitive answer.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Fri May 29 2009 - 22:15:46 CDT