Your PLSQL problem is that you are not declaring
the parameter to the procedure.
Here is a function definition here it is returning a CURSOR, but it
could be any oracle variable:
CREATE OR REPLACE FUNCTION select_by_lname(p_lname IN varchar2) RETURN
dbi_types.contact_cursor IS
ret_cursor dbi_types.contact_cursor;
v_lname dbinter_contact_list.lname%TYPE;
BEGIN
v_lname:=UPPER(p_lname);
OPEN ret_cursor FOR SELECT fname, lname, email, phone FROM dbinter_contact_list
WHERE UPPER(lname) LIKE UPPER('%' || p_lname || '%');
RETURN ret_cursor;
END select_by_lname;
/
Here is the java code to call it and get the return value:
//final int cursorReftype = OracleTypes.CURSOR; // should be
final int cursorReftype = -10; // bad
//The function call statement
CallableStatement stmt = con.prepareCall(
"{ ? = call select_by_lname(?)}");
//The type of return value from the function call, a cursor.
stmt.registerOutParameter(1, cursorReftype);//Types.OTHER);
//Bind the in parameter to the function call.
stmt.setString(2, sName);
ResultSet rs = null;
//Execute the statement.
stmt.execute();
//Get the ResultSet for the cursor.
rs = (ResultSet) stmt.getObject(1);
//Fetch the next row from the SELECT statement into the result set.
while(rs.next())
{
String fname = rs.getString("fname");
String lname = rs.getString("lname");
String email = rs.getString("email");
String phone = rs.getString("phone");
//Write each row of information into columns in each row of the html table.
System.out.print("<TR>");
System.out.print("<TD>" + fname + "</TD>");
System.out.print("<TD>" + lname + "</TD>");
System.out.print("<TD>" + email + "</TD>");
System.out.print("<TD>" + phone + "</TD>");
System.out.println("</TR>");
}
//Free up the statement resources.
stmt.close();
//Finished with result set.
if (rs != null) rs.close();
[EMAIL PROTECTED] wrote:
> Hi Joel,
>
> Good Morning. I am Edla B. Reddy working for CDS Intentaional Ltd, in
> Bangalore, India.
>
> I goyt u id from EJB forum. I am facing some problems while calling the
> Oracle Procedures, its giving access voilation error, or some different at
> different times.
>
> Please, go throgh the following code, and rectify it reply.
> ---------------------------------------------------------------------------
> ---------------------
> Properties props = new Properties();
> props.put("user","itv");
> props.put("password","itv");
> props.put("server","CDS008");
>
> try {
> Driver myDriver =
> (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
> Connection conn=myDriver.connect("jdbc:odbc:CDS008",props);
> // Create a stored proc
> Statement stmt1 = conn.createStatement();
> String proc1=("CREATE OR REPLACE PROCEDURE squareInt " +
> " IS "+
> " field1 INTEGER ;" +
> " BEGIN "+
> " field1 := 5 * 5; " +
> " END squareInt;");
> stmt1.execute(proc1);
> System.out.println("0");
> CallableStatement cs=conn.prepareCall("{call squareInt (?)}");
> System.out.println("1");
> cs.registerOutParameter(1,Types.INTEGER);
> System.out.println("2");
> cs.executeUpdate();
> System.out.println("3");
> System.out.println("the square value is : "+cs.getString(1));
> conn.commit();
> cs.close();
>
> conn.close();
> }catch(ClassNotFoundException ce){System.out.println(ce);}
> catch(SQLException se){System.out.println(se);}
>
> when i run i am getting the following problem.
>
> Connection success........ 0 #
> # An EXCEPTION_ACCESS_VIOLATION exception has been detected in native code
> outside the VM.
> # Program counter=0x1f78392a
> #
> Process Exit...
>
> One more thing in u reply to ejb forum, u executed the ananymous PL/SQL
> block of code instead of creating a proc and calling it, please can u
> convert the same to proc and send the same code.
>
> I hope you will solve my problem.
>
> Thanks & Regards,
> Edla B. Reddy
--
--------------------------------------
RHINO Systems Inc.
RDBMS and Internet development
Java/EJB/Oracle systems
www.rhinosystemsinc.com
Phone: 530-888-6248 x205
EFAX#: (425)969-0745
--------------------------------------
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
