Re: How to fetch object using dynamic SQL?

Tomasz Majchrzak wrote:
> 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

Unless you are using an antique version of Oracle, you seem to think version information is unimportant, why aren't you using native dynamic SQL rather than the DBMS_SQL package?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)