Nesting an anonymous block

This routine works fine.
What sort of behaviour are you getting (eg compilation error, never-ending loop ?)

DECLARE
 cursor c_1 is select 1 from dual;
 v_num number(1);
BEGIN
  OPEN c_1;
  LOOP
    FETCH c_1 into v_num;
    IF C_1%FOUND then
      dbms_output.put_line('a');
      IF v_num = 1 then
       BEGIN
        dbms_output.put_line('b');
        SELECT 2 INTO v_num from dual;
        SELECT 3 INTO v_num from dual;
    --    INSERT
    --    UPDATE
    --    INSERT
       EXCEPTION
--        when dup_val then null;
        when no_data_found then null;
       END;
      END IF;
      begin
        dbms_output.put_line('c');
        v_num := 9;
      exception
        when value_error then
          raise_application_error(-20001,'Bad');
      end;
    ELSIF c_1%NOTFOUND THEN
     dbms_output.put_line('d');
             exit;
     --INSERT
    ELSE
     dbms_output.put_line('e');
     NULL;
    END IF;
  END LOOP;
  dbms_output.put_line('f '||v_num);
  IF v_num < 10 then
       dbms_output.put_line('g');
       BEGIN
        SELECT 4 INTO v_num from dual;
        SELECT 5 INTO v_num from dual;
    --    INSERT
    --    UPDATE
    --    INSERT
       EXCEPTION
--        when dup_val then null;
        when no_data_found then null;
       END;
       dbms_output.put_line('h');
  END IF;
  CLOSE c_1;
  dbms_output.put_line('i');
  COMMIT;
END;


 

Thank you very much for your reply. I have gotten the script working. I believe the problem is that I was positioning my BEGIN in the wrong place. I was enclosing the IF statement in BEGIN and END. Once I wrapped the Select statement in the ELSE clause of the statement in BEGIN and END the errors went away.

I'm a little unclear on the syntax for anonymous blocks. It seems as though you need to keep everything directly anchored to a select statement. Is that correct?

Thanks again.

Rebecca

Anonymous blocks either start as

BEGIN

.....

END;

or

DECLARE

  local variables

BEGIN

  code....

EXCEPTION

END;

If you have a SELECT statement embedded (i.e. implicit SQL) then you have to SELECT columns INTO local variables and you should preprare for ORACLE exceptions being raised (i.e. TOO_MANY_ROWS, or NO_DATA_FOUND) and then handle them in the EXCEPTION area, then exit the anonymous block, and continue processing in the calling block (which could be either another anonymous block or a named block (i.e. a stored procedure or function).

Gio

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/Refund.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer