Exporting to a CSV file output

[paljnad]

I have this query that I am using to export data from multiple tables to a CSV File but I keep getting 'ORA - 12704 Character Set Mismatch' error message. The query is:

select case rownum when 1 then
'#DATASOURCE,CONTNO,TRANNO, TRANMODE,TRANTYPE, TRANTYPEDESCRIPTION,
PRIMARYCURR, BUNIT,BUNITSHORTNAME, BUNITFULLNAME, CPTY, CPTYSHORTNAME,
CPTYFULLNAME, DEALDATE, VALUEDATE, PAYRECTYPEID, AMOUNT, REFERENCE,
ACTIONED, ACTIONEDDATE, OURBANKPAY, OURBANKREC, THEIRBANKPAY, THEIRBANKREC,
SETTLEMETHODCODE, DESCRIPTION, TRADEFLOWTYPEID, PORTCODE,LINKREF1, LINKREF2, LINKREF3'
else datasource || ',' || contno || ',' || tranno || ',' || tranmode || ',' || trantype || ',' ||
trantypedescription || ',' || primarycurr || ',' || bunit || ',' || bunitshortname || ',' ||
bunitfullname || ',' || cpty || ',' || cptyshortname || ',' || cptyfullname || ',' || dealdate || ',' ||
valuedate || ',' || payrectypeid || ',' || amount || ',' || reference || ',' || actioned || ',' ||
actioneddate || ',' || ourbankpay || ',' || ourbankrec || ',' || theirbankpay || ',' ||
theirbankrec || ',' || settlemethodcode || ',' || description || ',' || tradeflowtypeid || ',' ||
portcode || ',' || linkref1 || ',' || linkref2 || ',' || linkref3
end
from (SELECT ctf.datasource as datasource,
c.ContNo as ContNo,
c.TranNo as Tranno,
c.TranMode as TranMode,
c.TRANTYPE as TranType,
t.Description AS TranTypeDescription,
c.PrimaryCurr as PrimaryCurr,
c.BUnit as BUnit,
b.ShortName AS BUnitShortName,
b.FullName AS BUnitFullName,
c.Cpty as Cpty,
y.ShortName AS CptyShortName,
y.FullName AS CptyFullName,
ctf.DealDate as DealDate,
ctf.ValueDate as ValueDate,
ctf.PayRecTypeID as PayRecTypeID,
ctf.Amount as Amount,
ctf.Reference as Reference,
ctf.Actioned as Actioned,
ctf.ActionedDate as ActionedDate,
ctf.OurBankPay as OurBankPay,
ctf.OurBankRec as OurBankRec,
ctf.TheirBankPay as TheirBankPay,
ctf.TheirBankRec as TheirBankRec,
ctf.SettleMethodCode as SettleMethodCode,
tk.Description as Description,
0 AS TradeFlowTypeID ,
c.PortCode as PortCode,
c.LinkRef1 as LinkRef1,
c.LinkRef2 as LinkRef2,
c.LinkRef3 as LinkRef3
FROM table c
INNER JOIN table ctf ON c.ContNo = ctf.ContNo
INNER JOIN table tk ON c.TranNo = tk.TranNo
AND c.TranMode = tk.TranMode
INNER JOIN table t ON c.TranNo = t.TranNo
AND c.TranMode = t.TranMode
AND c.TranType = t.TranType
LEFT OUTER JOIN table y ON c.Cpty = y.Cpty
INNER JOIN table b ON c.BUnit = b.BUnit
WHERE (c.TranNo = 99)
AND (tk.TranCode = 1)
and to_char(valuedate, 'mm/dd/yyyy') = to_char(sysdate, 'mm/dd/yyyy'))

If I just run the select query then it runs fine.

 

[SantaMufasa]

Palnjad,

We would be able to replicate, then help resolve, your error if you were able to post the apurtenant "CREATE TABLE..." and a few rows of "INSERT INTO..." statements that would allow us to duplicate your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[paljnad]

Hi Mufasa,

The tables are part of a vendor package and they directly gave the create and insert script to the DBAs and not to the app people. We are just trying to figure out a way to get the data out of the tables in CSV format.

 

[Thargy]

palnjad,

if you can select from the tables then you should be able to DESCribe the tables, which will enable you to make a create table statement.

If you use a productivity tool such as TOAD or SQL Developer (which is free from oracle) then you can generate insert statements at the press of a button.

Do you have access to these tools? If not, you can issue the describe command from sql plus.

