adding double quotes to a concatenated query

August 9, 2022

i need to add double quotes to the constraint name but for some reasons it does not work. Please advise

SELECT 'alter table '|| owner || '.' || table_name || ' disable constraint '"' || constraint_name ||  '"' ' FROM dba_constraints  WHERE owner IN ('test');

                                     *
Error at line 1
ORA-00923: FROM keyword not found where expected

>Solution :

You have 2 superfluous single quotes:

SQL> SELECT    'alter table '
  2         || owner
  3         || '.'
  4         || table_name
  5         || ' disable constraint '
  6         || '"'
  7         || constraint_name
  8         || '" '
  9    FROM user_constraints
 10   WHERE     owner IN ('SCOTT')
 11         AND table_name NOT IN ('EMP', 'DEPT');

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||'"'||CONSTRAINT_NAME|
--------------------------------------------------------------------------------
alter table SCOTT.STOCK disable constraint "ITEMID_FK"
alter table SCOTT.STOCK disable constraint "SYS_C00106526"
alter table SCOTT.STOCK disable constraint "STOCKID"
alter table SCOTT.STOCK disable constraint "SYS_C00106532"
alter table SCOTT.STOCK disable constraint "SYS_C00106531"
<snip>