Re: How to fetch object using dynamic SQL?

VC wrote:

> 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

Thanks for your comments. I wasn't aware that NDS didn't support spatial data types.

In 9i would it be possible to use a pipelined table function?

Thanks.

-- 
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)