Dear,
I'm new in pgsql, come from oracle and sql server.
any one can help by transferring a pl/sql procedure that imports data from a flat file, using UTL_FILE, and inserts or
updatestables.
bellow the proc:
CREATE OR REPLACE Procedure UPD is
sOracleMsg varchar2(512);
sCustomerMsg varchar2(100);
fHandle UTL_FILE.FILE_Type;
sPath varchar2(200) := '/home';
sfile varchar2(30) := 'prod_2903.txt';
sBuffer varchar2(4000);
contract vertraege."Vertrags_Nr"%TYPE;
phone vertraege."Phone"%TYPE;
status vertraege."Status"%TYPE;
-- Declare program variables as shown above
BEGIN
fHandle := UTL_FILE.FOPEN(sPath, '/'||sfile, 'r');
IF UTL_FILE.Is_OPEN(fHandle) THEN
loop
begin
UTL_FILE.GET_LINE(fHandle, sBuffer);
phone := substr(sBuffer,1,9);
status := upper(substr(sBuffer,10,1));
contract := substr(sBuffer,10,7);
update vertraege vr
set vr.phone = phone
where vr.vertrags_nr = contract;
commit;
exception
when no_data_found then
insert into vertaraege (phone, vertrags_nr)
values (phone, contract);
commit;
when others then
raise;
end;
end loop;
ELSE
raise_application_error(-20001,'can not open file' || sfile || '. or file not exist');
END IF;
COMMIT;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
rollback;
raise_application_error(-20002, 'can not open path:' || sPath);
END; -- Procedure
Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche Grüße !!!
Kasem NEFNIFI
AtosOrigin Belgium N.V.
Minervastraat 7
1930 Zaventem (Belgium)
Tel : +32(0)2 712 28 30
Fax : +32(0)2 712 28 63
GSM : +32 495 25 12 33
Email : kasem.nefnifi@atosorigin.com <mailto:kasem.nefnifi@atosorigin.com>
www.atosorigin.com <http://www.atosorigin.com>
****************************************************************************
Disclaimer:
This electronic transmission and any files attached to it are strictly
confidential and intended solely for the addressee. If you are not
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this
transmission in error, please notify the sender by return and delete
the transmission. Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages
resulting from any virus transmitted.
Thank You.
****************************************************************************