stored procedure from oracle to pgsql

Поиск
Список
Период
Сортировка
От Nefnifi, Kasem
Тема stored procedure from oracle to pgsql
Дата
Msg-id 25D4919915CCF742A88EE3366D6D913D08A86286@mailserver1
обсуждение исходный текст
Ответы Re: stored procedure from oracle to pgsql  ("Joshua D. Drake" <jd@commandprompt.com>)
Список pgsql-general
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.
****************************************************************************

В списке pgsql-general по дате отправления:

Предыдущее
От: Roman Neuhauser
Дата:
Сообщение: Re: Infinite recursion detected... How do I prevent that?
Следующее
От: Geoffrey
Дата:
Сообщение: Re: Best Linux Distribution