Invalid character [message #447948] |
Thu, 18 March 2010 11:54  |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
CREATE TABLE TABB(NAME VARCHAR2(10));
Table Created
INSERT INTO TABB(NAME) VALUES('MO''DY');
1 row inserted
INSERT INTO TABB(NAME) VALUES('S&P');
1 row inserted
SELECT *
FROM TABB;
NAME
S&P
MO'DY
I created a procedure
Create or Replace Procedure p(para varchar2)
Is
reff sys_refcursor;
cnt sys_refcursor;
v_cnt Number;
Begin
Open cnt For SELECT Count(*)
From ( Select name
From TABB
Where Name = para);
Fetch cnt Into v_cnt;
dbms_output.put_line('Count = '||v_cnt);
Close cnt;
Open reff for Select name
From TABB
Where Name = para;
End p;
Now my doubt is
1) This procedure is getting called from ado.net
with parameter MO'DY.I get this error on oracle server
On my side i can manipulate parameter to MO''DY to get Count as 1 but what to do if it called from ado.net, there i cannot change MO'DY
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 P('MO'DY');
3* END;
SQL> /
ERROR:
ORA-01756: quoted string not properly terminated
What is the solution for this error?
Regards,
Ritesh
|
|
|
|
|
Re: Invalid character [message #447951 is a reply to message #447950] |
Thu, 18 March 2010 12:24   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 18 March 2010 11:59
You are able to do it in one case and not in the other one.
Regards
Michel
Which is the actual question..
@getritesh: What error do you get when calling from your ado and what sql is sent from the ado?
Do you use bind variables (sending "MO'DY" as a String to be bound to the query) or do you somehow concatenate the value "MO'DY" into your command?
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Invalid character [message #448041 is a reply to message #448015] |
Fri, 19 March 2010 03:30   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Either I don't understand the actual question, or Michel does not.
As I read it, the original poster is very well aware of the double quote issue, but he asks how he can get around it using ado.net.
Unfortunately he chose not to answer my questions.
|
|
|
|
Re: Invalid character [message #448045 is a reply to message #448042] |
Fri, 19 March 2010 03:41   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I can read, thanks very much.
The thing is that, reading this thread, I still think that either the original post was very unclear (ok, to me that is), or getritesh did not understand Michel's initial response.
|
|
|
Re: Invalid character [message #448163 is a reply to message #448045] |
Fri, 19 March 2010 23:09   |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |

|
|
Hi,
In my project Oracle 10g if backend and .net is frontend
My .net developer calls packaged procedures created by me.
Now have a look at below procedure, it is on same lines with my real procedure[
Create or Replace Procedure p(para varchar2)
Is
reff sys_refcursor;
cnt sys_refcursor;
v_cnt Number;
Begin
Open cnt For SELECT Count(*)
From ( Select name
From TABB
Where Name = para);
Fetch cnt Into v_cnt;
dbms_output.put_line('Count = '||v_cnt);
Close cnt;
Open reff for Select name
From TABB
Where Name = para;
End p;
Here table TABB contains a column named NAME which has two values S&P and MO'DY.
My .net developer enters MO''DY as input parameter while calling stored procedure p, but still my .NET developer is getting record count as zero even though there are records with NAME = MO'DY.
Please guide me
Regards,
Ritesh
[Updated on: Fri, 19 March 2010 23:10] Report message to a moderator
|
|
|
Re: Invalid character [message #448164 is a reply to message #448163] |
Fri, 19 March 2010 23:13   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>Please guide me
Not an Oracle problem.
Query works as desired from SQLPLUS.
With proper SQL, desired results are returned.
SQL> drop table tic;
Table dropped.
SQL> create table tic (str varchar2(5));
Table created.
SQL> insert into tic values('MO'||CHR(39)||'DY');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tic;
STR
-----
MO'DY
SQL> select count(*) from tic where str = 'MO'||CHR(39)||'DY';
COUNT(*)
----------
1
SQL> select count(*) from tic where str = 'MO''DY';
COUNT(*)
----------
1
[Updated on: Fri, 19 March 2010 23:37] Report message to a moderator
|
|
|
Re: Invalid character [message #448176 is a reply to message #448163] |
Sat, 20 March 2010 02:06  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:My .net developer enters MO''DY as input parameter
And How MO''DY is interpreted in .Net?
Post the .Net code line.
If it is 'MO''DY', then the error is in .Net program, it should be 'MO''''DY'.
Regards
Michel
|
|
|