CODEWITHSUNDEEP
oracle-databaseplsql
oisleyen
oisleyen

Reputation: 92

how to check new line character for all columns of the table

Assume that there is table that named "GROUP_DETAILS" that has columns (NAME, CODE, DESCRIPTION).

So, the table looks like this:

enter image description here

I am trying to find new line character in all columns of the table. Then as soon as the column is found, I also want to show as a output. I have already created a script as below;

DECLARE
BEGIN
  FOR r IN (select column_name from user_tab_columns where TABLE_NAME = 'GROUP_DETAILS') LOOP
    select r from GROUP_DETAILS where instr(r, chr(10)) > 0;
  END LOOP;
END;

I am getting an error as below;

Error report - ORA-06550: line 4, column 48: PLS-00382: expression is of wrong type ORA-06550: line 4, column 12: PLS-00382: expression is of wrong type ORA-06550: line 4, column 5: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Upvotes: 0

Views: 4038

Answers (1)

user5683823
user5683823

Reputation:

Do you need to do this in PL/SQL? You could get the offending rows with plain SQL, like this:

select name, code, description,
       case when name        like '%' || chr(10) || '%' then 'name, '      end ||
       case when code        like '%' || chr(10) || '%' then 'code, '      end ||
       case when description like '%' || chr(10) || '%' then 'description' end
       as where_found
from group_details
where name        like '%' || chr(10) || '%'
   or code        like '%' || chr(10) || '%'
   or description like '%' || chr(10) || '%';

This will return all the rows where at least one of the columns contains a newline, and the calculated where_found column will tell you exactly which columns have those values. (They would also be clearly visible; the calculated column can be used for further processing, if needed.)

Upvotes: 2

Related Questions