Home » SQL & PL/SQL » SQL & PL/SQL » Invalid character (Oracle, 10g, Windows Server 2003)
Invalid character [message #447948] Thu, 18 March 2010 11:54 Go to next message
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 #447949 is a reply to message #447948] Thu, 18 March 2010 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What is the solution for this error?
single quote marks must always be used as pairs; odd number (3) is not valid
>P('MO'DY');
Re: Invalid character [message #447950 is a reply to message #447948] Thu, 18 March 2010 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quoting YOU:
INSERT INTO TABB(NAME) VALUES('MO''DY');

P('MO'DY');

You are able to do it in one case and not in the other one.

Regards
Michel

[Updated on: Thu, 18 March 2010 12:00]

Report message to a moderator

Re: Invalid character [message #447951 is a reply to message #447950] Thu, 18 March 2010 12:24 Go to previous messageGo to next message
Frank
Messages: 7880
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 #447959 is a reply to message #447951] Thu, 18 March 2010 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Thu, 18 March 2010 12:24
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..

Maybe I should say "you are able to WRITE it for one case and not for the other one" because for me the error, in what has been posted, is obvious.

Regards
Michel

Re: Invalid character [message #447985 is a reply to message #447959] Thu, 18 March 2010 22:36 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,


Quote:
Maybe I should say "you are able to WRITE it for one case and not for the other one" because for me the error, in what has been posted, is obvious.


You got it right


Please tell me the solution

Regards,
Ritesh
Re: Invalid character [message #447988 is a reply to message #447948] Thu, 18 March 2010 23:12 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ritesh,

Please try following code.


 create table rb ( str varchar2(10));
/
 insert into rb select  'MO' || '''' || 'DY'  from dual
/
commit;

SQL> select * From rb;

STR
----------
MO'DY


 create procedure p_rb(p_str in varchar2 , x out sys_refcursor)
 as
 begin
 open x for select * from rb where str = p_str;
 end;
 /

SQL> var x refcursor
SQL> exec p_rb('''' || 'MO' || '''' || 'DY' || '''' , :x);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL> print x

STR
----------
MO'DY

Elapsed: 00:00:00.28


Re: Invalid character [message #447991 is a reply to message #447988] Thu, 18 March 2010 23:47 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

Michel Cadot said

Quote:
Maybe I should say "you are able to WRITE it for one case and not for the other one" because for me the error, in what has been posted, is obvious.


I think Michel has understood my problem

Regards,
Ritesh
Re: Invalid character [message #447994 is a reply to message #447991] Thu, 18 March 2010 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I think Michel has understood my problem
Then he stands alone; once again.

Is your problem now solved?
Re: Invalid character [message #447995 is a reply to message #447994] Thu, 18 March 2010 23:58 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

Quote:
Is your problem now solved


No

Quote:

I think Michel has understood my problem
You got it right


Please tell me the solution



Michel Only has to answer him.

sriram Smile
Re: Invalid character [message #447996 is a reply to message #447994] Thu, 18 March 2010 23:59 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

No my problem is not solved

Regards,
Ritesh
Re: Invalid character [message #447997 is a reply to message #447996] Fri, 19 March 2010 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>No my problem is not solved
I'm glad I'm not you.

It is about time for Michel to get up & get back online.
You should hope he is in a benevolent state of mind.

Re: Invalid character [message #447998 is a reply to message #447997] Fri, 19 March 2010 00:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
BlackSwan wrote on Fri, 19 March 2010 00:08
>No my problem is not solved
I'm glad I'm not you.

It is about time for Michel to get up & get back online.
You should hope he is in a benevolent state of mind.



Laughing

sriram Smile
Re: Invalid character [message #448015 is a reply to message #447985] Fri, 19 March 2010 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you see the difference between
INSERT INTO TABB(NAME) VALUES('MO''DY');
and
P('MO'DY');

Regards
Michel
Re: Invalid character [message #448041 is a reply to message #448015] Fri, 19 March 2010 03:30 Go to previous messageGo to next message
Frank
Messages: 7880
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 #448042 is a reply to message #448041] Fri, 19 March 2010 03:34 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
I think Michel has understood my problem

Regards,
Ritesh


sriram Smile
Re: Invalid character [message #448045 is a reply to message #448042] Fri, 19 March 2010 03:41 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Column Reappears Again (merged)
Next Topic: Help in exists / not in
Goto Forum:
  


Current Time: Thu Dec 08 03:50:17 CST 2016

Total time taken to generate the page: 0.16978 seconds