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

When I run this:

select 'test"columnname"' from dual

Oracle returns this (notice the Oracle-generated column name):

'TESTCOLUMNNAME'
--------------------------------
test"columnname

The fact that Oracle's column name doesn't include my double-quote tells me that Oracle probably cannot represent that.

Best bet as far as I can see is to strip double-quotes from your data prior to using column names. Sadly, that will also require that you do the same filtering when you select those columns, but I don't see another way.

Answer 2

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 3

Yeah I know, but unfortunately this is a pretty large existing system so it's beyond the scope of the work I'm doing to rewrite the logic of how these columns and values are dealt with.

I think I'll just have to go with your idea of special character replacement, and just reverse the process once the result set is returned from Oracle. Hacky, like you said, but if there's no way to escape a double quote in an Oracle column alias name then I guess it's the only choice :)

Cheers for the help guys.

Answer 4

a possibly fruitful area of investigation would be to look into the quote method.

my $quotedString = $dbh->quote( $string );

Answer 5

use the Oracle quote operator:

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

the '#' can be replaced by any character