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.

Revision #1
Created Thu, Jul 25, 2019 11:23 PM by PeopleSoft Wiki
Updated Thu, Jul 25, 2019 11:24 PM by PeopleSoft Wiki