ORA-1036 on manually created parameter.

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
psbo
Posts: 6
Joined: Mon 01 Apr 2019 08:01

ORA-1036 on manually created parameter.

Post by psbo » Wed 31 Jul 2019 11:42

Hello.
I have a problem when opening a TUniQuery.
This Query has 3 parameters that the TUniQuery detect (PAramCheck := true).
But when I passes values to each param with a managed function, I passe a third param with name that not exists on the ParamList Query.
I modify the 3rd param dynamically and I open the Query.
Here an Exception is raised: ORA 1036
this is an exemple :

Code: Select all

//....
function fAddParameter(var poQuery: TUniQuery; psName: String; poDataType: TFieldType; pvValue: Variant): boolean;
var
  LengthValue: Integer;
  Index, I: Integer;
  pParam: TParam;
begin

  Result := False;
  poQuery.ParamCheck := False;
  index := -1;
  pParam := nil;
  if psName = '' then
    Exit;

  if IsEmptyOrNull( pvValue ) then
    pvValue := NULL;

  for I := 0 to poQuery.ParamCount-1 do
  begin
    if (poQuery.FindParam(psName) = nil) and (IsEmptyOrNull(poQuery.Params[I].Value)) then
    begin
      index := I;
      Break;
    end;
  end;

  if (poQuery.FindParam(psName) = nil) and index <> -1 then
  begin
    poQuery.Params.Delete(index);
    pParam := poQuery.Params.AddParameter;
	pParam.index := Index; 	
    pParam.Name := psName;
    poQuery.Params.AddParam(pParam);
  end
   
  poQuery.ParamByName(psName).ParamType := ptInput;
  poQuery.ParamByName(psName).DataType  := poDataType;
  poQuery.ParamByName(psName).Value     := pvValue;

  Result := True;

end;
//....
procedure TForm6.Button1Click(Sender: TObject);
begin
  qrSQL.Close;
{ // SQL text.
SELECT * FROM TIERS 
WHERE TIERS_FORMJ_ID = :jID
AND TIERS_LANGU_ID = :gID
AND TIERS_SCGEO_ID = :sID
}
  qrSQL.SQL.Clear;
  qrSQL.SQL.Text := mmSQL.Text ;
//mmRES.Lines.Add( IntToStr (qrSQL.ParamCount) );

  fAddParameter( qrSQL, 'jID'  , ftInteger,  1817 );
  fAddParameter( qrSQL, 'gID' , ftInteger,  1 );
  fAddParameter( qrSQL, 'x3'   , ftInteger,  1 );
  qrSQL.Open;

  pAddToMemo(mmRES, '-------------------------------------------------------------');
end;



It Change the paramName by raises an ORA-1036.

Please Help.

MaximG
Devart Team
Posts: 1438
Joined: Mon 06 Jul 2015 11:34

Re: ORA-1036 on manually created parameter.

Post by MaximG » Fri 02 Aug 2019 15:38

To investigate the issue, we need a complete code base of your test project, including all functions used in your project, e.g. IsEmptyOrNull. Please send it to us via the e-support form : https://www.devart.com/company/contactform.html

psbo
Posts: 6
Joined: Mon 01 Apr 2019 08:01

Re: ORA-1036 on manually created parameter.

Post by psbo » Mon 05 Aug 2019 07:55

Hello.
I have send you a complete exemple with SQL creation table.
I've send this mail with email : d******@saverglass.com
since we are your client.


Thanks.

MaximG
Devart Team
Posts: 1438
Joined: Mon 06 Jul 2015 11:34

Re: ORA-1036 on manually created parameter.

Post by MaximG » Wed 11 Sep 2019 15:37

Am I right, that the application that you've sent us is intended to demonstrate the inability to use a parameter named sThisParamIsHeigherThenThirteenCharacters' ?
Please note that the maximum parameter name length cannot exceed 30 characters for an Oracle database:
https://docs.oracle.com/cd/E18283_01/ti ... /names.htm

psbo
Posts: 6
Joined: Mon 01 Apr 2019 08:01

Re: ORA-1036 on manually created parameter.

Post by psbo » Tue 17 Sep 2019 09:03

No not for that.
I know that the max characters in a parameter is 30.
I want to tell you that If I name a parameter in my SQL as 'param1' then I declare it with the function fAddParameter as 'paramt1' then I want from the component to delete the real parameter and replace it with the name I force on it.

Exemple :

Code: Select all

  QueryTR.SQL.Text := 'SELECT *FROM TIERS WHERE TIERS_ID = :t1';
  fAddParameter(QueryTR, 'tC', ftInteger; 55);
  QueryTR.Open;
When calling fAddParameter, it will look that 'tC' does not exists, then It Create IT after deleting the 't1'.
With Firebird this code works but not in Oracle.

Thanks

ViktorV
Devart Team
Posts: 2856
Joined: Wed 30 Jul 2014 07:16

Re: ORA-1036 on manually created parameter.

Post by ViktorV » Fri 20 Sep 2019 12:57

Thank you for the information. We will investigate this UniDAC behavior and let you know the result.

psbo
Posts: 6
Joined: Mon 01 Apr 2019 08:01

Re: ORA-1036 on manually created parameter.

Post by psbo » Thu 10 Oct 2019 13:53

Heello,
Did you solved the problem ??
I'm still waiting.

Thanks you.

MaximG
Devart Team
Posts: 1438
Joined: Mon 06 Jul 2015 11:34

Re: ORA-1036 on manually created parameter.

Post by MaximG » Fri 11 Oct 2019 08:41

We've tested our components according to your description of the issue. In your code, you use different names for the same parameter in the query and during parameter binding. Such behavior is not correct in terms of Oracle. The same name must be used in the query and during parameter binding. Otherwise, you'll get a corresponding error message.
As such, UniDAC behaves correctly in your code example.

Post Reply