Devart Forums

torely

The version of my dotConnect for Oracle is 5.00.22. It is the latest release and there no such a property.
Is this functionality realized in 5.20 Beta?
Can you inform me when this functionality will be realized in the release?

torely

Using parameters is not clear
I ‘m using parameters with stored procedures and components behavior is not so that I expect.
Here is the sample that should help you understand what exactly I mean.

1st scenario (using OracleCommand with ParameterCheck = false)
Package:

Code: Select all

create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin
 …
End;

end TEST;

C# code:

Code: Select all

pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();

It works, but value of P3 parameter is assigned to P2 parameter and it doesn’t depend of the parameter name (i.e. I can write oracleCommand1.Parameters.Add("aaa ", "not test") and it still aasignes value “not test” to P2 parameter) it depends only of the order of parameter.
Here is serious problem. I should always check parameters order because if write

Code: Select all

oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "param3");
oracleCommand1.Parameters.Add("P2 ", "param2");

then indeed P2 value will be “param3” and P3 value “param2”.

2nd scenario (using OracleCommand with ParameterCheck = true)
Package:

Code: Select all

create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin
 …
End;

  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’) return REF_CURSOR;
Begin
 …
End;

end TEST;

C# code:

Code: Select all

pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();

In this case it will not work because return parameter name is unknown (even if there no return parameter (for procedure) I loose my defaul values as it described in help). And another problem: How components will define what function to use with 3 or 2 parameters (as it described in help, all unassigned parameters values will set to NULL).

Exactly what I need is to use OracleCommand with ParameterCheck = false, and not to check parameters order.

torely

After patching Oracle from 9.2.0.1 to 9.2.0.8 all troubles were solved.

Thanks to all Devart team for greate product!

torely

I have a problem with ASP.NET + OraDirect application deployment with IIS. When I try to browse my page there an error Can not load Oracle client. Check your PATH environment variable and registry settings raised. If try to run the application in VS 2005 everything works fine. I've read some topics here and applied your suggestions. It has no effect. Should I apply patch to my Oracle client or there are another ways to solve this problem?

Here is description of my environments:

Windows 2003 Server SP2
IIS 6.0
Framework v2.0.50727
OraDirect 4.50
Oracle 9.2.0.1 (server and client)

Variables are:
OraHome92 - e:\oracle\ora92\bin
Path - C:\Program Files\Support Tools\;C:\Inprise\vbroker\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\system32\WBEM;e:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;c:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Borland\StarTeam SDK 2005 R2\Lib;C:\Program Files\Borland\StarTeam SDK 2005 R2\Bin;C:\PROGRA~1\Borland\Delphi6\Bin;C:\PROGRA~1\Borland\Delphi6\Projects\Bpl

Oracle installed on disk E (e.g. oci.dll could be found in e:\oracle\ora92\bin folder)

Reester key:
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES]
"HOME_COUNTER"="1"
"DEFAULT_HOME"="OraHome92"
"LAST_HOME"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0]
"NAME"="OraHome92"
"PATH"="E:\oracle\ora92"
"NLS_LANG"="NA"

Please help me to solve this problem cause I already have no idea what to do.

Regards, torely.

torely

I've looked throw your help topic of using PL/SQL Tables and I've investigated the sample. But it is not exactly what I am looking for. I can't understand how to pass parameters of the PL/SQL Tables type into stored procedure.

Code: Select all

create or replace procedure TEST1(TXT string_list) is 
  cnt number; 
begin 
  cnt := 0; 
  for v in (select * from table(TXT)) loop 
    cnt := cnt + 1; 
  end loop;  
end TEST1; 

create type string_list is table of varchar2(100);

I need it for the following purposes. I have the SP with the select query which has where Field1 in (Param1) clause. I want to use such a pattern:

Code: Select all

select ... from ... where Field1 in (select * from table(Param1))

If there are other ways of solving my problem, I'm very much appreciated to learn them but using my pattern is more preferable.

Regards, torely.

torely

I published new topic

torely

Посмотрел в вашем хэлпе пример использования PL/SQL Tables и пример, который идет с компонентами. Но все это немного не то, не могу разобраться, как мне передавать в хранимую процедуру параметр типа PL/SQL Tables

Code: Select all

create or replace procedure TEST1(TXT string_list) is
  cnt number;
begin
  cnt := 0;
  for v in (select * from table(TXT)) loop
    cnt := cnt + 1;
  end loop;  
end TEST1;

create type string_list is table of varchar2(100);

Объясню для чего мне это нужно. У меня есть ХП, в которой есть select с условием where Field1 in (Param1). Я хочу написать ее так:

Code: Select all

select ... from ... where Field1 in (select * from table(Param1))

.
Если есть другие варианты решения этой задачи, то хотелось бы их увидеть, но мой вариант был бы предпочтительнее.
Заранее благодарен за ответы.

Devart Forums

Search found 7 matches

Using parameters is not clear

Problem with ASP.NET + OraDirect application deployment

Using pl/sql tables parameters in stored procedures

Использование параметров pl/sql tables в хранимых процедурах