ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested

[wchris]

Hello,

We get the error ora-01460 unimplemented or unreasonable conversion requested

with oracle release 1201000100 when calling a stored procedure with empty varchar2 parameters.

with oracle release 1102000400 the same call does not produce the error.


ABBENDUM : The error only occurs on Direct OraNet mode, not in OCI mode (tested with v10 and v12 client both are ok)

EDIT : (use oracle 12 and direct mode)
Empty string assignation to parameters triggers the issue when calling execproc

I'm trying to create a small example to reproduce the issue

[wchris]

Hi,

Can you please try this sample on an oracle 12c server and compile the project with delphi RIO 10.3 ?


Create an user TEST/TEST and change the server connection string inside the button event

Thank you

Code: Select all

CREATE OR REPLACE PROCEDURE test_journalhisto(
    jcx_no IN OUT varchar2,
    jcx_poste IN varchar2,
    jcx_utilos IN varchar2,
    jcx_utilatal IN varchar2,
    jcx_comm IN varchar2,
    mode_appel IN number,
    err_num OUT NUMBER,
    err_msg OUT VARCHAR2) IS

BEGIN
  jcx_no := 822431;
  COMMIT WORK;
EXCEPTION
     WHEN OTHERS THEN
     ROLLBACK WORK;
     err_num := SQLCODE;
     err_msg := jcx_no||' *'||jcx_poste||' *'||jcx_utilos||' *'||jcx_utilatal||' *'||jcx_comm||chr(13)||chr(10)||SUBSTR(SQLERRM,1,400);
END;
/

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, OraCall, Data.DB, DBAccess, Ora, MemDS;

type
  TForm1 = class(TForm)
    PROC_TEST: TOraStoredProc;
    OraButton1: TButton;
    OraSession: TOraSession;
    procedure OraButton1Click(Sender: TObject);
  private
    { Déclarations privées }
  public
    { Déclarations publiques }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.OraButton1Click(Sender: TObject);
begin
   with OraSession do
   begin
      Username := 'TEST';
      Server := 'ORA12SERVER:1521:ATAL';
      Password := 'TEST';

      AutoCommit := False;
      Options.Charset := 'WE8ISO8859P15';
      Options.DateFormat := 'DD/MM/YYYY';
      Options.DateLanguage := 'FRENCH';
      Options.Direct := True;
//      Options.Net := True;
      Options.NeverConnect := True;
      Connected := True;
   end;

   with PROC_TEST do
   begin
   ParamByName('JCX_NO').AsString := '';

   ParamByName('JCX_POSTE').AsString := 'Info';
   ParamByName('JCX_UTILOS').AsString := 'User';
   ParamByName('JCX_UTILATAL').AsString := '871';
   ParamByName('JCX_COMM').AsString := '';

   ParamByName('MODE_APPEL').AsInteger := 2;
   ExecProc;
   if ParamByName('ERR_NUM').AsInteger<>0 then ShowMessage('BAD')
   else ShowMessage('OK');
   end;
end;

end.

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 336
  ClientWidth = 293
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object OraButton1: TButton
    Left = 56
    Top = 208
    Width = 145
    Height = 73
    Caption = 'ORA-01460'
    TabOrder = 0
    OnClick = OraButton1Click
  end
  object PROC_TEST: TOraStoredProc
    StoredProcName = 'TEST_JOURNALHISTO'
    Session = OraSession
    SQL.Strings = (
      'begin'
      
        '  TEST_JOURNALHISTO(:JCX_NO, :JCX_POSTE, :JCX_UTILOS, :JCX_UTILA' +
        'TAL, :JCX_COMM, :MODE_APPEL, :ERR_NUM, :ERR_MSG);'
      'end;')
    Left = 160
    Top = 40
    ParamData = <
      item
        DataType = ftString
        Name = 'JCX_NO'
        ParamType = ptInputOutput
        Value = nil
      end
      item
        DataType = ftString
        Name = 'JCX_POSTE'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftString
        Name = 'JCX_UTILOS'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftString
        Name = 'JCX_UTILATAL'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftString
        Name = 'JCX_COMM'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftFloat
        Name = 'MODE_APPEL'
        ParamType = ptInput
        Value = nil
      end
      item
        DataType = ftFloat
        Name = 'ERR_NUM'
        ParamType = ptOutput
        Value = nil
      end
      item
        DataType = ftString
        Name = 'ERR_MSG'
        ParamType = ptOutput
        Value = nil
      end>
    CommandStoredProcName = 'TEST_JOURNALHISTO'
  end
  object OraSession: TOraSession
    Options.DateFormat = 'DD/MM/RR'
    Options.KeepDesignConnected = False
    AutoCommit = False
    Left = 74
    Top = 41
  end
end

[MaximG]

Please explain, what NLS parameters the used Oracle 12.1 has ( in order for you to get them, use the following query :

Code: Select all

select * from v$nls_parameters

)

[wchris]

SQL> select * from v$nls_parameters;

Code: Select all

PARAMETER                                                        VALUE                                                        CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_LANGUAGE                                                     FRENCH                                                            0
NLS_TERRITORY                                                    FRANCE                                                            0
NLS_CURRENCY                                                     €                                                                 0
NLS_ISO_CURRENCY                                                 FRANCE                                                            0
NLS_NUMERIC_CHARACTERS                                           ,                                                                 0
NLS_CALENDAR                                                     GREGORIAN                                                         0
NLS_DATE_FORMAT                                                  DD/MM/RR                                                          0
NLS_DATE_LANGUAGE                                                FRENCH                                                            0
NLS_CHARACTERSET                                                 WE8ISO8859P15                                                     0
NLS_SORT                                                         FRENCH                                                            0
NLS_TIME_FORMAT                                                  HH24:MI:SSXFF                                                     0
NLS_TIMESTAMP_FORMAT                                             DD/MM/RR HH24:MI:SSXFF                                            0
NLS_TIME_TZ_FORMAT                                               HH24:MI:SSXFF TZR                                                 0
NLS_TIMESTAMP_TZ_FORMAT                                          DD/MM/RR HH24:MI:SSXFF TZR                                        0
NLS_DUAL_CURRENCY                                                €                                                                 0
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                         0
NLS_COMP                                                         BINARY                                                            0
NLS_LENGTH_SEMANTICS                                             BYTE                                                              0
NLS_NCHAR_CONV_EXCP                                              FALSE                                                             0

19 lignes sélectionnées.

[MaximG]

Thank you for the information. We have reproduced the problem and fixed the bug. The fix will be included in the next ODAC build. Currently, we can send you a night build of ODAC with the fix. For this, please specify your license number and the exact version of Delphi you are using via the e-support form ( https://www.devart.com/company/contactform.html )

[wchris]

When using the contact form we get a mail saying

Could not deliver message to the following recipient(s):

Failed Recipient: *masked*@ch-w*masked*rg.fr
Reason: Remote host said: 554 5.7.1 <*masked*@ch-w*masked*g.fr>: Sender address rejected: Access denied

did you get the nightly build request ?
Thank you

[MaximG]

We received your request and sent you a link to download the night build with the necessary changes on your e-mail.