JDBC CallableStatement 执行oracle 自动建表和插入数据问题

Question

1，存储过程如下，问题是jdbc通过CallableStatement 调用MHT_OFM01_PROC过程，创建表，索引，触发器都创建了，就是ins_sql插入数据没有执行，很是费解，
EXEC MHT_OFM01_PROC('MHT_OFM01','20100401','001','002','01','02','99999');这样再sqlplus下可以。实在没办法了，高手给帮忙看看吧。CREATE OR REPLACE PROCEDURE MHT_OFM01_PROC
(t_name in varchar2,d_time varchar2,
StationCode varchar2,InstrumentID varchar2,
SamplingRate varchar2,Channel varchar2,F_ComponentCode varchar2
)
Authid Current_User
IS
ins_sql varchar2(1000);
cre_sql varchar2(1000);
inx_sql varchar2(1000);
seq_sql varchar2(1000);
trg_sql varchar2(1000);exist int;BEGINins_sql := 'insert into ' || t_name || ' values(0,'
      || d_time || ',' || StationCode || ','
      || InstrumentID || ',' || SamplingRate || ','
      || Channel || ',' || F_ComponentCode || ')';cre_sql := 'create table ' || t_name ||
      '(id int not null primary key,d_time varchar2(20) not null,StationCode varchar2(20) not null,
      InstrumentID varchar2(20) not null,SamplingRate varchar2(20)  not null,
      Channel varchar2(20)  not null,F_ComponentCode varchar2(20)  not null)';inx_sql := 'create index INDEX_MHT_OFM01 on ' || t_name || '(id,d_time)' ;seq_sql := 'create sequence SEQ_MHT_OFM01_ID';trg_sql := 'create or replace trigger TRG_MHT_OFM01_ID before insert on ' || t_name ||
         ' for each row begin
    select SEQ_MHT_OFM01_ID.nextval into :new.id from dual;
      end;';select count(*) into exist from user_tables where table_name = t_name ;if ( exist > 0 ) then
      execute immediate ins_sql;
end if;
      execute immediate cre_sql;
      execute immediate inx_sql;
      execute immediate seq_sql;
      execute immediate trg_sql;
      execute immediate ins_sql;commit;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;END MHT_OFM01_PROC;
/

Answer 1

刚又仔细的试了好几遍，原来是
EXEC MHT_OFM01_PROC('MHT_OFM01','20100401','001','002','01','02','99999');
第三个参数001地方，不能输入3120IGEA4042，这是为什么呢，create table的时候
InstrumentID varchar2(20) not null了啊，我本以为改成50长度，可还是不行，现在只能传给过程数字的字符串才行；直接再oracle编辑器里插入数据没问题，insert 一下，也没问题。请高手解答一下：
insert into mht_ofm01 values (0,'20100201','21GA0','2321321GASDD44','01','32','99999');

Answer 2

调试下 ins_sql 的值，然后再运行下，看是否有错误提示

Answer 3

看来是java代码的问题了 要不贴下source吧 

Answer 4

ins_sql有问题改成这样试试：
ins_sql := 'insert into ' || t_name || ' values(0,:1,:2,:3,:4,:5,:6)';execute immediate ins_sql 
using d_time,StationCode,InstrumentID,SamplingRate,Channel,F_ComponentCode;

Answer 5

我看了一下，EXEC MHT_OFM01_PROC('MHT_OFM01','20100401','001','002','01','02','99999');这样执行的话，问题在这个插入语句，原因应该是变量没有加引号的缘故吧，请高手看看，我在程序里
biz.DataInDB(new String[]{"'20100325'", "'42005AG9'", "'42005AG9'", "02", "01", "0.00"});这么写就没问题，差个单引号
   ins_sql := 'insert into ' || t_name || ' values(0,'
  || d_time || ',' || StationCode || ','
  || InstrumentID || ',' || SamplingRate || ','
  || Channel || ',' || F_ComponentCode || ')';

Answer 6

