reading text file

[hshah22222]

Contractor Name, Address, and Zip Code
YORK INTERNATIONAL CORPORATION
PURCHASING DIVISION
631 S RICHLAND AVE
YORK, PA
174033445

Contract_v = varchar2 ''contractor,name,and Zipcode);
Address_v = null;

Above is text file. want to read it in Oracle (preferably)or SQL Server.After reading Contractor Name,address,and Zip Code, I want to substring /concat all 4 lines (including tabs) and remove coma after YORKand put all 4 lines in as one field called Address_v and add ',' at the end (after zip code).

Thanks for the help.

[santoshym]

Hi

You can use

UTL_FILE

to read the text file in ur PL/SQL

Regards,

Santosh

[balajiyes]

hi
if you are using 9i use external table
this shuld be easy for ur requirement

[balajiyes]

Steps:
-------
1) create a external table with these Contractor Name, Address, and Zip Code columns.

2) write a select statement:

DECLARE
lv_Address_v VARCHAR2(300);
BEGIN
SELECT Contractor_Name || REPLACE(Address,',','') || Zip_Code || ',' INTO lv_Address_v FROM external_table_name;
insert into Address_v values(lv_Address_v);
commit;
END;

try this hope this help U

"remove coma after YORK" -- in don't understand this paret. i hope u want to remove from address column.

Cheers!