Devart Forums

Apparel21

Fantastic, thanks for the update!

Apparel21

Thanks for the advice; it looks like you have the same need as we do.

We've worked around it for now by reloading the data in the sorted order, so when we set it back to the default sort order, it displays the way we want it.
I think we're only getting away with it because our data is typically small, and we're just using a TVirtualTable (if it was linked to a database this would be a different story).

Code: Select all

Stream := TMemoryStream.Create;
try
  MemoryTable.SaveToStream(Stream, True, True);
  Stream.Position := 0;
  MemoryTable.Clear;
  MemoryTable.IndexFieldNames := '';
  MemoryTable.LoadFromStream(Stream, True);
finally
  Stream.Free;
end;

Apparel21

Hi, we use the TVirtualTable, and set the IndexFieldNames property to do sorting.

In ODAC 8.5, setting the IndexFieldNames property to 'NAME' would sort the table by name. Setting the IndexFieldNames property back to '' stopped sorting, and did nothing else.

In ODAC 9.7, setting the IndexFieldNames property to 'NAME' would sort the table by name. Setting the IndexFieldNames property back to '' reverts the sort of the data back to the ORIGINAL sort order, which is not what we want.

How do we "turn off" sorting? Having IndexFieldNames set to '' used to mean no sorting, not original sorting.

To get a clearer idea of what we are trying to achieve, we want to sort the grid when the user clicks the column title, but any new entries should be appended to the end (not be sorted) until the user sorts the grid again.

Thanks in advance

Apparel21

MaximG wrote:Try to specify the type of the 'StringParameter' when using it:
Code: Select all
 LSql.ParamByName('StringParameter').ParamType := ptInputOutput; 

This is exactly what we wanted to avoid - having to edit 10+ years of code. We can obviously fix it in a number of ways however we are going to have a massive task on our hands.

More information from the object inspector in XE after setting SQL:
LSql.ParamByName('StringParameter'),r
(FCollection:$2B80C80; FID:0; FParamRef:nil; FNativeStr:''; FData:Unassigned; FPrecision:0; FNumericScale:0; FNull:True; FName:'StringParameter'; FDataType:ftUnknown; FBound:True; FParamType:ptUnknown; FSize:0; FParamObjectClass:TParamObject; FParamObject:nil; FNational:False)

This works with FParamType set to ptUnknown in XE.

Maybe we're missing a setting that works with ParamCheck := True that will determine this information via metadata from the database?

It just works in XE but now doesn't in Seattle.

Apparel21

Hi

We are upgrading our source code from Delphi XE to Delphi 10 Seattle and at the same time we are upgrading the ODAC component.

Delphi XE is using odac80d15pro.exe
and
Delphi 10 Seattle is using odac96d23pro.exe

We are now receiving Oracle errors with some of our IN OUT arguments in procedures and package procedures. We currently don't pre-allocate buffers anywhere for parameters and there hasn't been a problem with this in the Delphi XE version.

It appears now in Delphi 10 Seattle that if the parameter value being passed IN has less memory allocated than what is coming OUT, an exception is raised.

Please note this still works in XE but no longer works in Seattle.

Here is a sample to reproduce.

Delphi XE successfully allocated a string and returned the correct result from the procedure whereas the following exception was thrown in Seattle: "Project raised exception class EOraError with message 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

Oracle Procedure Source Code:

Code: Select all

create or replace procedure ParameterTest(StringParameter IN OUT VARCHAR2) is
begin
  StringParameter := 'My value of 25 characters';
end;

Delphi Source Code:

Code: Select all

procedure TestParams;
var
  LConnection: TOraSession;
  LSql: TOraSql;
begin
  LConnection := TOraSession.Create(Self);
  try
    LConnection.Server := '###';
    LConnection.Username := '###';
    LConnection.Password := '###';
    LConnection.Connect;
    
    LSql := TOraSql.Create(Self);
    try
      LSql.Connection := LConnection;
      LSql.SQL.Add('BEGIN ParameterTest(:StringParameter); END;');
      LSql.ParamByName('StringParameter').AsString := 'In'; // Note: this is now seen as a two character buffer for the output but in XE this was not a problem.
      LSql.Execute;
      MessageBox(Handle, PWideChar(LSql.ParamByName('StringParameter').AsString), 'Out', 0);
 
    finally
      LSql.Free;
    end;
 
  finally
    LConnection.Free;
  end;
end;

Is something now broken or has there been an intentional breaking change?

Cheers

Apparel21

Hi again,

Any news on whether this will be modified or not?

Cheers,
Jonathan

Apparel21

Thanks for the quick reply! I look forward to hearing how it goes.

Apparel21

Hi everyone,

In our applications we typically use a non-direct method of connecting, and the MACHINE column of V$SESSION is correctly populated with the domain and terminal, as below:

Code: Select all

var oracleConnection1 = new OracleConnection();
oracleConnection1.Direct = false;
oracleConnection1.Server = "DEV";
oracleConnection1.UserId = "MYSCHEMA";
oracleConnection1.Password = "xxx";

MACHINE = 'MYDOMAIN\MYLAPTOP'

However when I use Direct Mode the MACHINE column just has the terminal name:

Code: Select all

oracleConnection2.Direct = true;
oracleConnection2.Server = "Server01";
oracleConnection2.Port = 1521;
oracleConnection2.Sid = "DEV";
oracleConnection2.UserId = "MYSCHEMA";
oracleConnection2.Password = "xxx";
oracleConnection2.Open();

MACHINE = 'MYLAPTOP'

Additionally, I also noticed that the process was 00000:00000.

We are unable to use the non-direct mode in this particular case, but we rely on the values in the MACHINE column for other processes.

Does anyone know any workarounds or fixes? We're running C# against Devart.Data.Oracle.dll v 8.5.602.0.

