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: 64153
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: 25050
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: 64153
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: Join two SQL statements
Next Topic: Variable stors the value of another variable
Goto Forum:
  


Current Time: Sun Dec 11 07:52:52 CST 2016

Total time taken to generate the page: 0.08710 seconds