TOraSession ORA-01460 error

[jjeffman]

Hello,

I am using ODAC components version 9.7.25 on C++ Builder 6.0 Professional Edition.

I have built a procedure inside a package which is responsible for adding records to a table that has a CLOB column.

The procedure works fine on my local Oracle 11g instance but not on my client 11g system.

It raises an exception with code ora-01460 and I have not any explicit date or number conversion.

Code: Select all

-- Table structure
Create table S4MED_EVENTO_MDM (
	EVENTO_ID Number NOT NULL ,
	DATAHORA Timestamp(3) NOT NULL ,
	MENSAGEM NVarchar2(600) NOT NULL ,
	SEVERIDADE Number(5,0) Default 0 NOT NULL ,
	GRUPO NVarchar2(20),
	TIPOEVENTO NVarchar2(15),
	ACKTIME Date,
	NOMEARQ NVarchar2(50),
	TEXTOARQUIVO Clob,
	APUSER Number(22,0),
	MEDICAO Number(22,0),
 Constraint pk_S4MED_EVENTO_MDM primary key (EVENTO_ID) 
	USING INDEX TABLESPACE nnnnnnn
) 
TABLESPACE ddddddd
/
-- procedure inside S4MED package
PROCEDURE addEventoMed( p_text   IN NVARCHAR2  -- Texto da mensagem
                        , p_sever IN INTEGER   -- Grau de severidade
                        , p_group IN NVARCHAR2 -- Grupo ao qual pertence o evento
                        , p_tipo  IN NVARCHAR2 -- Tipo do evento
                        , p_file  IN NVARCHAR2 -- Nome do arquivo lido
                        , p_ftext IN NVARCHAR2 -- Conteúdo do arquivo lido ao qual está associado o evento
                        , p_medicao IN INTEGER -- Medição a qual se refer o evento
                        , p_result OUT NUMBER ) AS
                      
   lob_loc CLOB;                      

BEGIN
  
   BEGIN
     INSERT INTO S4MED_EVENTO_MDM
            (DataHora
            ,Mensagem
            ,Severidade
            ,Grupo
            ,TipoEvento
            ,NomeArq
            ,TextoArquivo
            ,MEDICAO)
     VALUES
            (SYSTIMESTAMP
            ,p_text
            ,p_sever
            ,p_group
            ,p_tipo
            ,p_file
            ,EMPTY_CLOB()
            ,p_medicao) RETURNING TextoArquivo INTO lob_loc;
    
     IF p_ftext IS NOT NULL THEN
       DBMS_LOB.WRITE(lob_loc, LENGTH(p_ftext),
                       1, p_ftext);
     END IF;
                          
     SELECT seq_eventomdm.currval INTO p_result FROM sys.dual;

          
   EXCEPTION
     WHEN OTHERS THEN RAISE;
   END;
  
END addEventoMed;
/

The TOraSession component is configured using the default options, except Options->Direct = true and Options->UseUnicode = true.

I have copied the database charset ( WE8ISO8859P1 ) to the connection but it did not solve the issue.

What could be the reason of this error ?

Kind regards.

Jayme Jeffman

[jjeffman]

Hello.

I have found out that the problem is related to the connection charset.

The database is configured using the WE8ISO8859P1 charset and I am passing unicode values with special Portuguese characters to store in NVARCHAR2 columns.

Is there any documentation on how to deal with different Oracle charsets using ODAC ? I know I have already had troubles on this matter and the solution was not very elegant.

Kind regards.

Jayme Jeffman

[AlexP]

Hello,

Try using NCLOB instead of CLOB in the table.

[jjeffman]

Hello Alex

Thank you very much for answering me.

I have made the change you have suggested but I am still getting ORA-01460 error:

Code: Select all

06/07/2016 14:03:39.152 ORA-01460: unimplemented or unreasonable conversion requested

I have read in the ODAC Help that setting up the "Charset" property of TOraSession has only meaning when connecting through Oracle 8i client. Is that true?

The last time I have connected to try your suggestion I have configured TOraSession properties Unicode = true and Direct = true. I did not set up the Charset property.

Kind regards.

Jayme Jeffman

[AlexP]

You should also modify the variable type in the procedure and set the National property of the parameter to true.

Code: Select all

SP.Params[x].National := True;
SP.Params[x].AsWideString := 'Medicao';

[jjeffman]

Alex, thank you for answering me.

I have set the National property of the TOraStoredProc parameters to true and assigned its values through the AsWideString property.

Changing the column data type from Clob to NClob made the data not to be saved, so I have changed it back to Clob and setting the parameter National property to true eliminated the error ORA-01460.

I am still using direct connection, without Oracle client, and everything is working fine.

Thank you very much for the help.

Kind regards

Jayme Jeffman

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.