Stored Procedure in HSQLDB. Creation through SQLTool

Question

I am trying to write a stored procedure. Creating it through the SQL browser goes well but when I save it in SQL file and load it through SQLTool it fails

---

CREATE PROCEDURE SP_GETNEXTSEQUENCE(OUT out_new_seq_no DECIMAL, IN in_seq_id DECIMAL) 
MODIFIES SQL DATA
   BEGIN ATOMIC
     UPDATE SEQUENCE_GENERATOR SET seq_value=seq_value+1 where seq_id=in_seq_id;
     select seq_value into out_new_seq_no from SEQUENCE_GENERATOR where seq_id=seq_id;
   END;
   .;

---

I receive below error

Exception in thread "main" java.lang.Error: Error: could not match input
    at org.hsqldb.cmdline.sqltool.SqlFileScanner.zzScanError(Unknown Source)
    at org.hsqldb.cmdline.sqltool.SqlFileScanner.yylex(Unknown Source)

call made SqlTool.objectMain(sqlToolParams);

where

String [] sqlToolParams = {"--inlineRc", "URL="+"jdbc:hsqldb:mem:TestDB", SCHEMAPATH};

I need help with correcting the syntax.

I tried to get clues from http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html#sqltool_raw-sect

but seems like my understanding is not adequate enough

added Question -- Does SQL file need to be any specific encoding

Answer 1

Update on the Stored Proc

Correct definition of the Stored Proc goes like this

---

CREATE PROCEDURE SP_GETNEXTSEQUENCE(OUT pno_new_id INTEGER, IN pni_seq_id INTEGER)  
    MODIFIES SQL DATA
    BEGIN ATOMIC    
        UPDATE SEQUENCE_GENERATOR SET seq_value=seq_value+1 where seq_id=pni_seq_id;
        SET pno_new_id = (select seq_value from SEQUENCE_GENERATOR where seq_id=pni_seq_id);     
    END
 .;

---

After some digging and found Select statement had to be corrected. SQLTool does not accept a direct select

Before writing stored procs and functions please go through this http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_psm_statements