There is also an excellent response to precisely this question by the illustrious Mr Tom Kyte, see

http://asktom.oracle.com/tkyte/flat/index.html

for excellent details.

Regards

T

 

[SantaMufasa]

If I can't re-create your environment, then all I can do is make suggestions:

In situations such as this, I isolate the problem by cutting down the SQL code to a minimal level (where the error does not occur), then I start adding code back in until the error re-surfaces.

So, in your case, I would temporarily replace all of the CSV portion of the code with "count(*)":

select count*(*)
from (SELECT ctf.datasource as datasource,
       c.ContNo as ContNo,
       c.TranNo as Tranno,
       c.TranMode as TranMode,
       c.TRANTYPE as TranType,
       t.Description AS TranTypeDescription,
       c.PrimaryCurr as PrimaryCurr,
       c.BUnit as BUnit,
       b.ShortName AS BUnitShortName,
       b.FullName AS BUnitFullName,
       c.Cpty as Cpty,
       y.ShortName AS CptyShortName,
       y.FullName AS CptyFullName,
       ctf.DealDate as DealDate,
       ctf.ValueDate as ValueDate,
       ctf.PayRecTypeID as PayRecTypeID,
       ctf.Amount as Amount,
       ctf.Reference as Reference,
       ctf.Actioned as Actioned,
       ctf.ActionedDate as ActionedDate,
       ctf.OurBankPay as OurBankPay,
       ctf.OurBankRec as OurBankRec,
       ctf.TheirBankPay as TheirBankPay,
       ctf.TheirBankRec as TheirBankRec,
       ctf.SettleMethodCode as SettleMethodCode,
       tk.Description as Description,
       0 AS TradeFlowTypeID ,
       c.PortCode as PortCode,
       c.LinkRef1 as LinkRef1,
       c.LinkRef2 as LinkRef2,
       c.LinkRef3 as LinkRef3
  FROM table c
 INNER JOIN table ctf ON c.ContNo = ctf.ContNo
 INNER JOIN table tk ON c.TranNo = tk.TranNo
                                   AND c.TranMode = tk.TranMode
 INNER JOIN table t ON c.TranNo = t.TranNo
                             AND c.TranMode = t.TranMode
                             AND c.TranType = t.TranType
  LEFT OUTER JOIN table y ON c.Cpty = y.Cpty
 INNER JOIN table b ON c.BUnit = b.BUnit
 WHERE (c.TranNo = 99)
   AND (tk.TranCode = 1)
   and to_char(valuedate, 'mm/dd/yyyy') = to_char(sysdate, 'mm/dd/yyyy'))

Let us know if this eliminates the error. If it does, then you can start adding back in small segments of the CASE clause until the error resurfaces.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

Using "CASE ROWNUM WHEN 1" would generate the title line but SKIP the first row!

Maybe it's better to generate an HTML file which then can be either viewed using IE or loaded into an Excel spreadsheet.

