Nested Begin in a script file ,Stack Overflow during script execution

Question

Hi, Recently I'm having requirement to generate script file from excel sheet. I'm able to acomplish task almost. Bu the problem is usage of Local varibales are more in script file are more.Becuase of more Local variale, During execution of script file in toad,it throws an error PL/SQL 102 Stack overflow exception during insertion script. Here is the syntax

"declare Q2 integer;
begin
select table1_seq.nextval into Q2 from dual;
insert into table1 values(Q2,sysdate,'BATCH',sysdate,'BATCH',sysdate,'BATCH',2,'N',null,null);
insert into table2 values(Q2,table2_seq.nextval,sysdate,'BATCH',sysdate,'BATCH',sysdate,'BATCH',2,'BLAH','Y',null,null);
"

Notice that in the above code, I didnot have "end;" statement after declaring 'Q2' Reason is the i want scope of this variable 'Q2' available till end.

Under Q2 I may have another declare A1 A2

Some thing Look like this

Declare Q1 int;
Begin
 Insert()
       Declare A1 int;
         Begin
            Insert()
               Declare S1 int;
                  Begin
                     insert()......
           ........................
           .......................
           End;
           End;
           End;

In the above I may have 100-300 vairbales ,Since it looks like nested, Because of Nesting Stack error is poping up. If that is case ,is ther any possiblity of increase Nested Depth level so tht it will parse all script varibales. Or Is there any reason of having stack overflow.

even though Im not having nested IF or For Loops which goes out of scope.But in my script file while runing im getting "Stack overflow error". Will the above situation also falls into nested case. Please help me out ASAP.

-Mahender

Answer 1

"Declare" is not used for each variable, it signifies the start of the "declaration block".

DECLARE
    Q1 INTEGER;
    A1 INTEGER;
    S1 INTEGER;
    ...
BEGIN
    INSERT INTO foo.table_name (...) VALUES (...);
    ...
END;
/

You put all of your variable declarations between DECLARE and BEGIN, and all of your code between BEGIN and END.

This is not to say that you can't nest anonymous blocks, it's just that you seem to misunderstand the meaning of the DECLARE keword.