SQL & PL/SQL

Hi,

I want to loop through the tables listed in the sys.user_tab_cols and execute a dynamically generated SELECT statement for each of those tables.

Specifically, I want to get the values "table_name" and "column_name" of each record in sys.user_tab_cols and put together the SELECT statement "SELECT i.column_name.GET_GTYPE() FROM i.table_name" (see code below). The function GET_GTYPE() returns the geometry type (Oracle Spatial).

The function works, however, I do not know how to put together this dynamic SQL statement. Has anyone a hint?

Thanks!
Michael


-----

-- ORACLE PL/SQL

SET SERVEROUTPUT ON
DECLARE
CURSOR rec_user_tab_cols IS
SELECT t.table_name, t.column_name
FROM user_tab_cols t
BEGIN
FOR i IN user_tab_cols
LOOP

--
-- Here I want to get the geometry type for each table in the loop,
-- e.g. with SELECT i.column_name.GET_GTYPE() as geometry_type FROM i.table_name
-- but I cannot to get it started.
--

-- Output of table name, column name, and geometry type:
DBMS_OUTPUT.PUT_LINE(i.table_name || ',' || i.column_name || '...' || geometry_type);
END LOOP;
END;
/

Edited by: user11754180 on 02-Aug-2009 05:59