DBMS_SQL and JDBC

DBMS_SQL and JDBC

Post by GAHS » Wed, 31 Jul 2002 22:59:24



I have some Oracle functions that use DBMS_SQL for creating dynamic cursor.

This is an example:

function RESEARCH (
    p_table_name IN VARCHAR2,
    p_column_name IN VARCHAR2)
return INTEGER IS

    cursor INTEGER;
    v_query VARCHAR2(1000);

begin
    - Apertura cursore
    cursor := DBMS_SQL.OPEN_CURSOR;

    -- Inizializzazione della query string da passare al cursore
    v_query := 'select ' || p_column_name || ' from ' || p_table_name;

    -- Creazione dinamica cursore
    DBMS_SQL.PARSE(cursor, v_query, DBMS_SQL.NATIVE);

    -- Restituzione cursore
    return(cursor);

end RESEARCH;

Does anyone know of any way in JDBC to manage this cursor?

Thanks.
Antonio

 
 
 

DBMS_SQL and JDBC

Post by bhoga » Fri, 02 Aug 2002 11:33:01


The return value of OPEN_CURSOR function is applicable only within DBMS_SQL
package. Its true that it represents the cursor id, but this cursor is quite
different from regular cursors defined in PL/SQL, precompiler etc. So I
believe you have to process the cursor within pl/sql.
--
Kumar Bhogaraju

Quote:> I have some Oracle functions that use DBMS_SQL for creating dynamic
cursor.

> This is an example:

> function RESEARCH (
>     p_table_name IN VARCHAR2,
>     p_column_name IN VARCHAR2)
> return INTEGER IS

>     cursor INTEGER;
>     v_query VARCHAR2(1000);

> begin
>     - Apertura cursore
>     cursor := DBMS_SQL.OPEN_CURSOR;

>     -- Inizializzazione della query string da passare al cursore
>     v_query := 'select ' || p_column_name || ' from ' || p_table_name;

>     -- Creazione dinamica cursore
>     DBMS_SQL.PARSE(cursor, v_query, DBMS_SQL.NATIVE);

>     -- Restituzione cursore
>     return(cursor);

> end RESEARCH;

> Does anyone know of any way in JDBC to manage this cursor?

> Thanks.
> Antonio