SQL TO GENERATE INSERT SCRIPT FOR A PEOPLESOFT RECORD.
replace the PSMSGCATDEFN with your record. Run in oracle SQL tool. It will give a select statement add where or filter clause to it to generate insert statements
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(FIELDNAME | ' | ' | CHR(39) | ''''',''''' | CHR(39) | ' |
|| 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,
FIELDNAME,
FIELDNUM,
COUNT(*) OVER ( PARTITION BY RECNAME ) AS FIELDCOUNT,
ROW_NUMBER () OVER ( PARTITION BY RECNAME ORDER BY FIELDNUM) FIELDSEQUENCE
FROM
PSRECFIELD
WHERE
RECNAME='PSMSGCATDEFN' )
WHERE
FIELDCOUNT=FIELDSEQUENCE
START WITH
FIELDSEQUENCE = 1
CONNECT BY PRIOR
FIELDSEQUENCE+1=FIELDSEQUENCE
AND PRIOR
RECNAME=RECNAME
ORDER BY FIELDNUM
)