[Genevieve]

Is there a way to put a single quotes in a string other than double it ... so I won't have to code a script to automate this?

I've read about bind variable, but I don't know what it is.

Thanks
Genevieve

[andrewst]

Originally posted by Genevieve
Is there a way to put a single quotes in a string other than double it ... so I won't have to code a script to automate this?

I've read about bind variable, but I don't know what it is.

Thanks
Genevieve

Using bind variables removes a lot of the need to handle quotes in strings, because instead of writing this:

v_sql := 'insert into emp (ename) values (''' || v_name || ''')';
EXECUTE IMMEDIATE v_sql;

(Hope I got those quotes right!)

You would write this:

v_sql := 'insert into emp (ename) values (:name)';
EXECUTE IMMEDIATE v_sql USING v_name;

In something like ASP you would use a Prepared Statement like:

strSql = 'insert into emp (ename) values (?)';

and pass the name value as a parameter.

Apart from reducing code errors by an order of magnitude, bind variables are also ESSENTIAL for any serious Oracle application, because they allow queries to be re-used. A large multi-user application written with hard-coding like the first example above will have DRASTIC performance issues.

Bear in mind that when using "static SQL" in PL/SQL, you get bind variables for free. For example, this uses bind variables:

declare
v_name varchar2(30) := 'Smith';
begin
insert into emp (ename) values v_name;
end;

Always use static SQL rather than dynamic SQL if you can.

Of course, there will still be occasions when you do need to double up quotes in a string. There are a variety of ways to deal with this:

1) Just do it: string := 'Jim''s cat';

2) Break it up: string := 'Jim' || '''' || 's cat';

3) Use CHR(39): string := 'Jim' || CHR(39) || 's cat';

What are the circumstances you mentioned where you would need to write a script to automate this?

[Genevieve]

well fist thank you for the explanation about bind variable and as some of you may have guested I'm new to Oracle .... used to sql-server ... anyway...

the script was to double the single quotes automaticly ... so when a user will type something like [Bob's home] ... the script will double the quote so in the string it looks like [ Bob''s home ] so oracle will take it properly because I haven't be able to pass string containing single quote to the db except if I double the quotes.

[andrewst]

Originally posted by Genevieve
well fist thank you for the explanation about bind variable and as some of you may have guested I'm new to Oracle .... used to sql-server ... anyway...

the script was to double the single quotes automaticly ... so when a user will type something like [Bob's home] ... the script will double the quote so in the string it looks like [ Bob''s home ] so oracle will take it properly because I haven't be able to pass string containing single quote to the db except if I double the quotes.

If you use bind variables, you won't need to do that.