title: Force Oracle Drop Global Temp Table
body: <p>In our project I create some global temp table that will be like these:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>but the problem comes when I want to drop this table.
  Oracle will not let me to drop the table, and it says:</p>
  
  <pre><code>ORA-14452: attempt to create, alter or drop an index on temporary table already in use
  </code></pre>
  
  <p>I have to use this table in some procedure but it may be changed dependent to other reports. So I should always drop the table then I should recreate it with my needed fields.</p>
  
  <p>I have to use this for some business reasons so it is not possible for me to use tables, or other things. I can use just temp tables.
  I tried on commit delete rows, but when I call my procedure to use the data in this table there are no more rows in the table and they have been deleted.</p>
  
  <p>Any helps will greatly appreciated,
  thanks in advance</p>
  
  <p>/// EDIT</p>
  
  <pre><code>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();
              }
          }
      }
  }
  </code></pre>