Re: How to fetch object using dynamic SQL?

Hello Tomasz,

You cannot use DBMS_SQL.DEFINE_COLUMN(v_handle, 2, g) with the MDSYS.SDO_GEOMETRY data type. Only the following data types are allowed:

NUMBER
DATE
BLOB
CLOB CHARACTER SET ANY_CS
BFILE
UROWID You cannot substitute 'execute immediate' for dbms_sql either since you want to fetch your data in a loop. The solution is to use a cursor variable like this:

CREATE OR REPLACE FUNCTION GET_TUPLES (table_name VARCHAR2, column_name VARCHAR2) RETURN int IS
row_id ROWID ;
g MDSYS.SDO_GEOMETRY;
stmt VARCHAR2(100);

l_cursor sys_refcursor;

BEGIN
stmt := 'SELECT ROWID, ' || column_name || ' FROM ' || table_name; open l_cursor for stmt;

LOOP
  EXIT WHEN l_cursor%notfound;
  fetch l_cursor into row_id, g;
END LOOP;

In Oracle 8i, you'll need to the type for the cursor variable:

TYPE sys_refcursor IS REF CURSOR;

In 9i, it's already defined.

VC

"Tomasz Majchrzak" <tomekem_at_poczta.fm> wrote in message news:pan.2004.01.10.20.35.14.976085_at_poczta.fm...
> Hello,
>
> I want to fetch two columns using dynamic sql: one of them is rowid
> and the second is MDSYS.SDO_GEOMETRY. But DEFINE_COLUMN refuses to bind
> second argument with SDO_GEOMETRY type:
>
> DBMS_SQL.DEFINE_COLUMN(v_handle, 2, g);
>
> Error: PLS-00306 (wrong number of arguments or their types)
>
> I tried to specify variable size, but it doesn't help.
> Is it possible to use DEFINE_COLUMN function with types?
> COLUMN_VALUE doesn't work either. Is there other
> way to fetch such a column using dynamic SQL?
>
> Here is my code:
>
> CREATE OR REPLACE FUNCTION GET_TUPLES (table_name VARCHAR2, column_name
VARCHAR2) RETURN MY_NODES IS
> row_id ROWID ;
> g MDSYS.SDO_GEOMETRY;
> stmt VARCHAR2(100);
> v_handle INT;
>
> BEGIN
> stmt := 'SELECT ROWID, ' || column_name || ' FROM ' || table_name;
> v_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(v_handle, stmt, DBMS_SQL.NATIVE);
> DBMS_SQL.DEFINE_COLUMN(v_handle, 1, row_id);
> DBMS_SQL.DEFINE_COLUMN(v_handle, 2, g);
> v_rows := DBMS_SQL.EXECUTE(v_handle);
>
> LOOP
> EXIT WHEN DBMS_SQL.FETCH_ROWS(v_handle) = 0;
> DBMS_SQL.COLUMN_VALUE(v_handle, 1, row_id);
> DBMS_SQL.COLUMN_VALUE(v_handle, 2, g);
> END LOOP;
>
>
> Tomek
Received on Sat Jan 10 2004 - 22:35:48 CST