PL/SQL for Long Data Type Fields

On Oracle databases, you can't use standard SQL insert statements on fields that have a type of LONG. In order to work with data in tables with these types of fields, you'll need to use PL/SQL. Here's a very simple example of inserting into the message catalog from another database (database connection):

BEGIN
FOR ROW IN
       (SELECT  MESSAGE_SET_NBR,
                MESSAGE_NBR,
                MESSAGE_TEXT,
                MSG_SEVERITY,
                LAST_UPDATE_DTTM,
                DESCRLONG
        FROM    SYSADM.PSMSGCATDEFN@REMOTE_DB
        WHERE   MESSAGE_SET_NBR = '12345'
    )
    LOOP
    INSERT INTO SYSADM.PSMSGCATDEFN
    VALUES (    
                ROW.MESSAGE_SET_NBR,
                ROW.MESSAGE_NBR,
                ROW.MESSAGE_TEXT,
                ROW.MSG_SEVERITY,
                ROW.LAST_UPDATE_DTTM,
                ROW.DESCRLONG
           );
END LOOP;
END;
/

Note this isn't a problem with newer PeopleTools/Application versions where such fields have now changed to a data type of CLOB.

Page tags: database sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License