Here is an example from the SQL*Plus manual:

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
SPOOL  c:\my_file.html
SELECT datasource, contno, tranno, tranmode, trantype, trantypedescription,
       primarycurr, bunit, bunitshortname, bunitfullname, cpty, cptyshortname,
       cptyfullname, dealdate, valuedate, payrectypeid, amount, REFERENCE,
       actioned, actioneddate, ourbankpay, ourbankrec, theirbankpay,
       theirbankrec, settlemethodcode, description, tradeflowtypeid, portcode,
       linkref1, linkref2, linkref3
  FROM (SELECT ctf.datasource AS datasource, c.contno AS contno,
               c.tranno AS tranno, c.tranmode AS tranmode,
               c.trantype AS trantype
   -- Etc --
/
SPOOL off
SET MARKUP HTML OFF

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

Your code looks pretty strange anyway since you seem to be throwing away the first row of the query and replacing it with a header. Oh, well, what's one row between friends ?

When you say you've run the query, have you run it all the way through (retrieving every row) ? That is, not just in a window of something like TOAD where it will only display the first 500 or so rows.

 

[SantaMufasa]

Oops...my chubby fingers inadvertently caused "count(*)" to come out as "count*(*)". Sorry,



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[paljnad]

The select count(*) from..worked so now I am going to try and add back all the segments in steps to see where I get the error.

 

[paljnad]

I tried this and it ran without any errors:

select datasource || ',' || contno || ',' || tranno || ',' || tranmode || ',' ||
trantype || ',' || trantypedescription || ',' || primarycurr || ',' ||
bunit || ',' || bunitshortname || ',' || bunitfullname || ',' || cpty || ',' ||
cptyshortname || ',' || cptyfullname || ',' || dealdate || ',' ||
valuedate || ',' || payrectypeid || ',' || amount || ',' ||
reference || ',' || actioned || ',' || actioneddate || ',' ||
ourbankpay || ',' || ourbankrec || ',' || theirbankpay || ',' ||
theirbankrec || ',' || settlemethodcode || ',' || description || ',' ||
tradeflowtypeid || ',' || portcode || ',' || linkref1 || ',' ||
linkref2 || ',' || linkref3
from (SELECT ctf.datasource as datasource,
c.ContNo as ContNo,
c.TranNo as Tranno,
c.TranMode as TranMode,
c.TRANTYPE as TranType,
t.Description AS TranTypeDescription,
c.PrimaryCurr as PrimaryCurr,
c.BUnit as BUnit,
b.ShortName AS BUnitShortName,
b.FullName AS BUnitFullName,
c.Cpty as Cpty,
y.ShortName AS CptyShortName,
y.FullName AS CptyFullName,
ctf.DealDate as DealDate,
ctf.ValueDate as ValueDate,
ctf.PayRecTypeID as PayRecTypeID,
ctf.Amount as Amount,
ctf.Reference as Reference,
ctf.Actioned as Actioned,
ctf.ActionedDate as ActionedDate,
ctf.OurBankPay as OurBankPay,
ctf.OurBankRec as OurBankRec,
ctf.TheirBankPay as TheirBankPay,
ctf.TheirBankRec as TheirBankRec,
ctf.SettleMethodCode as SettleMethodCode,
tk.Description as Description,
0 AS TradeFlowTypeID ,
c.PortCode as PortCode,
c.LinkRef1 as LinkRef1,
c.LinkRef2 as LinkRef2,
c.LinkRef3 as LinkRef3
FROM table c
INNER JOIN table ctf ON c.ContNo = ctf.ContNo
INNER JOIN table tk ON c.TranNo = tk.TranNo
AND c.TranMode = tk.TranMode
INNER JOIN table t ON c.TranNo = t.TranNo
AND c.TranMode = t.TranMode
AND c.TranType = t.TranType
LEFT OUTER JOIN table y ON c.Cpty = y.Cpty
INNER JOIN table b ON c.BUnit = b.BUnit
WHERE (c.TranNo = 99)
AND (tk.TranCode = 1)
and to_char(valuedate, 'mm/dd/yyyy') = to_char(sysdate, 'mm/dd/yyyy'))

 

[LKBrwnDBA]

Ok, if you insist...try this:

SET ECHO OFF TERM OFF VER OFF PAGES 0 LIN 256 TRIMS ON
COL sq NOPRINT

SELECT '0000000000' sq,
'#DATASOURCE,CONTNO,TRANNO, TRANMODE,TRANTYPE, TRANTYPEDESCRIPTION, 
PRIMARYCURR, BUNIT,BUNITSHORTNAME, BUNITFULLNAME, CPTY, CPTYSHORTNAME, 
CPTYFULLNAME, DEALDATE, VALUEDATE, PAYRECTYPEID, AMOUNT, REFERENCE, 
ACTIONED, ACTIONEDDATE, OURBANKPAY, OURBANKREC, THEIRBANKPAY, THEIRBANKREC,
SETTLEMETHODCODE, DESCRIPTION, TRADEFLOWTYPEID, PORTCODE,LINKREF1, LINKREF2, LINKREF3' 
UNION
SELECT TO_CHAR(rownum,'FM0000000000') sq,
         datasource || ',' || contno || ',' || tranno || ',' || tranmode || ','
       || trantype || ',' || trantypedescription || ',' || primarycurr || ','
       || bunit || ',' || bunitshortname || ',' || bunitfullname || ','
       || cpty || ',' || cptyshortname || ',' || cptyfullname || ','
       || dealdate || ',' || valuedate || ',' || payrectypeid || ','
       || amount || ',' || REFERENCE || ',' || actioned || ','
       || actioneddate || ',' || ourbankpay || ',' || ourbankrec || ','
       || theirbankpay || ',' || theirbankrec || ',' || settlemethodcode || ','
       || description || ',' || tradeflowtypeid || ',' || portcode || ','
       || linkref1 || ',' || linkref2 || ',' || linkref3
  FROM (SELECT ctf.datasource AS datasource, c.contno AS contno,
               c.tranno AS tranno, c.tranmode AS tranmode,
               c.trantype AS trantype, t.description AS trantypedescription,
               c.primarycurr AS primarycurr, c.bunit AS bunit,
               b.shortname AS bunitshortname, b.fullname AS bunitfullname,
               c.cpty AS cpty, y.shortname AS cptyshortname,
               y.fullname AS cptyfullname, ctf.dealdate AS dealdate,
               ctf.valuedate AS valuedate, ctf.payrectypeid AS payrectypeid,
               ctf.amount AS amount, ctf.REFERENCE AS REFERENCE,
               ctf.actioned AS actioned, ctf.actioneddate AS actioneddate,
               ctf.ourbankpay AS ourbankpay, ctf.ourbankrec AS ourbankrec,
               ctf.theirbankpay AS theirbankpay,
               ctf.theirbankrec AS theirbankrec,
               ctf.settlemethodcode AS settlemethodcode,
               tk.description AS description, 0 AS tradeflowtypeid,
               c.portcode AS portcode, c.linkref1 AS linkref1,
               c.linkref2 AS linkref2, c.linkref3 AS linkref3
          FROM tablec c INNER JOIN tablectf ctf ON c.contno = ctf.contno
               INNER JOIN tabletk tk
               ON c.tranno = tk.tranno AND c.tranmode = tk.tranmode
               INNER JOIN tablet t
               ON c.tranno = t.tranno
             AND c.tranmode = t.tranmode
             AND c.trantype = t.trantype
               LEFT OUTER JOIN tabley y ON c.cpty = y.cpty
               INNER JOIN tableb b ON c.bunit = b.bunit
         WHERE (c.tranno = 99)
           AND (tk.trancode = 1)
           AND TO_CHAR (valuedate, 'mm/dd/yyyy')
             = TO_CHAR (SYSDATE, 'mm/dd/yyyy'))
ORDER BY 1
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Actually, LK, I believe the code above will fail since the first SELECT has 31 expressions and they won't match up in the UNION with the second SELECT, which has only 2 resulting expressions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[LKBrwnDBA]

Dave, I copied & pasted the SELECT ...FROM (SELECT ...) part.

On the other hand, the solution I posted is equivalent to two columns:

SELECT '0000000000' sq, '{title text}'
UNION
SELECT TO_CHAR(rownum,'FM0000000000') sq, {concat cols}
ORDER BY 1;

Maybe the OP has still to match the column headers with the actual concatenated columns?









----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

I would however change the UNION to UNION ALL. Having a UNION means Oracle will sort and de-duplicate the output, which is clearly unnecessary. This means the header will probably end up being sorted with the rest of the text, so may not be at the top.

 

[LKBrwnDBA]

The header will be at the top, that is the reason for adding the sequence column (sq) + ORDER BY 1!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Beilstwh]

