SET echo off ;
SET heading off ;
SET feedback off ;
SPOOL 'd:/tmp.sql' replace
SELECT CASE
WHEN LINE = 1 THEN
'CREATE OR REPLACE ' || TEXT
WHEN LINE = MAX_LINE THEN
TEXT || CHR(10 ) || '/'
ELSE
TEXT
END
FROM USER_SOURCE A
LEFT JOIN (SELECT A.NAME,A.TYPE, MAX(LINE) MAX_LINE
FROM USER_SOURCE A
GROUP BY A.NAME,A.TYPE ) B ON A.NAME||A.TYPE = B.NAME||B.TYPE
WHERE lower(A.NAME) IN
(
'bp_platform_order'
,'bp_x_wx_order_create'
,'bp_f_x_order_pay'
,'pkg_settle_status'
)
ORDER BY a.NAME||a.TYPE , LINE;
SPOOL OFF