You will have problems with the dbinter_contact_list.lname%TYPE;
declaration as well, since you don't have a dbinter_contact_list table
(not to mention the lname column) - you can simply declare
them as varchar2's to run what I've shown you.
Cheers,
Joel
"Joel A. Thompson" wrote:
> create or replace package dbi_types
> as
> type contact_cursor is ref cursor;
> end;
> /
>
> Good luck.
>
> -Joel
>
> [EMAIL PROTECTED] wrote:
>
> > Hi Joel,
> >
> > What is "dbi_types.contact_cursor" this is giving problem while creating a
> > function and samething can't we do it with Procedures.
> >
> > This is the error;
> >
> > LINE/COL ERROR
> > -------- -----------------------------------------------------------------
> > 0/0 PL/SQL: Compilation unit analysis terminated
> > 2/8 PLS-00201: identifier 'DBI_TYPES.CONTACT_CURSOR' must be declared
> >
> > Thanks & Regards,
> > Reddy
> >
> > 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
> > --------------------------------------
>
> --
> --------------------------------------
> RHINO Systems Inc.
> RDBMS and Internet development
> Java/EJB/Oracle systems
> www.rhinosystemsinc.com
> Phone: 530-888-6248 x205
> EFAX#: (425)969-0745
> --------------------------------------
--
--------------------------------------
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".
