为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:1.7
【操作系统】:linux
【CPU】:
【问题描述】:我写了一个建表的存储过程(因为需要多个语句加注释和索引),并且在存储过程中提交了,在DM管理工具上可以正常执行。但是用jdbcTemplate执行不报错,但是也不生效。执行一个普通的建表语句会生效。请问这是为什么?如何排查原因?
存储过程代码:CREATE OR REPLACE PROCEDURE iris_log_backups.create_backlog_table(table_name varchar)
AS
v_flag number(10,0);
v_sqlflag varchar(200);
BEGIN
v_flag:=0;
v_sqlflag:='select count() from user_tables where table_name=‘’‘||table_name||’‘’‘;
EXECUTE IMMEDIATE v_sqlflag INTO v_flag;
IF v_flag=0 THEN
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE “iris_log_backups”."’||table_name||’“(
“id” BIGINT IDENTITY(8, 1) NOT NULL,
“log_id” BIGINT NOT NULL,
“account_id” BIGINT NOT NULL,
“user_id” BIGINT NOT NULL,
“user_name” VARCHAR(128) NOT NULL,
“category” TINYINT NOT NULL,
“action” INT NOT NULL,
“entity_str1” VARCHAR(1000),
“entity_str2” VARCHAR(1000),
“entity_str3” VARCHAR(1000),
“reserve_field” VARCHAR(255),
“path_type” TINYINT,
“ctime” TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP() NOT NULL,
“ip” VARCHAR(32),
“location” VARCHAR(128),
“device” INT,
“neid” BIGINT,
“nsid” INT,
“dynamic_type” INT DEFAULT 99 NOT NULL,
NOT CLUSTER PRIMARY KEY(“id”),
CHECK(“account_id” >= 0),
CHECK(“log_id” >= 0),
CHECK(“user_id” >= 0)) STORAGE(ON “MAIN”, CLUSTERBTR) ;';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN “iris_log_backups”.”‘||table_name||’“.“log_id” IS ‘||’‘‘日志表主键id’’‘||’;‘;
–省略若干行
EXECUTE IMMEDIATE ‘CREATE INDEX "’||table_name||’.category” ON “iris_log_backups”.“‘||table_name||’”(“category” ASC) STORAGE(ON “MAIN”, CLUSTERBTR) ;';
–省略若干行
commit;
END;
END IF;
END create_backlog_table;
jdbc代码:
String sb = “{call iris_log_backups.create_backlog_table('”+backupsLogTableNameByTable+“')}”;
jdbcTemplate.execute(sb);
v_sqlflag:='select count() from user_tables where table_name=‘’‘||table_name||’‘’‘;
这一句里面count() 改成count(*)