Devart Forums

Posted: **Tue 10 Nov 2020 13:12**

hello,

I 'm using ORACLE with UniDAC and I ran into a not expected behaviour.

here is the demo ORACLE procedure:

create or replace procedure demo_proc
(
  o_error_msg   out varchar2
)
as
begin
  o_error_msg := null;
end;
/

declare
  v_error_msg   varchar2(10 char);
begin
  demo_proc(v_error_msg);
  dbms_output.put_line(coalesce(v_error_msg, 'null'));
end;
/

[output]
null

here is the Delphi component:

Code: Select all

  object spDemo: TUniStoredProc
    StoredProcName = 'DEMO_PROC'
    SQL.Strings = (
      'begin'
      '  DEMO_PROC(:O_ERROR_MSG);'
      'end;')
    Connection = conMain
    Transaction = trMain
    Left = 205
    Top = 80
    ParamData = <
      item
        DataType = ftString
        Name = 'O_ERROR_MSG'
      end>
    CommandStoredProcName = 'DEMO_PROC'
  end

and here is the Delphi code:

Code: Select all

  spDemo.Execute;

  if spDemo.ParamByName('O_ERROR_MSG').IsNull then
    ShowMessage('O_ERROR_MSG is null')
  else
    ShowMessage('O_ERROR_MSG is empty string');

As result I would expect the IsNull to be True, but it is not.
I changed the TUniStoredProc.Options.SetEmptyStrToNull to True, no luck.
When I changed the param's DataType to ftVariant, it suddenly started to work properly, but this is not an acceptable solution.

My problem is when you use ORACLE the empty strings should behave as null values, especially when I explict give an out parameter NULL value I except to retrieve that value on the Delphi side.
When I did the test with a NUMBER out type, it behaved as it should be, the IsNull became True, this is how it supposed to work I guess.

Posted: **Thu 12 Nov 2020 12:17**

What version of UniDAC are you using? The issue was fixed in the latest version of UniDAC (8.3.2).

Posted: **Thu 19 Nov 2020 22:11**

Thanks, we upgraded from 7.4.12 to 8.3.2, unfortunatelly after the upgrade when we want to connect we get an error: "NET: invalid port:"
We haven't chaged our source code, only th upgraded happened. Checked any leftover dcp/bpl, found none, all the latest.

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0
Delphi 7
update: tried on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production, works fine

Here is the component, removed the sensitive data.

Code: Select all

  object conDesignTime: TUniConnection
    ProviderName = 'Oracle'
    Port = 1521
    SpecificOptions.Strings = (
      'Oracle.Direct=True')
    Options.KeepDesignConnected = False
    DefaultTransaction = traDesignTime
    Username = '***************'
    Server = '***********-*********.**:1521/*******'
    LoginPrompt = False
    Left = 305
    Top = 200
    EncryptedPassword = '***************'
  end

Posted: **Fri 20 Nov 2020 16:01**

We've reproduced the issue and fixed it. The fix will be included in the next build of our product. As a workaround, we can send you a night UniDAC build including the required changes. For this provide us with your license number and IDE version you are interested in For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

Posted: **Fri 20 Nov 2020 20:12**

Thanks a lot, your quick solution is highly appreciated!
My collegue will contact you soon with the required licence.

Posted: **Sat 13 Feb 2021 21:12**

We will be waiting for additional information about the described issue

Devart Forums

TUniStoredProc's param IsNull property not working as expected

TUniStoredProc's param IsNull property not working as expected

Re: TUniStoredProc's param IsNull property not working as expected

Re: TUniStoredProc's param IsNull property not working as expected

Re: TUniStoredProc's param IsNull property not working as expected

Re: TUniStoredProc's param IsNull property not working as expected

Re: TUniStoredProc's param IsNull property not working as expected