Should be a simple select - need help!

[ocp2b]

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?

[stecal]

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]