INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

PLSQL| Goto Label Hangs Script

PLSQL| Goto Label Hangs Script

(OP)
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.

RE: PLSQL| Goto Label Hangs Script

(OP)
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.

RE: PLSQL| Goto Label Hangs Script

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.

RE: PLSQL| Goto Label Hangs Script

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?   

RE: PLSQL| Goto Label Hangs Script

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:

CODE

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;
/

RE: PLSQL| Goto Label Hangs Script

(OP)
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 smile  .

RE: PLSQL| Goto Label Hangs Script

Albitzt,

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

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

RE: PLSQL| Goto Label Hangs Script

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close