CODEWITHSUNDEEP
oracle-databaseplsql
Dinidu Hewage
Dinidu Hewage

Reputation: 2191

How to continue when no_data_found exception raised?

I have a value that i dont know exactly in which column it is inserted. I can determine that since this table columns are named as C1....C99. I am going to find the column using the following code. But when the no data found exception is called, I cant start my loop again.

DECLARE
   i number:=1;
   k boolean := true;
   output number;
   stmt varchar(500);
BEGIN
  <<repeat>>
  while(k)
  loop
    DBMS_OUTPUT.PUT_LINE( 'c column -->'||i );
   EXECUTE IMMEDIATE 'select 1 from X_TAB t where t.c'||i||'= ''NOTE_NFE'''

     into output;
     IF output = 1 then
        DBMS_OUTPUT.PUT_LINE( 'c column'||i );
        k:=false;
     END IF;
     i:=i+1;
     IF i = 100 then
        k:=false;
     END IF;
END LOOP;
EXCEPTION 
     when no_data_found then
       i:=i+1;
       if i = 100 then
          k:=false;
       end if;
       goto repeat;
END;
/

Upvotes: 1

Views: 12267

Answers (3)

Walter_Ritzel
Walter_Ritzel

Reputation: 1397

I think that moving your try catch to inside the loop will do the trick for you.

DECLARE
   i number:=1;
   k boolean := true;
   output number;
   stmt varchar(500);
BEGIN
  <<repeat>>
  while(k)
  loop
    DBMS_OUTPUT.PUT_LINE( 'c column -->'||i );
    begin
       EXECUTE IMMEDIATE 'select 1 from X_TAB t where t.c'||i||'= ''FISCAL_NOTE_NFE''' into output;
    exception when no_data_found then
          output := 0; --set a default value
          dbms_output.put_line('no data found.');
    end;
     IF output = 1 then
        DBMS_OUTPUT.PUT_LINE( 'c column'||i );
        k:=false;
     END IF;
     i:=i+1;
     IF i = 100 then
        k:=false;
     END IF;
END LOOP;
END;
/

Upvotes: 4

Mennan
Mennan

Reputation: 81

You can also use select count(1) from X_TAB

Upvotes: 1

MT0
MT0

Reputation: 167972

You can use:

SELECT CASE
       WHEN EXISTS (
              SELECT 1
              FROM   X_TAB
              WHERE  dynamic_column_name = 'FISCAL_NOTE_NFE'
            )
       THEN 1
       ELSE 0
       END
FROM   DUAL

It will never throw the exception as it will always return exactly one row.

You can use it like:

DECLARE
  i      INT := 0;
  output INT;
BEGIN
  FOR k IN 1 .. 100 LOOP
    DBMS_OUTPUT.PUT_LINE( 'c column -->'||k );
    EXECUTE IMMEDIATE 'SELECT CASE WHEN EXISTS(SELECT 1 FROM X_TAB WHERE  c'||k||' = ''FISCAL_NOTE_NFE'') THEN 1 ELSE 0 END FROM DUAL'
      INTO output;
    IF output = 1 THEN
      DBMS_OUTPUT.PUT_LINE( 'c column'||k );
      i := k;
      EXIT;
    END IF;
  END LOOP;
  -- Do something with i
END;
/

Upvotes: 1

Related Questions