In PL/SQL can I pass a value from anonymous block into the calling program

Question

For example I have a loop that is calling an anonymous block to do an action and as a result of that action a value is retrieved. Can I pass that value to the main program?

PROCEDURE MY_PROCEDURE
    IS
        my_id NUMBER;
    BEGIN
        FOR i IN 1..v_tab.COUNT LOOP
            IF v_tab(i).status = 'NEW' THEN

                DECLARE
                    v_row  my_table1%ROWTYPE;             

                BEGIN
                   someprocedure(var1 => v_tab(i).field1,
                                 var2 => v_tab(i).status,
                                 id    => my_id  -- OUT PARAMETER 
                                 );

                END;
            ELSE
                my_id := v_tab(i).id;
                ..... do something else
            END IF;
            --other steps performed here, utilizing my_id
        END LOOP;

        -- other steps performed here  
END MY_PROCEDURE;

The v_tab is defined as follows:

TYPE v_record IS RECORD
(
   id NUMBER,
   field1 VARCHAR2(50),
   status VARCHAR2(10)
);

TYPE v_tab_type IS TABLE OF v_record;

v_tab  v_tab_type;

Answer 1

Your question is really about the scope of your variables.

When you declare a variable in the declaration portion of a procedure/function/package/anonymous block, that variable is accessible over the entirety of that procedure/function/package/anonymous block.

For example, say you have a package that looks like this:

CREATE OR REPLACE PACKAGE variable_scope_pkg
  gv_global_public VARCHAR2(1);

  PROCEDURE p1 (in_param1 VARCHAR2,
                out_param1 VARCHAR2);
END variable_scope_pkg;
/

CREATE OR REPLACE PACKAGE BODY variable_scope_pkg
  gv_global_private VARCHAR2(1);

  PROCEDURE p1 (in_param1 VARCHAR2,
                out_param1 VARCHAR2)
  IS
    v_local VARCHAR2(1);
  BEGIN
  
    DECLARE
      v_sub_local VARCHAR2(1);
    BEGIN
      out_param1 := v_local || v_sub_local;
    END;
  
  END;
  
END variable_scope_pkg;
/

The gv_global_public variable defined in the package scope is available to be set or read by any code which has execute privileges on the package, including code inside the package itself. The gv_global_private variable defined in the package body is available to be set or read only by code within the package.

The p1 parameters and v_local variable are available to be set/read by any code within the p1 procedure, but the v_sub_local variable is only available to be set/read by any code within the anonymous block.

So, the answer to your question of "can I pass a value from anonymous block into the calling program?" is yes, but you must store the value in a variable that is within the scope of the calling program.

On a separate note, if you've got a nested anonymous block inside a procedure, you should consider whether it's absolutely necessary. Perhaps you should be pulling the anonymous block into its own procedure/function and then passing the values around via parameters.