Anyone on heer a database expert particulary Oracle?

[greatwhite]

Wondering if anyone on here is a database expert for Oracle.

[werner.reiche]

Certified DBA and SQL expert.
But I don't use it much anymore and haven't moved beyond 11.2.

[greatwhite]

This would be the same still.

I have a visual Studio Application that uploads a Text file as a Blob the text file contains french characters. When I download the file again the french characters are corrupted.
My understanding is Visual Studio is automatically set up for encoding.

I'm just the DBA for the data so there is another Oracle DBA above me that controls the Oracle instance he said the database is set for UTF 8 .

So I am trying to understand why these blobs are becoming corrupt. Is my application or is it Oracle.

[greatwhite]

Actually I just check the setting in Oracle and I get this.

NLS_CHARACTERSET AL32UTF8 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0

[greatwhite]

SELECT *
FROM v$nls_parameters
WHERE parameter LIKE '%CHARACTERSET';

[greatwhite]

So It seems Oracle is set correctly, has to be the application, which means all of our Blob loaders are scrambling the french characters.

Thank goodness they were not built by me. Legacy apps,.

[werner.reiche]

Is the column storing the file a blob or clob?
Assuming it's a blob, it's binary data and as such is not related to the character set for the database.
That would mean it is somewhere in your application.
I assume you are using an ODBC connector.
When you read the blob, do you read it as a blob or do you use the UTL functions to convert it to a string?

[greatwhite]

If conn.State = ConnectionState.Open Then
Console.WriteLine("Connected to database!")


' provide read access to the file
Dim Fs As FileStream = New FileStream(SourceLoc,
FileMode.Open, FileAccess.Read)

' Create a byte array of file stream length
Dim ImageData As Byte()
ReDim ImageData(Fs.Length)

'Read block of bytes from stream into the byte array
Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))

'Close the File Stream
Fs.Close()


SQLStr = SQLStr & " Update " & TableName & " Set BIN_DATA = :1 where FILENAME = :2"


' Set command to create Anonymous PL/SQL Block
'Dim cmd As OracleCommand = New OracleCommand()
Dim command As New OracleCommand() '(StrSQL, conn)

command.CommandText = SQLStr
command.Connection = conn
' Since executing an anonymous PL/SQL block, setting the command type
' as Text instead of StoredProcedure
command.CommandType = CommandType.Text

Dim param1 As OracleParameter = command.Parameters.Add("BIN_DATA", OracleDbType.Blob) 'BIN_DATA (BLOB)
param1.Direction = ParameterDirection.Input
' Assign Byte Array to Oracle Parameter
param1.Value = ImageData

Dim param2 As OracleParameter = command.Parameters.Add("FILENAME", OracleDbType.Varchar2) 'FILENAME
param2.Size = 50
param2.Direction = ParameterDirection.Input
' Assign Byte Array to Oracle Parameter
param2.Value = File



command.ExecuteNonQuery()
command.Dispose()

[greatwhite]

Yes it is a BLOB field

The files being uploaded are text files. I didn't build all this Database legacy