PLSQL| Goto Label Hangs Script 2

[albitzt]

Hello,

I am in the process of writing a script and I'm having some difficulty with using 'GOTO' label. Once I hit return it just sits there. Here is the generic code:


set serveroutput on
DECLARE
var_num1 number(25) NOT NULL := 0;
sql_stmt varchar2(30);
BEGIN

<<BLOCK_CHECK>>
IF var_num1 > 3 THEN
dbms_output.put_line('STOPPROG');
ELSE
var_num1 := var_num1 + 1;
dbms_output.put_line(var_num1);
sql_stmt := 'BLOCK_' || var_num1;
dbms_output.put_line(sql_stmt);
END IF;
GOTO BLOCK_1; -- The idea here is that this will be replaced with running variable sql_stmt which will goto the current count of the block.


<<BLOCK_1>>
dbms_output.put_line('THIS IS BLOCK 1');
GOTO COMMON_BLOCK;

<<BLOCK_2>>
dbms_output.put_line('THIS IS BLOCK 2');
GOTO COMMON_BLOCK;

<<BLOCK_3>>
dbms_output.put_line('THIS IS BLOCK 2');
GOTO COMMON_BLOCK;

<<COMMON_BLOCK>>
dbms_output.put_line('THIS IS THE COMMON ACTION');
GOTO BLOCK_CHECK;
END;
/

Also, by no means am I great at plsql , so if their is a more logical way to accomplish what I am doing and it's not overly complicated, please feel free to share.

Thanks.

 

[albitzt]

An update...So I am partially just impatient. The script does come back with:

Declare
*
Error at line 1:
ORA-06500: PL/SQL: storage error
ORA-04030: out of process memory when trying to allocate 16356 bytes
ORA-06512: at sys.dbms_output", line 127"
ORA-06512: at sys.dbms_output", line 113"
ORA-06512: at line 19

Still just as confused about this, so any feedback is still appreciated.

Thanks.

 

[KenCunningham]

Basically, you are running out of memory for the process. I'm sorry I don't have sufficient time to explain fully (and it depends on the OS you're using to some extent), but if you Google (other search engines are available) the ORA 04030 notice above, you should get some clues.

The internet - allowing those who don't know what they're talking about to have their say.

 

[stefanhei]

If I read the code right you coded an infinite loop, so your session "sitting there" until you run out of memory sounds normal.
What are you trying to accomplish?

 

[stefanhei]

Ok, after rereading I think I can guess what you try to do.
You want to execute the code-block identified by var_num1, then some common action. After that you want to process the next code-block and the common action until there are no more code-blocks.
Without an infinite loop and without goto you could do that like this:

set serveroutput on
DECLARE
   var_num1 NUMBER(25) NOT NULL := 1;
   sql_stmt VARCHAR2(30);
BEGIN
   WHILE var_num1 < 3 LOOP
      dbms_output.put_line(var_num1);
      sql_stmt := 'BLOCK_' || var_num1;
      dbms_output.put_line(sql_stmt);
      CASE var_num1
	     WHEN 1 THEN dbms_output.put_line('THIS IS BLOCK 1');
         WHEN 2 THEN dbms_output.put_line('THIS IS BLOCK 2');
		 WHEN 3 THEN dbms_output.put_line('THIS IS BLOCK 3');
	  END CASE;
      var_num1 := var_num1 + 1;
      dbms_output.put_line('THIS IS THE COMMON ACTION');	  
   END LOOP;
   dbms_output.put_line('STOPPROG');
END;
/

 

[albitzt]

stefanhei --

Dead on!!! This is perfect. It is exactly what I was trying to accomplish. using the CASE and loop makes a lot of sense now that I'm looking at the way you constructed this.

Thanks so much for the help .

 

[SantaMufasa]

Albitzt,

Be sure to thank Stefan by clicking on his post's

button.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[KenCunningham]

Thanks for my star too, though I don't feel it's entirely deserved given my minimal contribution

The internet - allowing those who don't know what they're talking about to have their say.