ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
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
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
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
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
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
Re: ODAC 10.3.9 AV inside execproc ora-01460 unimplemented or unreasonable conversion requested
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
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
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
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
We received your request and sent you a link to download the night build with the necessary changes on your e-mail.