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