Remove carriage return and line feed in the all columns in a table in oracle-CodePudding

Have to remove carriage return and/or line feed in the all columns in multiple tables in oracle. Thought of dynamic SQL query and pass the table name and column name using injection in the UPDATE query. Have tried the below query for a column but it errors out. Please assist me on the same.

DECLARE
    TableName VARCHAR2(256);
    ColumnName VARCHAR2(256);
    plsql_block VARCHAR2(500);
BEGIN
    TableName := 'TEST';
    ColumnName := 'VALUE2';
    plsql_block := 'BEGIN UPDATE :A SET :B = REPLACE(:B, CHR(13), '''') END;';
    EXECUTE IMMEDIATE plsql_block USING TableName, ColumnName, ColumnName;
END;

Error Message:

Error report -
ORA-06550: line 1, column 19:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 54:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ( begin case declare end exception exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << continue close current delete fetch lock
   insert open rollback savepoint set sql execute commit forall
   merge pipe purge json_exists json_value json_query
   json_object json_array
ORA-06512: at line 9
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

CodePudding user response：

You'll have to concatenate table/column names.

Sample data:

SQL> CREATE TABLE test
  2  AS
  3     SELECT 'abc
  4  def' value2 FROM DUAL;

Table created.

SQL> SELECT * FROM test;

VALUE2
-------
abc
def

PL/SQL block (no need to use BEGIN-END in dynamic SQL; pure UPDATE will do):

SQL> DECLARE
  2     TableName    VARCHAR2 (256);
  3     ColumnName   VARCHAR2 (256);
  4     plsql_block  VARCHAR2 (500);
  5  BEGIN
  6     TableName := 'TEST';
  7     ColumnName := 'VALUE2';
  8     plsql_block :=
  9           'UPDATE '
 10        || DBMS_ASSERT.sql_object_name (tablename)
 11        || ' SET '
 12        || DBMS_ASSERT.simple_sql_name (columnname)
 13        || ' = REPLACE('
 14        || DBMS_ASSERT.simple_sql_name (columnname)
 15        || ', CHR(10), '''')';
 16
 17     EXECUTE IMMEDIATE plsql_block;
 18  END;
 19  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM test;

VALUE2
-------
abcdef

SQL>