anonymous pl/sql block in set up

[Sathi Chowdhury]

Hi Sujit,
I am trying to execute an anonymous pl/sql block from setup

my setup looks like
<setup>
    <include connection-id="1" file="test/oracle/bulk.sql" />
</setup>

and th bulk.sql code looks like this :

BEGIN
DECLARE
type number_collection is table of Integer;
  ARG_LIST number_collection;
CURSOR C1 is select id from TABLE1 where rownum <6;
   BEGIN
     delete from table2 where 1=1;
    OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT INTO ARG_LIST  LIMIT 100;
    TEST_BULK( ARG_LIST ); --basically inserts   ---record into  table2
  exit when c1%notfound;
  END LOOP;
END
;
END
;

--------------------
and I get the following error  and I am clueless...pls help

  [sqlunit] Error (net.sourceforge.sqlunit.parsers.ParseException) encountered:
Encountered "<EOF>" at line 17, column 1.
  [sqlunit] Was expecting one of:
  [sqlunit]     <START_OTHER_SQL_CHAR> ...
  [sqlunit]     <OTHER_SQL_DELIMITER> ...
  [sqlunit]     <OTHER_SQL_CHAR> ...
  [sqlunit]
  [sqlunit] sqlunit-ant: System error (net.sourceforge.sqlunit.parsers.ParseExc
ption): Encountered "<EOF>" at line 17, column 1.
  [sqlunit] Was expecting one of:
  [sqlunit]     <START_OTHER_SQL_CHAR> ...
  [sqlunit]     <OTHER_SQL_DELIMITER> ...
  [sqlunit]     <OTHER_SQL_CHAR> ...
  [sqlunit]

[Sujit Pal]

Hi Sathi,

A free standing ; on a line by itself signifies the end of the statement, and I dont think END is tagged as a valid statement. If you are just trying to close out the BEGINs, then put the ; on the same line, and then put an extra ; or / (maybe preferable to distinguish it, although it takes both) free standing, so your code will look like this:

BEGIN
DECLARE
type number_collection is table of Integer;
ARG_LIST number_collection;
CURSOR C1 is select id from TABLE1 where rownum <6;
BEGIN
delete from table2 where 1=1;
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO ARG_LIST LIMIT 100;
TEST_BULK( ARG_LIST ); --basically inserts ---record into table2
exit when c1%notfound;
END LOOP;
END;
END;
/

-sujit

[Sathi Chowdhury]

thanks Sujit for taking time to reply.
I changed the script as you suggested...actually I tried to create a proc through this script below

create or replace procedure bulkwr(arg_dummy number default 0)is
ARG_LIST number_collection;
CURSOR C1 is select document_id from documents where rownum < 6;
BEGIN
delete from table2;
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO ARG_LIST  LIMIT 100;
TEST_BULK( ARG_LIST );
COMMIT;
exit when c1%notfound;
END LOOP;
END;
;

The procedure gers created but with a compilation error  as below......
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/48     PLS-00103: Encountered the symbol "" when expecting one of the
         following:
         begin function package pragma procedure subtype type use
         <an identifier> <a double-quoted delimited-identifier> form   current cursor external language

It's strange ! however I try to ORGANIZE lines the first line at the end always cause an error...no idea why is this happening?
the same script works fine in sqlplus etc.

[Sujit Pal]

Hi Sathi,

Does this work if the final ; is not there?

-sujit

[Sathi Chowdhury]

no..it does not.
If I pick the procedure code  from all_source and run the script it compiles file.The same content would not compile very first time.
-Sathi