Home » Developer & Programmer » JDeveloper, Java & XML » ORA-24338: statement handle not executed (oracle)
| ORA-24338: statement handle not executed [message #317324] |
Wed, 30 April 2008 08:40  |
nimmala
Messages: 2
Registered: April 2008
|
Junior Member |
|
|
I have written following java stored procedure as follows:
public class SecuredPropertyStoredProc {
private final static String querySecuredProperty =
"select value,version from PF_ENCRYPTED_PROP where context = ? and key = ?";
public static ResultSet getSecuredProperty_1(String context,
String propertyName) {
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = dataSource.getConnection();
((OracleConnection)conn).setCreateStatementAsRefCursor(true);
pstmt = conn.prepareStatement(querySecuredProperty);
pstmt.setString(1, context);
pstmt.setString(2, propertyName);
rs = (ResultSet) pstmt.executeQuery();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
rs.close();
pstmt.close();
if (conn != null) {
conn.close();
}
}
catch (Exception e) {
e.printStackTrace();
}
}
return rs;
}
}
Used loadjav to load java into DB.
>loadjava -t -u mnimmala_pf/mnimmala_pf@1
0.131.210.63:1521:pf -v -r SecuredPropertyStoredProc.java
Then create the call specification for the Java Stored Procedure
create or replace package sec_prop AS
type securedPropertyEntryCurType is REF CURSOR;
function getSecuredProperty_1(context IN VARCHAR2, propName IN VARCHAR2) return securedPropertyEntryCurType;
function setSecuredProperty_1(context IN VARCHAR2,propName IN VARCHAR2,propValue IN VARCHAR2) return NUMBER;
END sec_prop;
CREATE OR REPLACE PACKAGE BODY sec_prop AS
function getSecuredProperty_1(context IN VARCHAR2, propName IN VARCHAR2) return securedPropertyEntryCurType
as language java name
'com.convergys.security.data.storedproc.SecuredPropertyStoredProc.getSecuredProperty_1(java.lang.String,java.lang.String)
return java.sql.ResultSet';
function setSecuredProperty_1(context IN VARCHAR2,propName IN VARCHAR2,propValue IN VARCHAR2) return NUMBER
as language java name
'com.convergys.security.data.storedproc.SecuredPropertyStoredProc.setSecuredProperty_1(java.lang.String,java.lang.String,java.lang.St ring)
return int';
END sec_prop;
I tried to executed as follows :
SQL>variable y refcursor
SQL>execute :y := sec_prop.getSecuredProperty_1('ctxt1','key1');
PL/SQL procedure successfully completed.
SQL>print y
When I try to print the y I am getting following error.
ERROR:
ORA-24338: statement handle not executed
Can you please let me know what is fix for it. I dont know how to open the cursor in java stored procedure.
[Updated on: Wed, 30 April 2008 08:46] Report message to a moderator |
|
|
|
| Re: ORA-24338: statement handle not executed [message #317350 is a reply to message #317324] |
Wed, 30 April 2008 15:53  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11g> CREATE TABLE pf_encrypted_prop
2 (value VARCHAR2 (10),
3 version VARCHAR2 (10),
4 context VARCHAR2 (10),
5 key VARCHAR2 (10))
6 /
Table created.
SCOTT@orcl_11g> INSERT INTO pf_encrypted_prop VALUES ('Value1', 'Version1', 'Context1', 'Key1')
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO pf_encrypted_prop VALUES ('Value2', 'Version2', 'Context1', 'Key1')
2 /
1 row created.
SCOTT@orcl_11g> COMMIT
2 /
Commit complete.
SCOTT@orcl_11g> create or replace and compile java source named "SecuredPropertyStoredProc" as
2 import java.sql.DriverManager;
3 import java.sql.Connection;
4 import java.sql.ResultSet;
5 import java.sql.PreparedStatement;
6 import oracle.jdbc.driver.OracleDriver;
7 import oracle.jdbc.driver.OracleConnection;
8 public class SecuredPropertyStoredProc {
9 private final static String querySecuredProperty = "select value,version from PF_ENCRYPTED_PROP where context = ? and key = ?";
10 public static ResultSet getSecuredProperty_1(String context, String propertyName) {
11 ResultSet rs = null;
12 PreparedStatement pstmt = null;
13 Connection conn = null;
14 try {
15 DriverManager.registerDriver(new OracleDriver());
16 conn = new OracleDriver().defaultConnection();
17 ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
18 pstmt = conn.prepareStatement(querySecuredProperty);
19 pstmt.setString(1, context);
20 pstmt.setString(2, propertyName);
21 rs = (ResultSet)pstmt.executeQuery();
22 return rs;
23 } catch (Exception e) {
24 e.printStackTrace();
25 }
26 return null;
27 }
28 }
29 /
Java created.
SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE sec_prop
2 AS
3 TYPE securedPropetyEntryCurType IS REF CURSOR;
4 FUNCTION getSecuredProperty_1
5 (context IN VARCHAR2,
6 propName IN VARCHAR2)
7 RETURN sec_prop.securedPropetyEntryCurType;
8 --
9 FUNCTION setSecuredProperty_1
10 (context IN VARCHAR2,
11 propName IN VARCHAR2,
12 propValue IN VARCHAR2)
13 RETURN NUMBER;
14 END sec_prop;
15 /
Package created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY sec_prop
2 AS
3 FUNCTION getSecuredProperty_1
4 (context IN VARCHAR2,
5 propName IN VARCHAR2)
6 RETURN sec_prop.securedPropetyEntryCurType
7 IS
8 LANGUAGE JAVA NAME 'SecuredPropertyStoredProc.getSecuredProperty_1(java.lang.String,java.lang.String) return java.sql.ResultSet';
9 --
10 FUNCTION setSecuredProperty_1
11 (context IN VARCHAR2,
12 propName IN VARCHAR2,
13 propValue IN VARCHAR2)
14 RETURN NUMBER
15 IS
16 LANGUAGE JAVA NAME 'SecuredPropertyStoredProc.setSecuredProperty_1(java.lang.String,java.lang.String,java.lang.String) return int';
17 END sec_prop;
18 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE y REFCURSOR
SCOTT@orcl_11g> EXECUTE :y := sec_prop.getSecuredProperty_1 ('Context1', 'Key1');
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> PRINT y
VALUE VERSION
---------- ----------
Value1 Version1
Value2 Version2
SCOTT@orcl_11g>
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:22:43 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
