Page 1 of 1
Problem with Parameters name witch contains $
Posted: Wed 08 Jun 2011 09:23
by danyinfo
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.
Posted: Fri 10 Jun 2011 12:47
by Shalex
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).
Problem with Parameters name witch contains $
Posted: Fri 10 Jun 2011 13:33
by danyinfo
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
Posted: Tue 14 Jun 2011 07:18
by AlexP
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#?
Character $ in stored procedure
Posted: Wed 15 Jun 2011 07:50
by danyinfo
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();
Problem
Posted: Mon 20 Jun 2011 08:33
by danyinfo
Hello.
Any idea regarding this problem?
Thanks.
Posted: Mon 20 Jun 2011 11:45
by Shalex
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?
Character $ in stored procedure
Posted: Mon 20 Jun 2011 12:07
by danyinfo
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).
Posted: Tue 21 Jun 2011 13:43
by Shalex
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).