Spooling Data with UTF8 Format

Rajan Sw

Hi,

I have a table and the requirement is to spool the data into .sql file.

when I am spooling the data without UTF8 parameter, it is very quick and takes around a min but while the parameter in force, it is taking more than half an hour . The reason we are exporting in utf8 is because of the non printable characters are getting corrupted.

parameter is

export NLS_LANG=AMERICAN_AMERICA.UTF8

Spool sql is this one:

SET LINESIZE 32767

SET SERVEROUTPUT ON

SET FEEDBACK OFF

SET PAGESIZE 0

SET VERIFY OFF

SET TRIMSPOOL ON

SET TRIMOUT ON

set termout OFF

set heading OFF

SET echo OFF

SET LINES 32767

spool vw_etl_codevalue,SQL

SELECT 'INSERT INTO vw_etl_codevalue

(

category,

description,

messagecode,

messagetext,

codeorder,

deleted,

fininstkey,

txtlanguage,

codelang

)

VALUES

(

' || Decode(category,NULL,'NULL','''' || replace(category,'''','''''') || '''') || ',

' || Decode(description,NULL,'NULL','''' || replace(description,'''','''''') || '''') || ',

' || Decode(messagecode,NULL,'NULL','''' || replace(messagecode,'''','''''') || '''') || ',

' || Decode(messagetext,NULL,'NULL','''' || replace(messagetext,'''','''''') || '''') || ',

' || Decode(codeorder,NULL,'NULL','''' || replace(codeorder,'''','''''') || '''') || ',

' || Decode(deleted,NULL,'NULL','''' || replace(deleted,'''','''''') || '''') || ',

' || Decode(fininstkey,NULL,'NULL','''' || replace(fininstkey,'''','''''') || '''') || ',

' || Decode(txtlanguage,NULL,'NULL','''' || replace(txtlanguage,'''','''''') || '''') || ',

' || Decode(codelang,NULL,'NULL','''' || replace(codelang,'''','''''') || '''') || '

);'

FROM vw_etl_codevalue

/

Please share any thoughts how we can achieve without this OS parameter or any other better ways

and the current DB version is 12c R2