Problem with Parameters name witch contains $

Problem with Parameters name witch contains $

Postby 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.
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby 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).
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Problem with Parameters name witch contains $

Postby 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
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby 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#?
AlexP
Devart Team
 
Posts: 5525
Joined: Tue 10 Aug 2010 11:35

Character $ in stored procedure

Postby 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

Postby danyinfo » Mon 20 Jun 2011 08:33

Hello.
Any idea regarding this problem?

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

Postby 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?
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Character $ in stored procedure

Postby 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).
danyinfo
 
Posts: 9
Joined: Thu 02 Jun 2011 09:03

Postby 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).
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle