Problem with Parameters name witch contains $

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Problem with Parameters name witch contains $

Post by danyinfo » Wed 08 Jun 2011 09:23

Hello.
I have a stored procedure with parameters name like param$first.
In C# client when i call this procedure i obtain an error: ORA-01036: illegal variable name/number.
If i replace the $ character with _ it work ok, but i want to keep the curent form(with $ character) in all my procedures.
This requirement can be solved?

Thank you.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 10 Jun 2011 12:47

I cannot reproduce the problem with the following code using dotConnect for Oracle v 6.30.165:

Code: Select all

    //CREATE OR REPLACE PROCEDURE specsymb(param$first IN VARCHAR2) AS
    //BEGIN
    //    NULL;
    //END specsymb;

    using (OracleConnection conn = new OracleConnection()) {
        //conn.ConnectionString = "server=orcl1120;uid=***;pwd=***;";
        conn.ConnectionString = "Direct=true;server=dboracle;SID=orcl1120;uid=***;pwd=***;";
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "specsymb";
        cmd.Parameters.Add("param$first", "some string");
        cmd.ExecuteNonQuery();
    }
Please give us the following information:
1) how should we modify this sample to reproduce the issue in our environment?
2) the exact versions of your dotConnect for Oracle (x.xx.xxx), Oracle server, and Oracle client;
3) your connection string (roughly, without credentials).

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Problem with Parameters name witch contains $

Post by danyinfo » Fri 10 Jun 2011 13:33

Hello.
I use dbExpress Drivers.
My oracle server is Xe Edition 10G, the oracle client is ODAC 11.2 Release 3 (11.2.0.2.1).
My connection string is based on tnsnames.ora

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Tue 14 Jun 2011 07:18

Hello,

Please specify the following information:
- The name and the full version of your IDE;
- The full version of Devart dbExpress driver for Oracel;
- How are you using dbExpress drivers with C#?

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Character $ in stored procedure

Post by danyinfo » Wed 15 Jun 2011 07:50

Hello
- The name and the full version of your IDE;
MS Visual Studio 2008 SP1
- The full version of Devart dbExpress driver for Oracle;
dotConnect for Oracle 6.30 Express - 6.30.160 17-May-11
- How are you using dbExpress drivers with C#?

On the development machine I have:

ODAC 11.2 Release 3 (11.2.0.2.1) with Oracle Developer Tools for Visual Studio
Released December 28, 2010

with tnsnames.ora:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.x.x)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=orcl)
)
)

sqlnet.ora :

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, LDAP)
TRACE_LEVEL_CLIENT = OFF
TCP.CONNECT_TIMEOUT=10
SQLNET.SEND_TIMEOUT=5
SQLNET.RECV_TIMEOUT=5
sqlnet.outbound_connect_timeout = 15

connection string:

private static string GetConnectionString()
{
return "Data Source=ORCL;User ID=user;Password=pass;" +
" Pooling = True;Min Pool Size=10;Max Pool Size=20;Connection Lifetime=120;Connection Timeout=60;";

}

sample code:

command = _oracleConnection.CreateCommand();
sql = "GIS_INTERFACE.PK$INTERFACE.F$SET_WAYPOINTS";

command.CommandText = sql;
command.CommandTimeout = 5;
command.CommandType = CommandType.StoredProcedure;

OracleParameter prmOut = new OracleParameter("return", OracleDbType.Number);
prmOut.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(prmOut);

OracleParameter prm00 = new OracleParameter("param_route_name", OracleDbType.VarChar);
prm00.Direction = ParameterDirection.Input;
prm00.Value = name;
command.Parameters.Add(prm00);

OracleParameter prm11 = new OracleParameter("param_login_id", OracleDbType.Number);
prm11.Direction = ParameterDirection.Input;
prm11.Value = configInterfaceData.userId;
command.Parameters.Add(prm11);

command.ExecuteNonQuery();

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Problem

Post by danyinfo » Mon 20 Jun 2011 08:33

Hello.
Any idea regarding this problem?

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 20 Jun 2011 11:45

1. Please post here the DDL script of your stored procedure (or try to reproduce the problem with the code from my post) because it is not clear from your post which parameter has '$' in its name.
2. As I understood, both your Oracle server and Oracle client are of the 11.2 version, aren't they?

danyinfo
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Character $ in stored procedure

Post by danyinfo » Mon 20 Jun 2011 12:07

Hello.
1. Please post here the DDL script of your stored procedure (or try to reproduce the problem with the code from my post) because it is not clear from your post which parameter has '$' in its name.

Code: Select all

PACKAGE PK$INTERFACE AS
    FUNCTION F$SET_WAYPOINTS (
        param$route_name  nvarchar2,
        param$login_id        number
        )
    RETURN NUMBER;
END PK$INTERFACE;

2. As I understood, both your Oracle server and Oracle client are of the 11.2 version, aren't they?

No, my oracle server is Xe Edition 10G and the oracle client is ODAC 11.2 Release 3 (11.2.0.2.1).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 21 Jun 2011 13:43

We cannot reproduce the problem in our environment.
Please try using the "Unicode=true;" connection string parameter. If this doesn't help, tell us:
1) NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET of your Oracle server;
2) NLS_LANG of your Oracle client (from registry).

Post Reply