but if you use UNION ALL you dont need the sequence number or a sort.

Bill
Lead Application Developer
New York State, USA

 

[karluk]

Your ORA-12704 error strongly suggests that one or more of the columns in the concatenation "datasource || ',' || contno || ',' || ..." has as its datatype nchar or nvarchar2. Oracle doesn't allow mixing char and nchar datatypes this way.

Here is a simplified recreation of this error:

SQL> CREATE TABLE TEST_NVARCHAR2 (NVARCHAR2_VALUE NVARCHAR2(30));

Table created.

SQL> INSERT INTO TEST_NVARCHAR2  VALUES (N'ABC');

1 row created.

SQL> INSERT INTO TEST_NVARCHAR2  VALUES (N'XYZ');

1 row created.

SQL> INSERT INTO TEST_NVARCHAR2  VALUES (N'PDQ');

1 row created.

SQL> select case rownum when 1 then
  2  'NVARCHAR2_VALUE'
  3  ELSE
  4  '"' || NVARCHAR2_VALUE || '"' END
  5  FROM TEST_NVARCHAR2;
'"' || NVARCHAR2_VALUE || '"' END
                        *
ERROR at line 4:
ORA-12704: character set mismatch

As a fix, you could try using the cast function to convert the nchar columns to char:

SQL> select case rownum when 1 then
  2  'NVARCHAR2_VALUE'
  3  ELSE
  4  '"' || CAST(NVARCHAR2_VALUE AS VARCHAR2(30)) || '"' END
  5  FROM TEST_NVARCHAR2;

NVARCHAR2_VALUE
"XYZ"
"PDQ"

As Dagon points out, it's a little strange that you are replacing the first row of your query with a header, but maybe that's what you want.