cursor output use in select

Question

My last problem: select with variable parameter in the procedure

okay, I have one more question. I would like to extend this procedure to another element. Well, we have already chosen these identifiers from the typepkstring column, from all tables on the schema, and which are not in the PK column in the Composedtypes table. It works great. Added to this is a new condition. After choosing what I had before and what I have already achieved, I now have to check whether these specific selected identifiers have empty fields in the tables containing SOURCEPK and TARGETPK colums. To do this, first tried to break this problem again into small, preferably on a specific table. Here is the select which he obtains from the previous question:

SELECT DISTINCT METAINFORMATIONS.TYPEPKSTRING
               FROM  METAINFORMATIONS
               LEFT OUTER JOIN COMPOSEDTYPES c 
               ON METAINFORMATIONS.TYPEPKSTRING = c.PK 
               WHERE c.PK IS NULL;

I select doing what I want to get now:

SELECT DISTINCT METAINFORMATIONS.TYPEPKSTRING 
               FROM METAINFORMATIONS
               LEFT OUTER JOIN CAT2CATREL d
               ON METAINFORMATIONS.TYPEPKSTRING = d.TYPEPKSTRING
               WHERE d.sourcepk IS NULL AND d.targetpk IS NULL AND metainformations.typepkstring=8796093055031;

The metainformations table is a table that "naturally" meets the conditions of the previous procedure In order to achieve what she needs only parameterized, I think that it should have the following appearance:

Generally in select which I gave the place of the CAT2CATREL table more respect, set up table names that meet this select:

select to extract table names that needs:

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

In addition, the number 8796093055031 should be replaced by the value from the first cursor or vTYPEPKSTRING. But can I act in this way? Maybe I should create a second cursor that references this value.

I hope I have clearly explained this problem as I can explain it once in the comments. Thanks for any advice. Update question: so, I modified the select from the previous procedure to this character:

strSelect := 'SELECT DISTINCT m.TYPEPKSTRING ' ||
  ' FROM ' || i_table_name || ' m ' ||
  ' LEFT OUTER JOIN ' || is_table_name || ' d ' ||
  ' ON m.TYPEPKSTRING = d.TYPEPKSTRING ' ||
  ' WHERE d.sourcepk IS NULL AND ' ||
        ' d.targetpk IS NULL AND ' ||
        ' m.typepkstring IN (select count(*) from (SELECT DISTINCT m2.TYPEPKSTRING ' ||
                            ' FROM ' || i_table_name || ' m2 ' ||
                            ' LEFT OUTER JOIN COMPOSEDTYPES c2 ' ||
                            '  ON m2.TYPEPKSTRING = c2.PK ' ||
                            ' WHERE c2.PK IS NULL)) ';

as a result of the procedure so constructed, I get keys for which I wanted but the entry is for all tables that meet select in the modified call. This means that instead of receiving, say, 2 keys, he receives 2 same keys for each table. I tried it somehow counting but then I do not receive anything at the exit. modified 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
            FOR inds IN (select distinct table_name from all_tab_columns where column_name='SOURCEPK' OR column_name ='TARGETPK')
                LOOP
                  BEGIN
                    SIEROT(ind.table_name,inds.table_name);
            EXCEPTION 
                WHEN NO_DATA_FOUND THEN
                null;
                  END;
                END LOOP;
            END; 
  END LOOP;
END;

Answer 1

As far as replacing 8796093055031 goes, you can just use the first statement as a subquery in the second statment:

SELECT DISTINCT m.TYPEPKSTRING 
  FROM METAINFORMATIONS m
  LEFT OUTER JOIN CAT2CATREL d
    ON m.TYPEPKSTRING = d.TYPEPKSTRING
  WHERE d.sourcepk IS NULL AND
        d.targetpk IS NULL AND
        m.typepkstring IN (SELECT DISTINCT m2.TYPEPKSTRING
                             FROM  METAINFORMATIONS m2
                             LEFT OUTER JOIN COMPOSEDTYPES c2
                               ON m2.TYPEPKSTRING = c2.PK 
                             WHERE c2.PK IS NULL);

As for the rest, if I understand what you're trying to do it seems to me that you'll need to use dynamic SQL as shown in the answer to your previous question.