DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: embedding single quotes

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have been trying to put the following line into a spool file:
    execute send_mail('sheryl@acme.com','sheryl@test.com','TEST SUBJECT','PROD:USERS is 75% used');

    by the following sql:
    dbms_output.put_line('execute send_mail('''||send_address||''','''||rec_address||''','''TSA Alert''','''||dbname||''': '''||tbsname||''' is '''||bytes_pct||'''% used''');');

    (where :
    send_address := 'sheryl@acme.com'
    rec_address := 'sheryl@test.com'
    dbname := 'PROD'
    tbsname := 'USERS'
    bytes_pct := '75')

    But I get the following error:
    ORA-01756: quoted string not properly terminated

    I have now been working on this all day, by adding or removing the single quotes, even using chr(39), but still can't seem to crack this. Can anyone please help?

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    dbms_output.put_line('execute send_mail('''||send_address||''','''||rec_address||''',''TSA Alert'','''||dbname||':'||tbsname||' is '||bytes_pct||'''% used'');');

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Thanks.

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    Write a small function add_quotes:

    FUNCTION add_quotes(txt_in IN VARCHAR2) RETURN VARCHAR2 IS
    sq VARCHAR2(1) := '''';
    BEGIN
    RETURN (sq || txt_in || sq);
    END;

    And whenever you need to supply quotes, do this:

    show_message('Device '||add_quotes(:EQUIPMENT_CTRL.serial_number)||' '||'not found, Register manually');
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

Posting Permissions

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


Click Here to Expand Forum to Full Width