adding double quotes to a concatenated query-CodePudding

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

CodePudding user response：

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>

CodePudding user response：

You have too many single quotes:

' disable constraint '"' || constraint_name ||  '"' '
---------------------^                  ----------^

should be

' disable constraint "' || constraint_name ||  '" '

so in place that looks like:

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