dcsimg
Urgent! Got a ORA-6550 when call a stored procedure in servlet.
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Alan_Hu
Posted On:   Sunday, October 21, 2001 05:32 AM

Here is the stored procedure and my code fragment: PL/SQL stored procedure: CREATE OR REPLACE function user_schd ( userId in integer, startDate in varchar2, endDate in varchar2 ) return integer as v_result integer := 0; begin select count(*) into v_result from schedule where ownerid=userId and not ( (endAt (startAt>v_currEndDate) ); return v_result; end; / The servlet code: package ecalendar; import javax.servlet.*; import javax.servlet.http.*; import java.io.*; import java.util.*; import oracle.sql.*; import java.sql.*; public class test extends   More>>

Here is the stored procedure and my code fragment:

PL/SQL stored procedure:

			
CREATE OR REPLACE function user_schd (
userId in integer, startDate in varchar2, endDate in varchar2 )
return integer as
v_result integer := 0;
begin
select count(*) into v_result from schedule where ownerid=userId and not
( (endAt
(startAt>v_currEndDate) );

return v_result;
end;
/


The servlet code:
			
package ecalendar;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import oracle.sql.*;
import java.sql.*;

public class test extends HttpServlet {
private static final String CONTENT_TYPE = "text/html";

private Connection con = null;
private String DBMS_USERID = "";
private String DBMS_PASSWORD = "";
private String DBMS_SERVICE = "";
private String JDBC_URL = "";

/**Initialize global variables*/
public void init() throws ServletException {
JDBC_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
DBMS_USERID = "xxxx";
DBMS_PASSWORD = "xxxx";
}

/**Process the HTTP Get request*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//establish a jdbc connection
try {
// Load driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Get Connection
con = DriverManager.getConnection (JDBC_URL, DBMS_USERID, DBMS_PASSWORD);
} catch(Exception e) {
System.err.println( "problems connecting to DBMS:" );
System.err.println( e.getMessage() );

if( con != null) {
try { con.close(); }
catch( Exception e2 ) {}
}
return;
}

//a personal schedule
int a = 0;
CallableStatement cstmt = null;
try {
cstmt = con.prepareCall("{? = call user_schd(? ? ?)}");
cstmt.setInt(2, 1);
cstmt.setString(3, "1003654800000");
cstmt.setString(4, "1003658400000");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.execute();
a = cstmt.getInt(1);
System.out.println("a="+a);
} catch(SQLException SQLe) { System.err.println(SQLe.getErrorCode()); }
finally {
try {
cstmt.close();
con.close();
}
catch( Exception e ) {}
}
//........
}

/**Clean up resources*/
public void destroy() {
}
}


But I got a ORA-6550 error, any tips are welcomed!    <<Less

Re: Urgent! Got a ORA-6550 when call a stored procedure in servlet.

Posted By:   Bernie_Acs  
Posted On:   Wednesday, November 7, 2001 01:06 PM

oerr ora 6550

06550, 00000, "line %s, column %s:
%s"
// *Cause: Usually a PL/SQL compilation error.
// *Action:



When the table "schedule" is modified the store code would become invalid and may require compliation which can be done with the follwoing:



Alter Function user_schd compile;




Make sure there are not multiple versions of the function stored in the database under different schemas for instance praticularly if the owner of the function differs from the executer id in the java code.


using SQL*PLUS or like

login as userid from Java Code

select user_schd (1,'1003654800000','1003658400000')

from dual;



Does this work?

If so it will work in your Java code, too.

If not then there must be more than one illiteration of the PL/SQL code in DB or the object it references are being modified causing the invalidation/ cannot compile circumstance.

About | Sitemap | Contact