Thanks,
Jonathan

Apparel21

I have a pl/sql package with a procedure declared like this:

Code: Select all

CREATE OR REPLACE PACKAGE TableExample IS
  TYPE DictType IS TABLE OF CLOB INDEX BY VARCHAR2(256);
  PROCEDURE Test(sInputParameters IN DictType);
END TableExample;

My c# code is this:

Code: Select all

using (var connection = new OracleConnection())
{
    connection.ConnectionString = "User ID=user;Password=pwd;Data Source=dev";
    connection.Open();
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "TableExample.Test";
        cmd.Parameters.Clear();
        cmd.Prepare();
        ....
    }
}

When the cmd.Prepare() is called, Devart gives this error:
Type "OracleDbType.Clob" cannot be used in PL/SQL table parameter.
Stack trace looks like this:
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0)
at Devart.Data.Oracle.OracleParameter.set_ArrayLength(Int32 value)
at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
at Devart.Common.DbCommandBase.CreateSql()
at Devart.Common.DbCommandBase.get_Sql()
at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.Prepare()

Devart.Data.Oracle version is 8.2.90.0
Oracle Database 11g Release 11.2.0.3.0

Apparel21

We have upgraded from version 6.10.141.0 to version 6.50.237.0.
Certain types of connection errors now give a generic Connection pool timeout error, instead of the real connection problem.

If an invalid username or password is provided, we expect Devart.Data.Oracle.Exception "ORA-01017: invalid username/password; logon denied" to be the exception.

Instead we get a System.InvalidOperationException
{"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."}

Note- the correct error is thrown is pooling is not used, but we have always used pooling and this used to work.

Can anyone suggest a solution for this?
Thanks for any answers!

example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Devart.Data.Oracle;

namespace ConsoleApplication1
{
///
/// This class provides the internal error codes for known connection failures.
///
public class OracleErrors
{
///
/// The Database name could not be found in the TNS Names file.
///
public const int CouldNotResolveIdentifier = 12154;

///
/// The username and or password was invalid. Login was denied.
///
public const int InvalidUserNamePassword = 1017;

///
/// The value found in the SID fragment of the TNS Names entry could not be found.
///
public const int UnknownSID = 12505;

///
/// The value found in the Host fragment of the TNS Names entry could not be found.
///
public const int UnknownHost = 12545;

///
/// No listener could be found on the specified port.
///
public const int NoListenerFound = 12541;

///
/// Connection failed.
///
public const int TnsInvalidArguments = 12532;

///
/// Connection failed.
///
public const int TnsPermissionDenied = 12546;
}

class Program
{
static void Main(string[] args)
{
OracleConnection Connection = new OracleConnection("User ID=name;Password=pwd;Data Source=DATABASE");

OracleDependency.Enabled = false;
try
{
Connection.Open();
}
catch (Exception e)
{
string ConnectionFailureReason = e.Message;
if (e is OracleException)
{
OracleException ex = (e as OracleException);
switch (ex.Code)
{
case OracleErrors.CouldNotResolveIdentifier:
ConnectionFailureReason = "Could Not Resolve Identifier";
break;
case OracleErrors.UnknownSID:
ConnectionFailureReason = "UnknownSID";
break;
case OracleErrors.UnknownHost:
ConnectionFailureReason = "UnknownHost";
break;
case OracleErrors.NoListenerFound:
ConnectionFailureReason = "NoListenerFound";
break;
case OracleErrors.TnsInvalidArguments:
ConnectionFailureReason = "TnsInvalidArguments";
break;
case OracleErrors.TnsPermissionDenied:
ConnectionFailureReason = "TnsPermissionDenied";
break;
case OracleErrors.InvalidUserNamePassword:
ConnectionFailureReason = "InvalidUserNamePassword";
break;
}
}

throw new Exception(ConnectionFailureReason);
}
}
}
}

Apparel21

Thanks, this has fixed the problem for us!

Apparel21

Any news on this problem? I have emailed you support directly also on [email protected], but have not had any response. This is preventing us from releasing our .net products using OraDirect.net . Please help!

Apparel21

We are using OraDirect.NET version 4.30.20.0 for our .net projects, and ODAC version 6.00.0.4 for our VCL projects.

In the odac (VCL) version, there is a problem with an extra listening port being opened for OCIEvents which causes problems with the firewall.
This is fixed by setting OCIEvents := False in some initialization code.
(see this forum in the ODAC group...
http://devart.com/forums/viewtopic.php?p=38320#38320 )

The OraDirect.NET library also has this problem of opening the extra port. How can I stop this happening ?

Apparel21

Sorry, I do mean OraDirect.NET .

Apparel21

We have a similar problem, but in .net.

Currently we use version 6.00.0.4 of the ODAC for VCL, and are setting OCIEvents := False; in code which prevents the windows firewall from popping up.

Now we are starting to use ODAC.NET version 4.30.20.0, and have the same problem. What is the equivalent solution in .NET ?

Devart Forums

Search found 15 matches

Re: Direct Mode does not supply domain name in V$SESSION.MACHINE column

Re: TVirtualTable sorting, and disabling sorting

TVirtualTable sorting, and disabling sorting

Re: Oracle IN OUT changes between Delphi XE to Delphi 10 Seattle

Oracle IN OUT changes between Delphi XE to Delphi 10 Seattle

Re: Direct Mode does not supply domain name in V$SESSION.MACHINE column

Re: Direct Mode does not supply domain name in V$SESSION.MACHINE column

Direct Mode does not supply domain name in V$SESSION.MACHINE column

How can I call a plsql procedure with a plsql table as a parameter?

version 6.50 returns generic errors when pooling used

any news?

OraDirect.NET - open ports

We are having a similar problem