Home » SQL & PL/SQL » SQL & PL/SQL » Loop through table user_tab_cols and execute dynamic SQL for each table (Oracle 10g (Spatial))
Loop through table user_tab_cols and execute dynamic SQL for each table [message #416430] Sun, 02 August 2009 08:18 Go to next message
BeckH
Messages: 1
Registered: August 2009
Junior Member
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. I tried the Oracle User's Guide but did not come further. Has anyone a hint?

Thanks!
Michael

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;
/
Re: Loop through table user_tab_cols and execute dynamic SQL for each table [message #416431 is a reply to message #416430] Sun, 02 August 2009 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL
Section Using the EXECUTE IMMEDIATE Statement in PL/SQL

Regards
Michel
Re: Loop through table user_tab_cols and execute dynamic SQL for each table [message #416434 is a reply to message #416430] Sun, 02 August 2009 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 25571
Registered: January 2009
Location: SoCal
Senior Member
> The function GET_GTYPE() returns the geometry type (Oracle Spatial).

Please post URL to where Oracle documents GET_TYPE() function.
Re: Loop through table user_tab_cols and execute dynamic SQL for each table [message #416437 is a reply to message #416434] Sun, 02 August 2009 12:53 Go to previous message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a spatial function: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm#i1005614

Regards
Michel
Previous Topic: converting ibatis sql code to pl/sql
Next Topic: sequence in Dynamic sql
Goto Forum:
  


Current Time: Sat Aug 19 08:18:34 CDT 2017

Total time taken to generate the page: 0.02163 seconds