我测试一个简单的oracle插入数据问题,怎么总是在抱ORA-00947的错误啊!notice.sql脚本
create table notice
(
id int,
title varchar2(20),
content varchar2(20)
)测试程序如下:OracleTestConnect.javapackage demo;import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import demo.Notice;
public class OracleTestConnect
{
public static void main(String[] args)
{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String title = null;
String content = null;
String strSql = null;
int id;
try
{
//以下是连接数据库的操作
String drivername = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:ORAC";
String name = "scott";
String password = "tiger";
//加载驱动
Class.forName(drivername).newInstance();
//连接
conn = DriverManager.getConnection(url,name,password);
System.out.println("数据库连接成功!!!");
//以下是插入数据的操作
try
{
title = "标题";
content = "内容";
id = 1;
strSql =
"insert into notice(id,title,content) values('"+id+","+title+","+content+"')";
st = conn.createStatement();
st.executeUpdate(strSql);
System.out.println("插入成功:"+strSql);
}
catch(Exception e)
{
e.printStackTrace();
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}}抱错信息如下:java.sql.SQLException: ORA-00947: 没有足够的值 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:865)
at demo.OracleTestConnect.main(OracleTestConnect.java:48)
数据库连接成功!!!总说我没有插入足够的值,可是我的表里面只有这三个字段啊!所以我就不明白了,望各位大虾指点
还有就是怎么样让id列在程序里面自动增长,意思就是说:比如mysql,sqlserver数据库里面都可以在表的字段后面加一个自动增长的标识,而oracle数据库却是要创建序列才能够自动增长,可是我怎么样才能够在程序里面绑定序列让这个字段自动增长呢?
create table notice
(
id int,
title varchar2(20),
content varchar2(20)
)测试程序如下:OracleTestConnect.javapackage demo;import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import demo.Notice;
public class OracleTestConnect
{
public static void main(String[] args)
{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String title = null;
String content = null;
String strSql = null;
int id;
try
{
//以下是连接数据库的操作
String drivername = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:ORAC";
String name = "scott";
String password = "tiger";
//加载驱动
Class.forName(drivername).newInstance();
//连接
conn = DriverManager.getConnection(url,name,password);
System.out.println("数据库连接成功!!!");
//以下是插入数据的操作
try
{
title = "标题";
content = "内容";
id = 1;
strSql =
"insert into notice(id,title,content) values('"+id+","+title+","+content+"')";
st = conn.createStatement();
st.executeUpdate(strSql);
System.out.println("插入成功:"+strSql);
}
catch(Exception e)
{
e.printStackTrace();
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}}抱错信息如下:java.sql.SQLException: ORA-00947: 没有足够的值 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:865)
at demo.OracleTestConnect.main(OracleTestConnect.java:48)
数据库连接成功!!!总说我没有插入足够的值,可是我的表里面只有这三个字段啊!所以我就不明白了,望各位大虾指点
还有就是怎么样让id列在程序里面自动增长,意思就是说:比如mysql,sqlserver数据库里面都可以在表的字段后面加一个自动增长的标识,而oracle数据库却是要创建序列才能够自动增长,可是我怎么样才能够在程序里面绑定序列让这个字段自动增长呢?
strSql = "insert into notice(id,title,content)
values('"+ id + "','" + title + "','" + content + "')";少了四个单引号,你把id title content 三个值变成一个了,所以说没有足够的值啊
呵呵,要仔细哈
正确的语句如下:
strSql = "insert into notice(id,title,content) values('" + id + "','" + title + "','" + content + "')";
下面是建立一个sequence的语句,要在oracle内执行:
create sequence NOTICE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 16736
increment by 1
nocache;
然后sql语句这样写:
strSql = "insert into notice(id,title,content) values( NOTICE_SEQ.Nextval, ' " + title + " ', ' " + content + " ') ";