sql - search line break in several tables and get the rowid

Question

I have a list of tables and columns stored into "CAMPOS_BL_FS_050321_2" and i want to loop through that tables, only can i get one rowid for table, i need create an array to stored all rowid from a table, but i can't get the right code to works

DECLARE
  match_count varchar2(30 byte);
BEGIN
  FOR t IN (SELECT SCHEMA_NAME,COLUMN_NAME,TABLE_NAME,DBLINK FROM CAMPOS_BL_FS_050321_2) LOOP
    EXECUTE IMMEDIATE
   'select rowid FROM ' || t.SCHEMA_NAME || '.' || t.TABLE_NAME || t.DBLINK ||' WHERE instr(' || t.COLUMN_NAME  || ', chr(10)) > 0 and rownum=1'
   INTO match_count;
    IF match_count IS NOT null THEN
        insert into log_tabla values(t.COLUMN_NAME ,t.TABLE_NAME, match_count);
      COMMIT;
    END IF;
  END LOOP;
END;
/

标签： sqloracleplsql

Answer 1

You don't specify what error you're getting (please do next time), just that you can't get it to work so this is just my 2 cents.

match_count is a scalar value. It can hold a single value - 1 rowid in your case. But your select statement can/will have multiple rows so you need to store that in a variable that can hold an array of values. In pl/sql that is called a collection - you can read all about that [here][1]. Typically you create a TYPE in your block of the records you want to store and use that:

DECLARE
  TYPE rowid_t IS TABLE OF rowid INDEX BY BINARY_INTEGER;
  l_rowids rowid_t;
BEGIN
  SELECT rowid BULK COLLECT INTO l_rowids FROM h_item_all;
  FOR i IN 1 .. l_rowids.COUNT 
  LOOP
    dbms_output.put_line(l_rowids(i));
  END LOOP;

  FOR t IN (SELECT SCHEMA_NAME,COLUMN_NAME,TABLE_NAME,DBLINK FROM CAMPOS_BL_FS_050321_2) 
  LOOP
    EXECUTE IMMEDIATE
'select rowid FROM ' || t.SCHEMA_NAME || '.' || t.TABLE_NAME || t.DBLINK ||' WHERE instr(' || t.COLUMN_NAME  || ', chr(10)) > 0 and rownum=1'
    BULK COLLECT INTO l_rowids;

  FOR i IN 1 .. l_rowids.COUNT 
  LOOP
    insert into log_tabla values(t.COLUMN_NAME ,t.TABLE_NAME, l_rowids(i)); 
  END LOOP;   
  COMMIT;
  END LOOP;
END;
/