Checking if the values found by the cursor meet the new condition

So I'm back with the procedure for searching for keys / identifiers in the schematic.

here is the ready procedure:

CREATE OR REPLACE PROCEDURE SIEROT(i_table_name VARCHAR2) IS
  strSelect      VARCHAR2(32767);
  c                  SYS_REFCURSOR;

  vTYPEPKSTRING  PRODUCTS.TYPEPKSTRING%TYPE;

  TYPE c_list IS TABLE of PRODUCTS.TYPEPKSTRING%type INDEX BY binary_integer; 
  TYPEPK_list c_list; 
  counter integer := 0; 
BEGIN 
  strSelect := 'SELECT DISTINCT i.TYPEPKSTRING ' ||
               '  FROM ' || i_table_name || ' i ' ||
               '  LEFT OUTER JOIN COMPOSEDTYPES c ' ||
               '    ON i.TYPEPKSTRING = c.PK ' ||
               '  WHERE c.PK IS NULL';

    OPEN c FOR strSelect;


  FETCH c INTO vTYPEPKSTRING;

  WHILE c%FOUND LOOP
    counter := counter + 1; 
    TYPEPK_list(counter) := vTYPEPKSTRING; 

    dbms_output.put_line('TABLE: '||i_table_name||'('||counter||'):'||TYPEPK_list(counter)); 

    FETCH c INTO vTYPEPKSTRING;
  END LOOP; 

  CLOSE c;
EXCEPTION
  WHEN OTHERS THEN
    IF c%ISOPEN THEN
      CLOSE c;
    END IF;
END SIEROT;

And here's the call:

set serveroutput on
DECLARE
    ind integer := 0;
BEGIN
FOR ind IN (select table_name from all_tab_columns where column_name='TYPEPKSTRING' AND table_name!='COMPOSEDTYPES')
  LOOP
    BEGIN
        SIEROT(ind.table_name);
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        null;
    END; 
  END LOOP;
END;

This procedure looks for all the values for 'typepkstring' on the schema. These are the values of keys, which can be found in the 'composedtypes' table in the PK column. More specifically, in the framework of this procedure we find identifiers that appear on the schema and are not included in this collective view in the column pk. In my particular bottle, there are three of them. The procedure informs me in which table the key was found. Everything works great. However, I have to add another one to this functionality. I have about 132 tables on this schama, which contain columns 'sourcepk' and 'targetpk'. These columns also contain number keys. Now I want to make sure about one thing: Both of these columns, i.e. both 'sourcepk' and 'targetpk' should be empty for those of my 'typepkstrings' found.

I know how to find tables with interesting columns:

select distinct  table_name from all_tab_columns where column_name='SOURCEPK' OR column_name ='TARGETPK';

Generally, I wanted to convert this part of the procedure:

FETCH c INTO vTYPEPKSTRING;

  WHILE c%FOUND LOOP
    counter := counter + 1; 
    TYPEPK_list(counter) := vTYPEPKSTRING; 

    dbms_output.put_line('TABLE: '||i_table_name||'('||counter||'):'||TYPEPK_list(counter)); 

    FETCH c INTO vTYPEPKSTRING;
  END LOOP;

I tried to add here in the for loop the above select to tables containing sourcepk and targetpk and then the if condition on the vTYPEPKSTRING variable. whether after a fetch operation in a given pass, found 'typepkstring' has some values for these columns or not. However, nothing came of it Generally, I encounter new problems without interruption. And I really need help, at least in targeting, though I would not have despised a code. Thank you in advance for any hints.:)

Solution

I've combined, combined and figured out. I gave up the previous construction of the procedure and decided to do it all over again.

I used 4 nested cursors, of which 2 are ref cursors. Everything works according to the assumptions. Thanks for the advice on the previous post.

CREATE OR REPLACE PROCEDURE SIEROTKA
IS
    t_name           VARCHAR2(30);
    od_typePK      PRODUCTS.TYPEPKSTRING%TYPE;
    t_nameST       VARCHAR(30);
    od_Source  cat2prodrel.SOURCEPK%TYPE;
    od_Target  cat2prodrel.TARGETPK%TYPE;

    sv_tname VARCHAR2(32767);
    Get_typePK      SYS_REFCURSOR;

    sv_tnameST      VARCHAR2(32767);
    Get_Sierotki         SYS_REFCURSOR;

    CURSOR Get_PK_TN
    IS
        SELECT table_name FROM all_tab_columns
        WHERE column_name='TYPEPKSTRING' AND
        table_name!='COMPOSEDTYPES';

    CURSOR Get_ST_TN
    IS
        SELECT DISTINCT table_name from all_tab_columns
        WHERE column_name='SOURCEPK' OR 
        column_name='TARGETPK';

BEGIN

    OPEN Get_PK_TN;
    LOOP
        FETCH Get_PK_TN INTO t_name;
        EXIT WHEN Get_PK_TN%NOTFOUND;

        sv_tname := 'SELECT DISTINCT i.TYPEPKSTRING FROM ' || t_name || ' i LEFT OUTER JOIN COMPOSEDTYPES c ON i.TYPEPKSTRING=c.PK WHERE c.PK IS NULL';

        OPEN Get_typePK FOR sv_tname;
        LOOP
            FETCH Get_typePK INTO od_typePK;
            IF Get_typePK%FOUND THEN
                    dbms_output.put_line('Znalezione Sirotka to: ' || t_name || ' --------- ' || od_typePK);
            ELSE
                    EXIT WHEN Get_typePK%NOTFOUND;
            END IF;

            OPEN Get_ST_TN;
            LOOP
                FETCH Get_ST_TN INTO t_nameST;
                EXIT WHEN Get_ST_TN%NOTFOUND;

                sv_tnameST := 'SELECT SOURCEPK, TARGETPK FROM ' || t_nameST || ' WHERE PK=' || od_typePK || '';

                OPEN Get_Sierotki FOR sv_tnameST;
                LOOP
                    FETCH Get_Sierotki INTO od_Source, od_Target;
                    IF Get_Sierotki%FOUND THEN
                            dbms_output.put_line('Jednak to nie sierotka, bo posiada wpis: ' || t_name || ' --------- ' || od_typePK || ' ____________ ' || t_namest || ' --------- ' || od_Source || ' --------- ' || od_Target);
                    ELSE
                            EXIT WHEN Get_Sierotki%NOTFOUND;
                    END IF;    
                END LOOP;
                CLOSE Get_Sierotki;

            END LOOP;
            CLOSE Get_ST_TN;

        END LOOP;
        CLOSE Get_typePK;

    END LOOP;
    CLOSE Get_PK_TN;

END;

For this, a simple one-line call in the begin end block:

set serveroutput on
BEGIN
    SIEROTKA;
END;
/

Until next, see you soon.