How to change table you query from in FOR loop IN clause with PL/SQL

Question

I am trying to wring a pl/sql script that will check every value for every column and every row for trailing whitespace.

My plan is to get the column and table name for my app from dba_tab_cols, then loop through each row of that table on that column and check for trailing whitespace and trim if needed.

My problem is that this requires my inner loop to have to update the table it is selecting from and am finding that such an action is not supported. I'm not sure if there is a way around this or another method I should consider. Every path I explore ends up at the same problem: I have to update the table the inner loop is selecting from.

My pseudocode is below:

BEGIN
  for i in
    (
    --get table name and column name for all tables from MYAPP
    select table_name, column_name from dba_tab_cols where owner='MYAPP'
    )
    LOOP
      BEGIN
        for k in
          (
          -- for each column in each table returned from outer loop, loop through each row and check for trailing whitespace
          select i.column_name from MYAPP.i.table_name -- I know this is wrong, not sure what should go here
          )
          LOOP
            -- compare value of column with value of trim(column) 
            -- if different then fix
          END LOOP;
      END;
    END LOOP;
END;
/

EDIT: One requirement is to identify and print out the offending values so a simple update statement is not enough. I have been trying to understand sys_refcursor because it seems like that might be what I need but I'm strugging to get a grasp on it.

You don't need to loop over every row, just update `update tab set field = trim(trailing ' ' from field) where field != trim(trailing ' ' from field)` — wolfrevokcats, Feb 28 '18 at 19:27
Interesting, maybe I'm over thinking then. I'll play around with this approach for a bit and see where I get. Thanks — exit_1, Feb 28 '18 at 19:30

kfinity · Accepted Answer · 2018-02-28T21:00:28.910

If you can't write a specific table name in your code, you need a dynamic SQL statement.

BEGIN
  for i in
    (
    --get table name and column name for all tables from MYAPP
    select table_name, column_name, owner from dba_tab_cols where owner='MYAPP'
    )
    LOOP
      execute immediate 'update ' || i.owner || '.' || i.table_name 
        || ' set ' || i.column_name || ' = trim(' || i.column_name || ') '
        || ' where ' || i.column_name || ' <> trim(' || i.column_name || ')';
    END LOOP;
END;
/

EDIT: Okay, if you want to log each value it finds, that's way more complicated, haha! Yeah, pretty sure you need a second loop for that. There are several ways to do this sort of thing; I did a bulk collect into a collection, and then looped over that collection.

DECLARE
  type t_srec is RECORD(rid ROWID, str varchar2(4000));
  type t_string_tab is table of t_srec;
  v_st t_string_tab;
BEGIN
  for i in
    (
    --get table name and column name for all tables from MYAPP
    select table_name, column_name, owner from dba_tab_cols where owner='MYAPP'
    )
    LOOP
      execute immediate 'select rowid, ' || i.column_name || ' as str from ' || i.table_name 
        || ' where ' || i.column_name || ' <> trim(' || i.column_name || ')'
        bulk collect into v_st;
      for j in 1..v_st.count loop
        dbms_output.put_line(i.table_name || '.' || i.column_name || ' has value: ' || v_st(j).str);
        execute immediate 'update ' || i.table_name 
            || ' set ' || i.column_name || ' = trim(' || i.column_name || ') '
            || ' where rowid = :rid' using v_st(j).rid;
      end loop;
    END LOOP;
END;
/

One thing I was trying to add onto this was a way to report or print out all offending values but ran into a roadblock. I was trying to do a sort of select from dual but I'm fumbling. Is there an easier way that comes to mind? — exit_1, Feb 28 '18 at 20:01
Thank you, I'll have to take some time to really digest what you're doing here. I never heard of `bulk collect` before so I'll have to study up. I had started going down the road of learning more about `REF CURSOR` but again, I can't find a way to update the table with the next one in the `open` statement. Can this also be done with `REF CURSOR` strategy or am I wasting my time with it? — exit_1, Feb 28 '18 at 21:16
Sure, it's pretty similar. Instead of executing the dynamic select statement and bulk collecting into a collection of RECORD variables, you `open` the ref cursor variable for your dynamic SQL statement, then start a loop and `fetch` it into a RECORD variable (or multiple variables). Example: https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.apdv.plsql.doc/doc/c0053894.html — kfinity, Feb 28 '18 at 21:52

score 0 · Answer 2 · answered Feb 28 '18 at 19:26

0

See this: Search All Fields In All Tables For A Specific Value (Oracle)

You'll have to figure out how to check for white space, but the bulk of your problem has been solved before.

answered Feb 28 '18 at 19:26

n8.

1,732
3
16
38

How to change table you query from in FOR loop IN clause with PL/SQL

2 Answers2