Re: Dynamic SQL question.

Thank you for your answer. The problem is that we have to use dbms_sql to prepare dynamic statement within Oracle in stead of in java calling environment. So if you know how to do this by returning results from dbms_sql.execute_and_fetch that will be great.

L.B.

In article <7gacrn$8ig$1_at_news.doit.wisc.edu>,   e-mail.address_at_my.sig (Gerard M. Averill) wrote:
> In article <7g8i0l$e82$1_at_nnrp1.dejanews.com>, odessa7_at_my-dejanews.com wrote:
> >Greetings!
> >
> >I need to create a dynamic select statement and return a result set back to
> >Java calling env.
> >
> >Suppose, I built my dynamic select as 'SELECT fname, lname FROM emp where ssn
> >= :x'
> >
> >What do I do next? I heard I need to use DBMS_SQL package. How do I get a
> >result set back from DBMS_SQL I can return a cursor/or array back to java
> >caller? Any code sample is appreciated.
> >
>
> L.B,
>
> You don't need to use DBMS_SQL to do what you want; this can be done simply
> using a prepared statement with a bind variable. Assuming you have a valid
> database connection object:
>
> try {
> PreparedStatement stmt = conn.prepareStatement("SELECT fname, lname
FROM
> emp where ssn = ?");
>
> try {
> stmt.setString(1, "123456789"); // Bind SSN value (assumed string)
> ResultSet rs = stmt.executeQuery();
>
> while (rs.next()) {
> String firstName = rs.getString(1); // Get column 1 as string
> String lastName = rs.getString(2); // Get column 2 as string
>
> < do something with values >
> }
> }
> finally {
> stmt.close(); // Close statement (and result set)
> }
> }
> catch (SQLException e) {
> < error handling >
> }
>
> Note the use of a ? instead of the Oracle-like syntax for bind variables.
> Prepared statements with bind variables can be reused; that is, after
> preparing it once, new SSNs can be bound ( stmt.setString ) and the query
> re-executed ( stmt.executeQuery ) repeatedly.
>
> Hope this helps.
> Gerard
>
> ----
> Gerard M. Averill, Researcher
> CHSRA, University of Wisconsin - Madison
> GAverill<at>chsra<dot>wisc<dot>edu
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 29 1999 - 16:09:07 CDT