Oracle+mybatis 分号问题 Cause: java.sql.SQLException: ORA-00911: 无效字符

[吸尘器]

mybatis 执行一个oracle建表操作

<select id="createTable" parameterType="java.util.Map">
		<foreach collection="tableMap" index="key" item="value">
			create table "${key}"(
			<foreach collection="value" item="fields" separator=",">
				<include refid="commonSql"></include>
			</foreach>
			)
			<foreach collection="value" item="fields" separator=",">
				<if test="fields.fieldIsAutoIncrement">
					;CREATE SEQUENCE  "${key}_${fields.fieldName}_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE
				</if>
				<if test="fields.fieldIsKey">
					;ALTER TABLE ${key} ADD CONSTRAINT PK_${key}_${fields.fieldName} PRIMARY KEY ("${fields.fieldName}")
				</if>
				<if test="fields.fieldIsUnique">
					;CREATE UNIQUE INDEX IX_${key}_${fields.fieldName} ON ${key}("${fields.fieldName}")
				</if>
			</foreach>
		</foreach>
	</select>

建表的时候要同时建序列，主键，唯一键。
多个操作用分号分隔执行，会报错
nested exception is java.sql.SQLException: ORA-00911: 无效字符

去掉分号的话，又报错
Caused by: java.sql.SQLException: ORA-00922: 选项缺失或无效

请问各位大侠有没有办法解决，用什么可以替代分号，或者一句sql可以完成表，主键，序列，唯一键创建

[nayi_224]

记得就是不能写分号。可以试试begin或者直接写存储过程。

[吸尘器]

引用 1 楼 nayi_224 的回复:

记得就是不能写分号。可以试试begin或者直接写存储过程。

试过，begin不行，会报错

PLS-00103: 出现符号 "CREATE"在需要下列之一时：
( begin case declare
   exit for goto if loop mod null pragma raise return select
   update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   continue close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe purge

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-0655

而且就oracle不能用分号，MySQL和sqlserver都可以用

[吸尘器]

问题解决了，可以改个写法

<select id="createTable" parameterType="java.util.Map">
		<foreach collection="tableMap" index="key" item="value">
			begin
			execute immediate
			'create table "${key}"(
			<foreach collection="value" item="fields" separator=",">
				<include refid="commonSql"></include>
			</foreach>
			)';
			<foreach collection="value" item="fields" separator=",">
				<if test="fields.fieldIsAutoIncrement">
					execute immediate
					'CREATE SEQUENCE  "${key}_${fields.fieldName}_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE';
				</if>
				<if test="fields.fieldIsKey">
					execute immediate
					'ALTER TABLE ${key} ADD CONSTRAINT PK_${key}_${fields.fieldName} PRIMARY KEY ("${fields.fieldName}")';
				</if>
				<if test="fields.fieldIsUnique">
					execute immediate
					'CREATE UNIQUE INDEX IX_${key}_${fields.fieldName} ON ${key}("${fields.fieldName}")';
				</if>
			</foreach>
			end;
		</foreach>
	</select>