oracle 全局临时表空间,强制Oracle Drop全局临时表

在我们的项目中,我创建了一些全局临时表,如下所示：

CREATE GLOBAL TEMPORARY TABLE v2dtemp (

id NUMBER,GOOD_TYPE_GROUP VARCHAR2(250 BYTE),GOOD_CODE VARCHAR2(50 BYTE),GOOD_TITLE VARCHAR2(250 BYTE)

)

ON COMMIT PRESERVE ROWS;

但问题出现的时候我想放弃这张桌子.

甲骨文不会让我放弃表,它说：

ORA-14452: attempt to create,alter or drop an index on temporary table already in use

我必须在某些过程中使用此表,但可能会根据其他报告进行更改.所以我应该总是丢弃表,然后我应该用我需要的字段重新创建它.

出于某些商业原因,我必须使用它,所以我不可能使用表格或其他东西.我只能使用临时表.

我尝试了提交删除行,但是当我调用我的过程来使用此表中的数据时,表中没有更多行,并且它们已被删除.

任何帮助将非常感谢,

提前致谢

///编辑

public void saveJSONBatchOpenJobs(final JSONArray array,MtdReport report) {

dropAndCreateTable();

String sql = "INSERT INTO v2d_temp " +

"(ID,KARPARDAZ,GOOD_TYPE_GROUP,GOOD_CODE,GOOD_TITLE,COUNT,"

+ "FACTOR_COUNT,GHABZ_COUNT,DEAL_NO,DEAL_DATE,REQUEST_NO,REQUEST_DATE,"

+ "REQUEST_CLIENT,STATUS,TYPE,MTDREPORT_ID,GEN_SECURITY_DATA_ID) " +

"VALUES (MTD_KARPARDAZ_OPEN_JOBS_SEQ.nextval,?,?)";

getJdbcTemplate().batchUpdate(sql,new BatchPreparedStatementSetter() {

@Override

public void setValues(PreparedStatement ps,int i) throws sqlException {

JSONArray values = array.getJSONArray(i);

if(!values.get(0).equals("null"))

ps.setString(1,values.get(0).toString());

else

ps.setNull(1,Types.VARCHAR);

if(!values.get(1).equals("null"))

ps.setString(2,values.get(1).toString());

else

ps.setNull(2,Types.VARCHAR);

if(!values.get(2).equals("null"))

ps.setString(3,values.get(2).toString());

else

ps.setNull(3,Types.VARCHAR);

if(!values.get(3).equals("null"))

ps.setString(4,values.get(3).toString());

else

ps.setNull(4,Types.VARCHAR);

if(!values.get(4).equals("null"))

ps.setBigDecimal(5,new BigDecimal(values.get(4).toString()));

else

ps.setNull(5,Types.NUMERIC);

if(!values.get(5).equals("null"))

ps.setBigDecimal(6,new BigDecimal(values.get(5).toString()));

else

ps.setNull(6,Types.NUMERIC);

if(!values.get(6).equals("null"))

ps.setBigDecimal(7,new BigDecimal(values.get(6).toString()));

else

ps.setNull(7,Types.NUMERIC);

if(!values.get(7).equals("null"))

ps.setString(8,values.get(7).toString());

else

ps.setNull(8,Types.VARCHAR);

if(!values.get(8).equals("null"))

ps.setDate(9,new Date(new Timestamp(values.getLong(8)).getDateTime()));

else

ps.setNull(9,Types.DATE);

if(!values.get(9).equals("null"))

ps.setString(10,values.get(9).toString());

else

ps.setNull(10,Types.VARCHAR);

if(!values.get(10).equals("null"))

ps.setDate(11,new Date(new Timestamp(values.getLong(8)).getDateTime()));

else

ps.setNull(11,Types.DATE);

if(!values.get(11).equals("null"))

ps.setString(12,values.get(11).toString());

else

ps.setNull(12,Types.VARCHAR);

if(!values.get(12).equals("null"))

ps.setString(13,values.get(12).toString());

else

ps.setNull(13,Types.VARCHAR);

if(!values.get(13).equals("null"))

ps.setString(14,values.get(13).toString());

else

ps.setNull(14,Types.VARCHAR);

if(!values.get(14).equals("null"))

ps.setLong(15,new Long(values.get(14).toString()));

else

ps.setNull(15,Types.NUMERIC);

if(!values.get(15).equals("null"))

ps.setLong(16,new Long(values.get(15).toString()));

else

ps.setNull(16,Types.NUMERIC);

}

@Override

public int getBatchSize() {

return array.size();

}

});

String bulkInsert = "declare "

+ "type array is table of d2v_temp%rowtype;"

+ "t1 array;"

+ "begin "

+ "select * bulk collect into t1 from d2v_temp;"

+ "forall i in t1.first..t1.last "

+ "insert into vertical_design values t1(i);"

+ "end;";

executesql(bulkInsert);

}

private void dropAndCreateTable() {

String dropsql = "declare c int;"

+ "begin "

+ "select count(*) into c from user_tables where table_name = upper('v2d_temp');"

+ "if c = 1 then "

+ "truncate table v2d_temp"

+ "drop table v2d_temp;"

+ " end if;"

+ "end;";

executesql(dropsql);

String createsql = "CREATE GLOBAL TEMPORARY TABLE v2d_temp (\n"

+ "DEAL_ID NUMBER,\n"

+ "id NUMBER,\n"

+ "karpardaz VARCHAR2(350),\n"

+ "GOOD_TYPE_GROUP VARCHAR2(250 BYTE),\n"

+ "GOOD_CODE VARCHAR2(50 BYTE),\n"

+ "GOOD_TITLE VARCHAR2(250 BYTE),\n"

+ "COUNT NUMBER,\n"

+ "FACTOR_COUNT NUMBER,\n"

+ "GHABZ_COUNT NUMBER,\n"

+ "DEAL_NO VARCHAR2(50 BYTE),\n"

+ "DEAL_DATE DATE,\n"

+ "REQUEST_NO VARCHAR2(50 BYTE),\n"

+ "REQUEST_DATE DATE,\n"

+ "REQUEST_CLIENT VARCHAR2(250 BYTE),\n"

+ "STATUS VARCHAR2(250 BYTE),\n"

+ "TYPE VARCHAR2(250 BYTE),\n"

+ "GEN_SECURITY_DATA_ID NUMBER(10),\n"

+ "MTDREPORT_ID NUMBER\n"

+ ")\n"

+ "ON COMMIT PRESERVE ROWS";

executesql(createsql);

}

private void executesql(String sql) {

Connection con = null;

try {

con = getConnection();

Statement st = con.createStatement();

st.execute(sql);

} catch (sqlException e) {

e.printStackTrace();

} finally {

if(con != null) {

try {

con.close();

} catch (sqlException e) {

e.printStackTrace();

}

}

}

}