#915 double quoted string with semicolon value not handeled well

[Carey Matthew Black]

The following command fails when connected to Oracle. ( It works in other Oracle clients.)
alter user SOMEUSER identified by "SOME;PASSWORD_with_a_semicolon";

Error that was returned:
"
Error: ORA-01740: missing double quote in identifier

SQLState: 42000
ErrorCode: 1740
Position: 47
Error occured in:
alter user updater identified by "SOME
"

[sfst]

Version 4.5.0 still throws this error.

I am not sure whether it is related to the problem that this select command:

select q'{long string with some quoted semicolon ';' in the string.}' from dual;

Throws:
--begin quote--
Error: ORA-01756: quoted string not properly terminated

SQLState: 42000
ErrorCode: 1756
Position: 79
Error occurred in:
select q'{long string with some quoted semicolon '
--end quote--

FYI: This here runs fine:
select q'{long string with some quoted colon ':' and double dashes '--' in the string.}' from dual
union all
select q'{long string with some comment starter '/*' in the string.}' from dual
union all
select q'{long string with some comment end '*/' in the string.}' from dual

If it is unrelated, I can open another ticket.

[Gerd Wagner]

According to my tests q'{...}' in an Oracle specific way of quoting e.g. it does not work on Postgres. I see the following options/workarounds:

1.) Use standard quoting and standard escaping:
select 'long string with some quoted semicolon '';'' in the string.' from dual

2.) Change the statement separator. If you are using the Oracle Plugin at menu Global Preferences --> tab Oracle, if not at menu New Session Properties --> tab SQL.

[sfst]

[..]does not work on Postgres

Yes, unfortunately, which is a pity, but it isn't a requirement for SQuirreL SQL to only use the intersection of capabilities of Postgres and Oracle, right?

1.) Use standard quoting and standard escaping:
select 'long string with some quoted semicolon '';'' in the string.' from dual

That is what I am using, but it is an inconvenience.
I am often inserting SQL commands into a setup table (some modules are fetching those commands to do some daily tasks like data integrity checks and data integration)
So I have to modify the SQL commands I have manually tested (doubling those single quotation marks in a search & replace operation).
Discovering the Oracle quoting made that much more convenient and also keeps the commands more readable (before I had to keep 2 versions of each command: the one for testing and one for inserting into the setup table).
As mentioned, this workaround is not a show-stopper, just an inconvenience.

2.) Change the statement separator. If you are using the Oracle Plugin at menu Global >Preferences --> tab Oracle, if not at menu New Session Properties --> tab SQL.

I totally forgot about that :-)
This is probably a reasonable workaround for the original problem with the password and I will try to keep that in mind.
But for my large script collection this would be rather impractical and probably just shifting the problem to a different character.