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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
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>

Re: ORA-24338: statement handle not executed [message #317391 is a reply to message #317350] Wed, 30 April 2008 23:41 Go to previous message
nimmala
Messages: 2
Registered: April 2008
Junior Member
Thanks, Its working now.
Previous Topic: Create Oracle Text Index from Java via JDBC
Next Topic: Error while running OA Framework page in Jdeveloper
Goto Forum:
  


Current Time: Sat Apr 19 12:41:32 CDT 2014

Total time taken to generate the page: 0.05910 seconds