How to fetch object using dynamic SQL?

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 - 14:35:32 CST