Hi everyone,

I'm having extreme difficulties in getting a stored procedure to work. I am passing string values from a web page to the following procedure which updates a table in an Oracle db.


var cmd = Server.CreateObject("ADODB.Command");
cmd.ActiveConnection = Application("bmcmaindb");

var param1 = cmd.CreateParameter("text1", adChar, adParamInput, 4000);

var param2 = cmd.CreateParameter("text2", adChar, adParamInput, 4000);

cmd.Parameters.Append(param1);
cmd.Parameters.Append(param2);

cmd.Parameters("text1") = param1;
cmd.Parameters("text2") = param2;

cmd.CommandText = "{CALL my_pkg.my_proc(?,?) }";

cmd.CommandType = adCmdText;
cmd.Execute();

cmd = null;


The trouble here is that it is knocking off text after any apostrophe within both of the parameters. Does anyone know why?


Regards,

John

The reason for this that Oracle sees the apostrophe as the end of the field. To enter apostrophe's into the Oracle database you need to double up the apostrophe in the string, so that Oracle processes see it as an apostrophe rather than the end of the field.

Just Escape the variables when you're putting them into the database. UnEscape them when you're reading them out of your database.


OK, it's pretty irritating, but it works.