Inserting rows from flat file.

[ProDev]

Hi,

I need something like this ....

A stored procedure that will read a flat file and will insert /update respective columns in multiple oracle tables. Is it possible to write such a PL/SQl stored procedure ??

Thanx

 

[sem]

If you do need a pl/sql solution and may place that file to the location accessible by Oracle server, read about UTL_FILE package. But normally sql*loader is used for such purposes.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

ProDev,

To confirm Sem's (Dima's) post, certainly, PL/SQL can issue both "INSERT" and "UPDATE" (and, when necessary "DELETE") commands against any number of tables. Additionally, PL/SQL can read flat files that contain entries that your PL/SQL program can interpret in such a was as to INSERT/UPDATE/DELETE rows in one or more tables.

You can design such a flat file to have entries that signal an INSERT, UPDATE, or DELETE activity, followed by entries to INSERT or UPDATE or the data that specifies one or more rows to DELETE. In such a case, it is entirely up to you to devise the data scheme(s) in the flat file to carry out such activities.

Dima and I cannot be any more specific since nothing more specific about your tables, flat files, or processing requirements appear in your post. But in direct answer to your question, "can one create 'A stored procedure that will read a flat file and will insert /update respective columns in multiple oracle tables?'" the answer is "certainly"...but we're not likely to be interested in coding it for you here on Tek-Tips, even if you post specifics, but we're happy to offer some guidance, as Dima and I have (hopefully) done here.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:43 (07Apr04) UTC (aka "GMT" and "Zulu"), 00:43 (07Apr04) Mountain Time)

 

[ProDev]

Hi,

Pls let me know the steps involved in reading a record form flat file (using UTL_FILE) and then inserting the same into Oracle table if the record does not exist in table and update if it exists.


Flat file is as shown here


"Zip Code","City","County","State","FIPS"

"00008","Tongass National Forest","Prince of Wales-Outer Ketchikan","AK","02201"

"00009","Lake Clark National Preserve","Bethel Census Area","AK","02050"

"00010","Yukon Delta Wilderness","Wade Hampton Census Area","AK","02270"

I need to populate a table

OMMON_STATE_DETAILS
(
COMMON_STATE_DETAILS_ID INTEGER NOT NULL,
STATE_NAME VARCHAR2(20),
STATE_CODE VARCHAR2(2),
STATE_FIPS_CODE VARCHAR2(2)
)


COMMON_STATE_DETAILS_ID is a Sequence number.
STATE_NAME is the 4th column value in file.
STATE_CODE is null.
STATE_FIPS_CODE is 5th column value in file.


Thanx

 

[SantaMufasa]

ProDev,

When you say, "...update if it (record) exists", what, precisely, will exist in the table?...The only data that appears to be common between your flat-file and the table are "STATE_CODE" and "STATE_FIPS_CODE". So what will you be updating? What will you be inserting if "STATE_CODE" and "STATE_FIPS_CODE" don't match?...just "STATE_CODE" and "STATE_FIPS_CODE", right?

Am I missing something?

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:02 (07Apr04) UTC (aka "GMT" and "Zulu"), 13:02 (07Apr04) Mountain Time)

 

[ProDev]

Well, "STATE_CODE" and "STATE_FIPS_CODE" are the columns. "STATE_FIPS_CODE" needs to be updated if the STATE from flat file (i.e 'AK','CA', ...) is exists in table.


By the way the flat file will be like this


Zip Code","City","County","State","FIPS"

"00008","Tongass National Forest","Prince of Wales-Outer Ketchikan","AK","02201"

"00009","Lake Clark National Preserve","Bethel Census Area","CA","02050"

"00010","Yukon Delta Wilderness","Wade Hampton Census Area","NY","02270"


i.e, the STATE is not duclicated in flat file


While an insertion, it should happen with COMMON_STATE_DETAILS_ID as SEQ_COMMON_STATE_DETAILS_ID.NextVal


Thanks