Page 1 of 1
ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Mon 28 Jan 2019 15:26
by 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
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Tue 29 Jan 2019 12:13
by 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
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Tue 29 Jan 2019 16:55
by MaximG
Please explain, what NLS parameters the used Oracle 12.1 has ( in order for you to get them, use the following query :
)
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Tue 29 Jan 2019 17:35
by 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.
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Wed 30 Jan 2019 13:09
by 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 )
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Fri 01 Feb 2019 09:28
by 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
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
Posted: Mon 04 Feb 2019 15:53
by MaximG
We received your request and sent you a link to download the night build with the necessary changes on your e-mail.