java.sql.SQLException: ORA-28112: failed to execute policy function代码 String sql = "select region from rec_servnumber_region t where ? between beginnum and endnum";
ResultSet rs = null;
String region = "";
ps = conn.prepareStatement(sql);
ps.setString(1, SERVNUMBER); rs = ps.executeQuery();错误指在rs = ps.executeQuery();这一行。
ResultSet rs = null;
String region = "";
ps = conn.prepareStatement(sql);
ps.setString(1, SERVNUMBER); rs = ps.executeQuery();错误指在rs = ps.executeQuery();这一行。
解决方案 »
- GlassFish等应用服务器上的web应用怎么从外网访问?
- 很简单的问题
- Struts2 <s:property> 问题
- ant1.7不支持jdk1.6?!
- 有哪位了解"北京东方国信电子有限公司"的吗?
- 各位高手,webservice返回的xml soap包可以用什么方法解析?
- 征集优秀译者:翻译O'reilly好书《Enterprise JavaBeans 3.0,5E》!
- 中文问题怎么解决~~~
- 开发时什么时候确定前端页面?
- 大家进来讨论以下我转载了一篇文章-IT人为什么薪水少!
- java开发一个网站,要有站内搜索功能,请问前辈们这个怎么做啊
- 有谁看过 JPetStore 的ibatis 6.0版本和 Spring 的实现没
复制到数据库中查查看看有没有问题!
oracle数据库有个类型varchar2 注意 这个2是不能去掉的 如果去掉就会报这个failed to execute policy function
PreparedStatement ps = null;
String sql = "select region from rec_servnumber_region t where ? between beginnum and endnum";
ResultSet rs = null;
String region = "";
ps = conn.prepareStatement(sql);
ps.setString(1, SERVNUMBER); rs = ps.executeQuery();
while (rs.next()) {
region = rs.getString("region");
}
if (region == null || region.equals("")) {
region = "";
throw new SQLException("手机号码"+SERVNUMBER+"在表rec_servnumber_region中没有对应的region信息");
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
return region;
}
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at com.huawei.guo.services.ExcelIntoDbBean.getRegionFormServNum(ExcelIntoDbBean.java:151)
at com.huawei.guo.services.ExcelIntoDbService.insertCheck_gift(ExcelIntoDbService.java:189)
at com.huawei.guo.services.ExcelIntoDbService.insertCheck(ExcelIntoDbService.java:106)
at com.huawei.guo.process.ExcelToDB.main(ExcelToDB.java:106)
请大家帮忙!
这种写法是没问题的
他说的非常对,你应该先在数据库命令行下面 输入 "select region from rec_servnumber_region t where 15922222222 between beginnum and endnum";(假设你找的是159222222这个号码)。。你看会不会报错,能不能得到结果。。总 觉得你的这句话 String sql = "select region from rec_servnumber_region t where ? between beginnum and endnum"; 怪怪的! 我估计是这一行出错了。
在使用 betweetn and 的时候,应该 这样吧: 列名 betweetn 参数1 and 参数2
你怎么这样弄、
建议你把你的数据库 rec_servnumber_region 贴出来大家看看 就知道你的错误在哪了。。
1.如果是传列名的话,直接拼sql吧 "select region from rec_servnumber_region t where " +SERVNUMBER+"……"
2.如果是传值,那么你这个sql写的就有问题了
3.如果beginnum 和 endnum 是列,该这么写:
String sql = "select region from rec_servnumber_region t where ? > beginnum and ?<endnum"
Connection conn = null;
PreparedStatement ps = null;
String sql = "select region from rec_servnumber_region t where ? between beginnum and endnum";
ResultSet rs = null;
String region = "";
try {
conn = DBConnect.getConnection1();
ps = conn.prepareStatement(sql);
String SERVNUMBER = "13561440001";
ps.setString(1, SERVNUMBER);
rs = ps.executeQuery();
while (rs.next()) {
region = rs.getString("region");
}
if (region == null || region.equals("")) {
region = "";
throw new SQLException("手机号码" + SERVNUMBER
+ "在表rec_servnumber_region中没有对应的region信息");
}
System.out.println("region = " + region); } catch (SQLException e) {
e.printStackTrace();
}
}}
Connection conn = null;
PreparedStatement ps = null;
String SERVNUMBER = "13561440001";
String sql = "select region from rec_servnumber_region t where '"+SERVNUMBER+"'> beginnum and '"+SERVNUMBER+"'< endnum";
ResultSet rs = null;
String region = "";
try {
conn = DBConnect.getConnection1();
ps = conn.prepareStatement(sql);
//ps.setString(1, SERVNUMBER);
rs = ps.executeQuery();
while (rs.next()) {
region = rs.getString("region");
}
if (region == null || region.equals("")) {
region = "";
throw new SQLException("手机号码" + SERVNUMBER
+ "在表rec_servnumber_region中没有对应的region信息");
}
System.out.println("region = " + region); } catch (SQLException e) {
e.printStackTrace();
}
}
}这个也行,都是一样的。不知道为什么测试的就给我说报错呢。
/**
*
*/
package com.jonathan.jdbc;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/**
* @author Jonathan Shengwq
*
*/
public class JDBCDemo { /**
*
*/
public JDBCDemo() {
} public static void testORA28112OnOracle9i() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
// orcl为数据库的SID
String url = "jdbc:oracle:thin:@192.168.1.26:1521:orcl";
String user = "trackao_nbyz_0518";
String password = "trackao_nbyz_0518";
String resultIdString = "";
Connection connection = null;
PreparedStatement stmt = null;
ResultSet rs = null;
connection = DriverManager.getConnection(url, user, password);
String sql = "select * from SINGLE_DATA t where t.operation_id = ? and ? between t.value1 and t.value2"; stmt = connection.prepareStatement(sql);
stmt.setString(1, "CS110009855");
stmt.setString(2, "62");
rs = stmt.executeQuery();
while (rs.next()) {
resultIdString = rs.getString("id");
System.out.println(resultIdString);
}
stmt.close();
connection.close();
System.out.println("Successed!!!");
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param args
*/
public static void main(String[] args) {
testORA28112OnOracle9i();
}}
1 String sql = "select region from rec_servnumber_region t where ? between beginnum 2 and endnum";
3 ResultSet rs = null;
4 String region = "";
5 ps = conn.prepareStatement(sql);
6 ps.setString(1, SERVNUMBER);7 rs = ps.executeQuery();错误肯定是在第7行,你想得到的sql语句是这样:
select region from rec_servnumber_region t where SERVNUMBER between beginnum 2 and endnum
然而你通过第6行:ps.setString(1, SERVNUMBER);这种方式得到的sql语句是这样的:select region from rec_servnumber_region t where "SERVNUMBER" between beginnum 2 and endnum
所以,当你执行ps.executeQuery()的时候就有问题了,问题是找不到"SERVNUMBER"这个字段。
解决方案:动态改变表名或字段名时,采用拼接字符串的形式。
具体代码如下:
String sql = "select region from rec_servnumber_region t where " + 字段变量名 + " between beginnum 2 and endnum";
ResultSet rs = null;
String region = "";
ps = conn.prepareStatement(sql);rs = ps.executeQuery();
提示function没有授权...
?oracle不知道是什么类型...
String sql = "select * from SINGLE_DATA t where t.operation_id = ? and ? between t.value1 and t.value2";简化成
String sql = "select * from SINGLE_DATA t where t.operation_id = ?";
后面的相关代码也跟着改变或注销下有可能是你的jdbc驱动部分,不支持between这个sql 关键词
setString or setInt自己考虑吧。使用ps.setInt(1, SERVICENUMBER)
ResultSet rs = null;
String region = null;
ps = conn.prepareStatement(sql);
ps.setString(1, SERVNUMBER); // setString or setInt
ps.setString(2, SERVNUMBER);rs = ps.executeQuery();
可以用把sql语句的字符串分段试试。
同时注意between语法。
只有这样select region from rec_servnumber_region t where “+SERVNUMBER+” between beginnum and endnum"
第二点确保 SERVNUMBER传进来的实际参数在 rec_servnumber_region中有值
ps.setString(index, value); 好好理解一下 是设置 第一个参数对应的值是value 连参数都没有如何设置
相当于 key-value形式 不要做 无用功 直接这样就搞定
select region from rec_servnumber_region t where “+SERVNUMBER+” between beginnum and endnum"
好好看下 API