Should be a simple select - need help!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Should be a simple select - need help!

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    I am trying to develop a sql statement to produce the following:

    ALTER TABLESPACE ALM_DSEG1_TSP ADD DATAFILE 'C:\SV\ALM\ADM\ALMDS1.TSP'
    SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 4000M ;

    I am using the following select statement to get the results.

    select 'ALTER TABLESPACE '|| tablespace_name || ' ADD DATAFILE '
    || substr(file_name,3,24)
    || ' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 4000M ;'
    from dba_data_files
    where tablespace_name = 'ALM_DSEG1_TSP';

    However, I am not able to get the single tick or quote mark before the "C:" or after the "TSP". I manually type it in.

    Any suggestions?



  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You need to escape the single quotation mark with more single quotation marks.

    select '''c:\file_name''' from wherever;

    The string 'c:\file_name' needs to be quoted differently than a string without any single ticks on it - confusing, but that's how it works. You can also use chr(39) to represent the single tick.
    To create a single quote, concatenate CHR(39) to the string.

    Example 1
    ---------
    SQL> SELECT 'test' || CHR(39) || 'case' result
    2> FROM dual;

    RESULT
    ---------
    test'case


    Example 2
    ---------
    SQL> SELECT CHR(39) FROM dual;

    C
    -
    '

    Keep the following two rules in mind:

    1. Enclose every character string in single quotes.
    The single quote is a string delimiter.

    2. Inside a string literal, use two consecutive single quotes
    to create a literal single quote.

    Example 1
    ---------
    6 single quotes: 'test' || '''''' || 'case' ---> test''case
    8 single quotes: 'test' || '''''''' || 'case' ---> test'''case

    You can also implement the above in the following way:

    'test''case' ---> test'case
    'test''''case' ---> test''case


    Hence:

    a. To create a single quote, concatenate 4 single quotes: ''''

    The two single quotes in the middle define the single quote.
    The outside single quotes are the single quotes that must
    surround a string.


    [Edited by stecal on 06-28-2002 at 12:01 PM]

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