Input Tool, special characters replaced for CLOB column in DB

[rp19]

I've a Clob column in a database table which has special characters e.g "(double quotes) and '(single quotes)
When I input them in Alteryx via Input tool or IN-DB tool these get replace by blanks or?(question mark character)

----------Sample text----------------

Input in DB - Hell’o “How”are you’
Input in Alteryx - Hell?o How?are you?
---------------------------------------

I've already tried checking "Force sql w char support", but it results in an error m_OCILobRead Error: ORA-24806: LOB form mismatch.
I've also tried changing the encoding from Localization to Unicode UTF-8, but this also doesn't solve the error.

Is there any way I can retain special characters as it is ?

[apathetichell]

what database and driver are you using? my suggestion would be to check the driver config in odbc 64.

[Bhavyapaliwal20]

If this is the only issue you're facing, you can try using the Multi-Row or Multi-Field Formula tool in Alteryx to replace the '?' with the correct characters, such as single quotes (' " ') or double quotes (" " ").

[apathetichell · Accepted solution]

More information on the driver/db can help. This is most likely a conflict between something in the driver and your database. I see this with certain drivers where the driver is set for one encoding and the DB has another.

[rp19]

Driver : Oracle Insta Client, connecting to db via oci, version 12.1.
DB : Oracle Database 19c

[rp19]

replacing is not possible, as there are multiple characters getting replaced by some different characters with a possibility of new characters coming in future.

[Bhavyapaliwal20]

You're right, but in your approach, you separated the process, which may be cause issues in the future. Instead, it's more efficient to use the replace function specifically in areas where it's unnecessary to retain certain parts, like in this case. Based on my analysis, there's no need for 'ADDRESS_LIST.0.' in that particular column.

[rp19]

I've also tried using Python tool to connect to db for fetching data :

from ayx import Alteryx
import cx_Oracle
from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('oracle+cx_oracle://username:password@hostname:portnumber/servicename')
con = engine.connect()
outpt = con.execute("SELECT 1 FROM DUAL")
df = pd.DataFrame(outpt.fetchall())
df.columns = outpt.keys()
print(df.head())
con.close()

But this results into "ORA-12505: TNS:listener does not currently know of SID given in connect descriptor"

[rp19 · Accepted solution]

Problem indeed was with the drivers. 

OCI couldn't understand special chars, but when I shifted the connection to ODBC and checked Force sql w char support, Input tool understands the characters now.

[apathetichell]

Can you mark my suggestion to check the driver as a solution?