PeopleSoft Wiki

SELECT * FROM (
SELECT 'SELECT TO_CHAR(REPLACE(TT,'','''')'','')'' )) FROM ('||
   'SELECT ''INSERT INTO '|| (CASE WHEN SUBSTR(RECNAME, 1, 2) != 'PS' THEN 'PS_' || RECNAME ELSE RECNAME END)
   || ' VALUES(''''''||''''||'
  ||    SUBSTR(SYS_CONNECT_BY_PATH(decode(FIELDTYPE,4,'to_char('||fieldname||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')',fieldname)||'||'||CHR(39)||''''','''''||CHR(39)||'||', ' '), 2)
 
   || CHR(13)
   || CHR(39)|| ');'' AS TT FROM '
   || (CASE WHEN SUBSTR(RECNAME, 1, 2) != 'PS' THEN 'PS_' || RECNAME ELSE RECNAME END)
   || ');' as select_statement
   ,DECODE(FIELDSEQUENCE,1, FIELDNAME,'')
   ,FIELDNUM
FROM
   (
   SELECT
     RECNAME,
     a.FIELDNAME,
     FIELDNUM,
     b.fieldtype,
     COUNT(*) OVER ( PARTITION BY RECNAME ) AS FIELDCOUNT,
     ROW_NUMBER () OVER ( PARTITION BY RECNAME ORDER BY FIELDNUM) FIELDSEQUENCE
   FROM
     PSRECFIELD a, PSDBFIELD b
   WHERE
     RECNAME='<your recname>' 
       AND a.FIELDNAME = B.FIELDNAME) 
WHERE
   FIELDCOUNT=FIELDSEQUENCE
START WITH
   FIELDSEQUENCE = 1
CONNECT BY PRIOR
   FIELDSEQUENCE+1=FIELDSEQUENCE
AND PRIOR
   RECNAME=RECNAME
ORDER BY FIELDNUM
)