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();这一行。
解决方案 »
- mybatis求指教
- 三层架构和mvc的关系
- 11111111111111
- 有谁知道用JavaService.exe如何引入多个jar?
- hibernate 双向关联的问题
- 需要用struts+hibernate开发一个oa系统,请问其中的收文发文应该怎么写
- Struts2从表单获取请求参数问题
- List<Map<String,String>怎么封装到JSON对象中,然后再还原成List<Map<String,String>
- 问个STRUTS里form表单的问题
- 在java中如何显示pdf
- 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