procedure builder works but plus "locks up"

From: Eric Givler <egivler_at_flash.net>
Date: Wed, 27 Sep 2000 17:33:27 GMT
Message-ID: <HbqA5.9264$864.634339_at_news.flash.net>

I have a procedure that locks up when I run it in SQL*Plus but I can step through it fine in Procedure Builder. I have no idea whey it does not work in Plus and would like to figure out a way to "step through it" or figure out why PLUS is "hanging". Any ideas? I'm running SQL*plus 8.0.6 and Procedure Builder is from the developer 6i install.

The source follows:

rem my_lame_script.sql
rem remove set serverout, declare and the ending begin..end in procedure builder to debug

set serveroutput on size 10000

DECLARE
procedure areas_and_subareas is
/*
set serveroutput on size 100000

DECLARE
rem paprgrsf_areas_and_subareas.sql
rem this will display the area/subarea list that appears in the paprgrsf form
rem when a park and park unit type have been selected */
  areas1 NUMBER;
  num_area NUMBER;
  num_sub_area NUMBER;
  areass NUMBER;
  Eid_area NUMBER;
  Eid_subarea NUMBER;

  ndb_owner_Eid parks.eid%type := '6213';   ndb_code_code park_units.park_unit_type_code%type := 'OGT_SITE';

  CURSOR areas IS
   SELECT eid, park_unit_identifier
     FROM park_units

   WHERE eid IN (SELECT park_unit_eid_1
                   FROM park_units
                  WHERE park_eid = ndb_owner_Eid /* &park_eid or
:PARK_NAME.ndb_owner_eid  */
                    AND park_unit_type_code = ndb_code_code  /*
upper('&pu_type')  */
                );

  ident park_units.park_unit_identifier%TYPE;

  CURSOR sub_areas IS

     SELECT eid
          , park_unit_identifier
       FROM park_units
      WHERE park_unit_level = 2
        AND park_unit_eid_1 = Eid_area;

  sub_ident park_units.park_unit_identifier%TYPE;

/*
  CURSOR p_units IS
   SELECT DISTINCT park_unit_type_code FROM park_units

   WHERE park_unit_level = 99 AND park_unit_eid_1 IS NULL
   AND park_eid = :PARK_NAME.ndb_owner_eid
   AND park_unit_type_code = :FACILITY_TYPE.ndb_code_code;

*/

   ndb_num_areas_subareas number := 0;

   chk_all                boolean;

   type area_rec_type is record
      ( ndb_area     park_units.park_unit_identifier%type
       ,ndb_area_eid park_units.eid%type
       ,ndb_subarea  park_units.park_unit_identifier%type
       ,ndb_subarea_eid park_units.eid%type );

   type area_tabtype is table of area_rec_type
        index by binary_integer;

   area_subarea area_tabtype;

BEGIN   NDB_NUM_AREAS_SUBAREAS := 0;
  CHK_ALL := Null;

/*
  go_block('AREA_SUBAREA');
  clear_block;

  go_block ('FACILITY_TYPE');
  go_item('ndb_description');
  do_key('LIST_VALUES');

*/

  BEGIN     num_area := 0;
--go_block('AREA_SUBAREA');
--Clear_Block;

    OPEN areas;

--SET_APPLICATION_PROPERTY(cursor_style, 'BUSY');

    LOOP

      FETCH areas INTO Eid_area, ident;
      EXIT WHEN areas%NOTFOUND OR areas%NOTFOUND IS NULL;

      num_sub_area := 0;
      OPEN sub_areas;

      LOOP
        FETCH sub_areas INTO Eid_subarea, sub_ident;
        EXIT WHEN sub_areas%NOTFOUND OR sub_areas%NOTFOUND IS NULL;

        AREA_SUBAREA(ndb_num_areas_subareas).ndb_area := ident;
        AREA_SUBAREA(ndb_num_areas_subareas).ndb_area_eid := Eid_area;
        AREA_SUBAREA(ndb_num_areas_subareas).ndb_subarea := sub_ident;
        AREA_SUBAREA(ndb_num_areas_subareas).ndb_subarea_eid := Eid_subarea;
 num_area := 1;
        num_sub_area := 1;

 --next_record;
        eid_subarea := NULL;

        NDB_NUM_AREAS_SUBAREAS := NDB_NUM_AREAS_SUBAREAS + 1;


      END LOOP;

      CLOSE sub_areas;

      if (num_sub_area =0) then
        NDB_NUM_AREAS_SUBAREAS := NDB_NUM_AREAS_SUBAREAS + 1;
        AREA_SUBAREA(ndb_num_areas_subareas).ndb_area := ident;
        AREA_SUBAREA(ndb_num_areas_subareas).ndb_area_eid := Eid_area;
        --Next_Record;
      end if;

      eid_area := NULL;

    END LOOP;     CLOSE areas;

--first_record;
--for sites directly under park

  END;   --SET_APPLICATION_PROPERTY(cursor_style, 'DEFAULT');

  FOR i in 1..ndb_num_areas_Subareas LOOP

     begin
       dbms_output.put( RPAD(AREA_SUBAREA(i).ndb_area,30) || ' ' );
       dbms_output.put( LPAD(AREA_SUBAREA(i).ndb_area_eid,10) || ' ' );
       dbms_output.put( RPAD(AREA_SUBAREA(i).ndb_subarea,30) || ' ');
       dbms_output.put_line( LPAD(AREA_SUBAREA(i).ndb_subarea_eid,10) );
       exception
          when no_data_found then
             dbms_output.new_line; -- subarea may not be populated
     end;

   END LOOP; END areas_and_subareas;
begin

   areas_and_subareas;
end;
/ Received on Wed Sep 27 2000 - 19:33:27 CEST