| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL question.
In article <7gahpf$6ev$1_at_nnrp1.dejanews.com>, odessa7_at_my-dejanews.com wrote:
>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.
L.B,
Oh, I see....
The "cursor" that DBMS_SQL uses in its calls cannot be mapped in any way to a Java-side ResultSet. If you have a Oracle stored program that you are calling from Java that uses DBMS_SQL to perform a query, you must return the cursor ID (obtained from DBMS_SQL.Open_Cursor) to your Java program, which must then use it in subsequent DBMS_SQL.Fetch_Rows and DBMS_SQL.Column_Value (I'm not sure how well JDBC handles overloaded procedures like DBMS_SQL.Column_Value).
Generally, if this is something you're going to do a lot of in your Java app, I'd recommend writing a package to wrap the functionality of DBMS_SQL. I've done this with good results; my Java code is significantly less messy than doing all the individual DBMS_SQL calls, especially when dealing with query results.
G.
 |
 |