Search code examples
sqlstored-procedureshsqldbddl

HSQLDB Create view inside stored procedure

The following code

CREATE PROCEDURE create_all_views()
LANGUAGE SQL
--MODIFIES SQL DATA
 BEGIN ATOMIC
  FOR select view_name from views_to_create DO
     CREATE VIEW view_name AS SELECT * FROM view_name || '_TAB';
   END FOR;
END;

produces syntax error

[Code: -5581, SQL State: 42581]  unexpected token: CREATE : line: 5

What is wrong here and is it possible to use DDL statements inside procedures at all?

Solution

  • The current version HSQLDB does not allow Data Definition Language statements in procedures.