Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: How can i get ' in a Spoolfile?

    Have anyone a tip?
    I want to generate a SQL-Script with using spool.

    Look:
    spool xx.sql

    select 'update accounts set account_name = P_'||a.personal_nr||' where account_id = '||c.account_id||';'
    from ....

    spool off

    Result:
    update accounts set account_name = P_12345 where account_id = 12345;

    But P_12345 is an Ascii and must set in '. How can I get the ' in the spoolfile?

    Thanx for HELP
    M@tes

  2. #2
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    4

    Wink

    You have to include 2 single quotes for single quote to be generated in the spool file:

    try

    select 'update accounts set account_name = ''P_'||a.personal_nr||' '' where account_id = '||c.account_id||';'
    from ....


    Also don't forget to put some white space before starting where clause.


    Good luck !!

  3. #3
    Join Date
    Aug 2003
    Posts
    6

    @qbkoniki

    Nice Try. I check it before i write in forum. (Looks DB2 like ;-) )
    Result
    update accounts set account_name = "P_12345"....

    But this does'nt work. (ORA-00904)

    I use 8.1.6.0.0
    and it will only accept " = '12345' " for char.

    Thank you
    anybody an other idea?

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi try this:

    select 'update accounts set account_name = '||'''P_'||a.personal_nr||''' where account_id = '''||c.account_id||''';'
    from ......

    I tried it here and it should.

    Rgs,
    Breen.

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

    Re: @qbkoniki

    Originally posted by M@tes
    Nice Try. I check it before i write in forum. (Looks DB2 like ;-) )
    Result
    update accounts set account_name = "P_12345"....

    But this does'nt work. (ORA-00904)

    I use 8.1.6.0.0
    and it will only accept " = '12345' " for char.

    Thank you
    anybody an other idea?
    bkoniki's answer didn't say use double quote ("), it said use 2 single quotes (''). The font in this forum makes it difficult to see the difference!
    It will work.

  6. #6
    Join Date
    Aug 2003
    Posts
    6

    Lightbulb @Breen

    Hi Breen

    really it works. Thank you.

    The Second (person_id ) is a number. So no ' need.

    But the first of your script really works. And look to the ''' behind the first concat. That's really hard.

    You have single chars with concats.
    1. update accounts set account_name = (open and close with ')
    2. one ' (open and close with ')
    3. at this Point no new char will begin with ' but oracle made it.



    M@tes

  7. #7
    Join Date
    Aug 2003
    Posts
    6

    Re: @qbkoniki

    Originally posted by andrewst
    bkoniki's answer didn't say use double quote ("), it said use 2 single quotes (''). The font in this forum makes it difficult to see the difference!
    It will work.
    Hi andrewst
    thank you. I have think so.
    And now i know a quote is this ' .
    I had testet both single and double.

    @ALL
    Thank you!!

    M@tes

  8. #8
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: How can i get ' in a Spoolfile?

    Try this:

    SELECT CHR(39)||'USERNAME'||CHR(39) FROM DUAL;

    CHR(39)||'
    ----------
    'USERNAME'


    34 = "
    37 = %
    95 = _

    And if you're not sure of the ASCII Cgharacter translations, just look under ASCII in MS Access to get the list.

    And this select statement will have you cursing:
    SELECT CHR(39)||CHR(33)||CHR(35)||CHR(64)||CHR(37)||CHR(6 3)||CHR(32)||CHR(95)||CHR(36)||CHR(34) FROM DUAL;
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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