Ora9

-- ==============================================================
-- ORACLE'S REAL BCP FOR TABLES WITH CLOBS
-- Works exactly like: bcp "SELECT * FROM table" out file.txt -c -t "~"
-- Separator: ~
-- ==============================================================

SET ECHO OFF FEEDBACK OFF VERIFY OFF HEADING OFF PAGESIZE 0
SET LINESIZE 32767 TRIMSPOOL ON LONG 2000000000 LONGC 2000000000
SPOOL C:\temp\CACHECPLSORDER_bcp.txt

SELECT
NVL(TO_CHAR(INTERNIDENTITY),'') || '~' ||
NVL(TO_CHAR(INTERNINSTID),'') || '~' ||
NVL(TO_CHAR(INTERNVERSION),'') || '~' ||
NVL(TO_CHAR(INTERNTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF6'),'') || '~' ||
NVL(TO_CHAR(INTERNRECTYPE),'') || '~' ||
NVL(DBMS_LOB.SUBSTR(INTERNFIXSTRING, 3999, 1),'') || '~' || -- 3999 = safe for ~ delimiter
NVL(ORDERID,'') || '~' ||
NVL(ACCOUNT,'') || '~' ||
NVL(MERGEID,'') || '~' ||
NVL(PARENTORDERID,'') || '~' ||
NVL(SALESPERSONID,'') || '~' ||
NVL(SYMBOL,'') || '~' ||
NVL(TO_CHAR(EXPIREDATE,'YYYY-MM-DD'),'') || '~' ||
NVL(TO_CHAR(EXPIRETIME,'HH24:MI:SS'),'') || '~' ||
NVL(TO_CHAR(LEAVESQTY,'FM99999999999999999990.999999999999'),'') || '~' ||
NVL(TIMEINFORCE,'') || '~' ||
-- Sybase-compatible MD5 (UTF-8)
RAWTOHEX(STANDARD_HASH(
UTL_I18N.STRING_TO_RAW(
NVL(TO_CHAR(INTERNIDENTITY),'') ||
NVL(TO_CHAR(INTERNINSTID),'') ||
NVL(TO_CHAR(INTERNVERSION),'') ||
NVL(TO_CHAR(INTERNTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF6'),'') ||
NVL(TO_CHAR(INTERNRECTYPE),'') ||
NVL(DBMS_LOB.SUBSTR(INTERNFIXSTRING,3999,1),'') ||
NVL(ORDERID,'') || NVL(ACCOUNT,'') || NVL(MERGEID,'') ||
NVL(PARENTORDERID,'') || NVL(SALESPERSONID,'') || NVL(SYMBOL,'') ||
NVL(TO_CHAR(EXPIREDATE,'YYYY-MM-DD'),'') ||
NVL(TO_CHAR(EXPIRETIME,'HH24:MI:SS'),'') ||
NVL(TO_CHAR(LEAVESQTY,'FM99999999999999999990.999999999999'),'') ||
NVL(TIMEINFORCE,'')
, 'AL32UTF8')
, 'MD5'))
FROM CACHECPLSORDER
ORDER BY INTERNIDENTITY, INTERNINSTID, INTERNTIMESTAMP;

SPOOL OFF
EXIT