-
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?
-
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]
