Dynamic sql invalid relational operator

You should not use the declare section twice in anonymous block.

Try this:

SET SERVEROUT ON ;

DECLARE
   SRCE_SB_CATGRY_ID   VARCHAR2 (1000);
   str1   VARCHAR2 (10000);
   x      SYS_REFCURSOR;
BEGIN
   FOR i
      IN (SELECT ISO_CNTRY_ID, X.COLUMN_NAME
            FROM iso_cntry I,
                 (SELECT DISTINCT COLUMN_NAME
                    FROM ALL_tab_cols@suplr_md_qa2
                   WHERE     table_name = 'TEMP_ARIBA'
                         AND COLUMN_NAME != 'SSC_ID') x
           WHERE ISO_CNTRY_DESC_TXT LIKE '' || COLUMN_NAME || '%')
   LOOP
         str1 :=    ' SELECT DISTINCT ssc_id  '
               || ' FROM stddata_suplr.temp_ariba@suplr_md_qa2 '
               || ' WHERE '
               || ' '
               || i.column_name
               || ' = ''Y'' ';
         
         OPEN x FOR str1;

         LOOP
            FETCH x INTO SRCE_SB_CATGRY_ID;

            EXIT WHEN x%NOTFOUND;



            NULL;
         END LOOP;
      END;
   END LOOP;

   END;

Please manage the logic according to your requirement.

This line in the driving looks wrong:

WHERE ISO_CNTRY_DESC_TXT LIKE '' || COLUMN_NAME || '%'

Try it without concatenating the leading quote:

WHERE ISO_CNTRY_DESC_TXT LIKE COLUMN_NAME || '%'

Many thanks for all your suggestions. It was a data error for one of the iso_cntry_id . I filtered it in my where clause and it's working fine now.

Changes which I made in first loop

FOR i
      IN (SELECT ISO_CNTRY_ID, X.COLUMN_NAME
            FROM iso_cntry I,
                 (SELECT DISTINCT COLUMN_NAME
                    FROM ALL_tab_cols@suplr_md_qa2
                   WHERE table_name = 'TEMP_ARIBA') x
           WHERE     ISO_CNTRY_DESC_TXT LIKE '' || COLUMN_NAME || '%'
                 AND i.ISO_CNTRY_ID != 18)
   LOOP

best regards, Vijay