java调用oracle存储过程动态生成表

1.给用户授予权限

即便是用户有了connect,resource权限了你还是要给他授予权限的，否则在java调用时候会提示你权限不够。

grant create any table to userxxx;

2.创建存储过程

CREATE OR REPLACE PROCEDURE createAnyTable(tableName_in IN VARCHAR2)
AS
  SQL_in VARCHAR2(5000);
BEGIN
  SQL_in := 'CREATE TABLE ' || tableName_in || '
   (
 id    number NOT NULL,
 name  VARCHAR2(60) NOT NULL,
 age  number
 )tablespace   USERS
   pctfree   10
   initrans   1
   maxtrans   255
   storage
   (
     initial   1M
     minextents   1
     maxextents   unlimited
    )';
  EXECUTE IMMEDIATE SQL_in;
  SQL_in := 'alter table ' || tableName_in || ' add constraint PK_' ||
            tableName_in || ' Primary key(id)';
  EXECUTE IMMEDIATE SQL_in;
END;

3.用java进行测试

package com.hdx.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class TestProcedure {
	public static void main(String[] args) throws Exception {
		Connection conn = getOracleConn();
		CallableStatement cs = conn.prepareCall("{call up_CreateTable(?)}");
		cs.setString(1, "tableTest");
		boolean r = cs.execute();
		System.out.println(r);
	}

	public static Connection getOracleConn() throws Exception {

		Connection conn = null;
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test1", "hdx");
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

}