Use of keyword DISTINCT and concatonation in a SELECT statement
355105Feb 16 2009 — edited Feb 16 2009Hi all...after digging through this forum, I have not found any examples of the proper syntax when using
the DISTINCT keyword in combination with the concatonation operator in a SELECT statement. I'm
attempting to dynamically generate the ALTER statements I need to change the profile on the user accounts
in the database. I'm using Oracle 10gR2 on a Sun Solaris platform.
Statement:
select 'alter user '||distinct(a.username)||' profile TESTNEW_DEFAULT_PROFILE;' from dba_users;
*
ERROR at line 1:
ORA-00936: missing expression
Without the DISTINCT clause, the statement works but of course delivers multiple rows per user.
So far I have not come across anything in the Oracle documentation to point me in the right direction. Any
helpful suggestions are welcome! Tks