create or replace
PROCEDURE MHT_OFM01_PROC
(t_name in varchar2,d_time varchar2,
StationCode varchar2,InstrumentID varchar2,
SamplingRate varchar2,Channel varchar2,F_ComponentCode varchar2
)
Authid Current_User
IS
ins_sql varchar2(1000);
cre_sql varchar2(1000);
inx_sql varchar2(1000);
seq_sql varchar2(1000);
trg_sql varchar2(1000);exist int;BEGINcre_sql := 'create table ' || t_name ||
      '(id int not null primary key,d_time nvarchar2(50) not null,StationCode nvarchar2(50) not null,
      InstrumentID nvarchar2(50) not null,SamplingRate nvarchar2(50)  not null,
      Channel nvarchar2(50)  not null,F_ComponentCode nvarchar2(50)  not null)';inx_sql := 'create index INDEX_MHT_OFM01 on ' || t_name || '(id,d_time)' ;seq_sql := 'create sequence SEQ_MHT_OFM01_ID';trg_sql := 'create or replace trigger TRG_MHT_OFM01_ID before insert on ' || t_name ||
       ' for each row begin
select SEQ_MHT_OFM01_ID.nextval into :new.id from dual;
   end;';ins_sql := 'insert into ' || t_name || ' values(0,' || '''' || d_time || '''' || ',' || '''' || StationCode || '''' || ','
|| '''' || InstrumentID  || '''' || ',' || '''' || SamplingRate || '''' || ','
|| '''' || Channel || '''' || ',' || '''' || F_ComponentCode ||  '''' || ')';select count(*) into exist from user_tables where table_name = t_name ;if ( exist > 0 ) then
      execute immediate ins_sql;
end if;
      execute immediate cre_sql;
      execute immediate inx_sql;
      execute immediate seq_sql;
      execute immediate trg_sql;
      execute immediate ins_sql;commit;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;END MHT_OFM01_PROC;

Answer 7

虽然看到这个回答，不过就从这点我个人也简单说下，我不清楚这个是用来做工具还是应用程序，如果是只是创建工具这个我无话可说，不过如果是应用程序代码中要去动态创建东西，这个有点点吓人，至少对于ORACLE数据来说，细节不多说了。
不过上面的SQL建议使用预编译方式，拼串的SQL在ORACLE内部会很多意想不到的事情，而且还不知道是这里出的问题，尤其在OLTP系统中。如果如上述列的个数是固定的，在过程中，使用EXECUTE IMMEDIATE XXX_SQL USING 参数列表即可，但是参数不固定这样执行往往就不行了。我先说怎么弄，再回到这个话题上来。
如果是外部应用程序调用，如JAVA，因为列的个数的动态获取的，如通过界面的，在界面上就将列名称、数据类型按照一定规则拼串进去，在内部解析为TABLE数组即可循环生成创建语句，当然有些特殊需求可以传入自定义类型，如可以传入LIST，单从我我角度一直不是很相信他的类型转换和性能运行情况；如果是存储过程内部调用就可以直接传TABLE类型了，这个就不用多说了。然后回到上述过程，因为我是觉得你的创建过程和插入语句没有必要封装到一个事务内部的，因为创建表的语句是DDL操作，他不需要COMMIT提交就自动创建了，而INSERT语句是独立的所以这个创建语句可以继续放在你的过程中，而插入语句是可以放在JAVA应用程序中的，因为应用程序中是可以动态获取参数的个数的，可以动态拼接参数个数，使用预编译写入。进一步，这样的表创建后，若SQL需要反复使用，需将其配置化，因为表是动态创建的，每次去拼SQL往往是浪费时间，第一次生成这个SQL后，应该将其存储到某个地方（这个自己根据框架去选择），第二次就直接使用，其实我上述说的预编译就是对于ORACLE的共享池管理征用问题，而ORACLE共享池管理就是同样的SQL就只在共享池中分配一个空间，第一次使用后，若没有被替换掉，第二次直接使用，而不需要通过一大堆HARD PARSE过程和共享资源征用问题。

Answer 8

本QQ群新建
  希望能和大家一起探讨oracle各方面的问提
    QQ群号：54775466
    QQ群号：54775466
   期待你的加人
            积极讨论者 爱好者进
                      本群欢迎您的到来。