Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    2

    Question Unanswered: quotes in string

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: quotes in string

    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?

  3. #3
    Join Date
    May 2003
    Posts
    2
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •