Double Quotes in Oracle Column Aliases

Question

Ok, this is bit of an obscure question, but hopefully someone can help me out with it.

The system I'm working on builds a dynamic SQL string for execution inside a stored procedure, and part of that dynamic SQL defining column aliases, which themselves are actually values retrieved from another table of user generated data.

So, for example, the string might look something like;

SELECT table1.Col1 AS "This is an alias" FROM table1

This works fine. However, the value that is used for the alias can potentially contain a double quote character, which breaks the outer quotes. I thought that I could maybe escape double quotes inside the alias somehow, but I've had no luck figuring out how to do so. Backslash doesn't work, and using two double quotes in a row results in this error;

SQL Error: ORA-03001: unimplemented feature
03001. 00000 -  "unimplemented feature"
*Cause:    This feature is not implemented.

Has anyone had any experience with this issue before? Cheers for any insight anyone has.

p.s. the quotes are needed around the aliases because they can contain spaces.

Answer 1

Can you just put another character instead of double quotes and replace that with double quotes in the code?

Something like this:

SELECT table1.Col1 AS "This is |not| an alias" FROM table1

Then just replace | with ".

I know it's a hack, but I can't think of any better solution... And what you are doing there is a hack anyway. The "nice" way would be to select the values and the column names separately and associate them in your code. That would make things much cleaner.

Answer 2

use the Oracle quote operator:

select q'#someone's quote#' from dual;

the '#' can be replaced by any character