Oracle protocol and string params

[MJFShark]

Hi All!

I'm using some input and output params in a PL/SQL anonymous block and I'm seeing some strange issues. It appears that ptInput params that are strings need to be ftWideString type, but ptOutput params need to be ftString type (or they come out as incorrectly encoded strings with embedded #0 (utf16 as ansi basically.)) Using ptInputOutput params are also affected (and I don't think there's a workaround since they have to be ftWideString on input and ftString on output.) Here's a console program that shows the issue. It won't error, but will show the incorrect encoding of "ParamOut". Uncomment the indicated line to see the other error. I'm thinking that this is a bug, but wanted to check here before creating a ticket.

Code: Select all

program ZeosParamTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes,
  Data.DB,
  ZDbcIntfs, ZConnection, ZDataset;

var
  ZConn: TZConnection;
  ZQuery: TZQuery;
  Param: TParam;
begin

  try
    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'oracle';
    ZConn.User := 'scott';
    ZConn.Password := 'tiger';
    ZConn.Database := '//192.168.1.100/orcl.local';
    ZConn.LibraryLocation := 'C:\Oracle\19\32\oci.dll';
    ZConn.AutoCommit := False;
    ZConn.TransactIsolationLevel := tiReadCommitted;
    ZConn.Connect;

    ZQuery := TZQuery.Create(nil);
    ZQuery.Connection := ZConn;
    // Note that we have to double the :: on := to escape it from the parser.
    ZQuery.SQL.Text := 'BEGIN :ParamOut ::= ''Input: ''|| :ParamIn; END;';

    Param := ZQuery.ParamByName('ParamIn');
    Param.ParamType := ptInput;
    Param.AsString := 'Testing';  // Sets datatype to ftWideString which works
    // Param.DataType := ftString;   // UnComment this to see the error.

    Param := ZQuery.ParamByName('ParamOut');
    Param.ParamType := ptInputOutput;
    // Param.DataType := ftString;       // Correct output
    Param.DataType := ftWideString;   // Looks like utf-16 as ansi.

    ZQuery.ExecSQL;

    // Write out the param values.
    for var  i := 0 to ZQuery.Params.Count - 1 do
    begin
      Param := ZQuery.Params[i];
      Writeln(Param.DisplayName + ': ' + Param.AsString);
    end;

    Writeln('Done');

    ZConn.Disconnect;
    ZQuery.Free;
    ZConn.Free;

  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Write('Press Enter to quit...');
  ReadLn;

end.

Thanks!
-Mark

[EgonHugeist]

Hello Mark,

thank's for the details. Seems all the features i made are not perfect @all.
Should by fixed by https://sourceforge.net/p/zeoslib/code-0/6972/.
Please report your findings.
Btw. It's a bad practice using Variable types (strings/bytes) without spezify the maximum size for drivers like Oracle. OCI is still not able to describe the parameter. Thus ZDBC is doing some guesswork now and uses the OCI max limit of 4000*BytesPerChar + Length indicator. In case of "utf8mb4" it's a amount of ~16kb for both parameters. JFYI

[MJFShark]

Thank you! Tested and it's fixed in all my tests except the following which I think is a separate issue. Thank you for the tip on param size!

Code: Select all

   // Just testing "returning into" clause which can be used on insert, update or delete statements.
    ZQuery.SQL.Text := 'DELETE FROM EMP WHERE EMPNO = 1 RETURNING ENAME INTO :BIND3';

    Param := ZQuery.ParamByName('BIND3');
    Param.ParamType := ptOutput;
    Param.DataType := ftString;
    Param.Size := 200;

    ZQuery.ExecSQL;

This fails with: Cannot retrieve Resultset data and doesn't seem to be datatype specific (if I try to return empno i get the same error.)

-Mark

[MJFShark]

Thank you for the new fix related to the RETURNING INTO clause! Tested and verified the fix. I've also noticed one other issue. If a stored procedure has a VARCHAR2 parameter, the "paramcheck" TParam DataType will be defined as ftWideMemo (which seems correct since they could be 32k max.) However if that type isn't changed before executing it will result in:

SQL Error: ORA-01458: invalid length inside variable character string

Note: This probably isn't worth mentioning, but using Param.AsString := 'blah'; will reset the Param.DataType to ftWideString so to test with an initial value set the Param.Value directly. Thanks again!

-Mark

[EgonHugeist]

According the invalid sizes:
Could you plz test https://sourceforge.net/p/zeoslib/code-0/7073/

[MJFShark]

I've tested the fix and it seems to work perfectly. Thanks!

-Mark