Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Select fields
Dynamic Select fields [message #185718] Thu, 03 August 2006 04:35 Go to next message
mstern
Messages: 2
Registered: August 2006
Junior Member
Hello!

This is my problem and I hope somebody can help me ...

PROBLEM DESCRIPTION
-------------------

I have about 95 tables in a ORACLE 10g Express databse (Windows and Solaris). In each of these tables the first field/column contains a date field.

The names of these first columns are different in nearly every table.


I need a Select statement/procedure (...) which does the following:

-) get every table from the actual user
-) get the NAME of the first field/column of each of these tables. (This is always a date field.)
-) get the different values in this date field for every table.


EXAMPLE
-------

Let's conisder these three tables:


SQL> desc "alphaauskunft";
 Name              Null?    Typ
 ----------------- -------- ------------
 Sicherungstermin           DATE
 Mandant                    CHAR(3)
 Name                       VARCHAR2(18)

SQL> desc "BEI00NET";
 Name              Null?    Typ
 ----------------- -------- ------------
 Sicherungspunkt            DATE
 Mandant                    CHAR(3)

SQL> desc "c5j3";
 Name              Null?    Typ
 ----------------- -------- ------------
 Periode                    DATE
 Mandant                    CHAR(3)



In each table the first filed is a date field. The names of these fields are:

Sicherungstermin
Sicherungspunkt
Periode



WHAT I TRIED
============

This gives me a list of all user tables:

SELECT TABLE_NAME from USER_TABLES ORDER BY TABLE_NAME;


This gives me the name of the first field/column of a specific table:

select column_name from user_tab_columns where table_name = 'alphaauskunft' and rownum = 1


This gives me the different Entries in a table:

SELECT DISTINCT("Sicherungstermin") FROM "alphaauskunft";


MY QUESTION
===========

How can I combine these the SELECT Statements in one query? Or procedure or ...?!?


It should look something like this (it does not work this way):

SELECT DISTINCT("Fieldname") FROM TABLE "Tablename";

Tablename should be replaced by every table from the actual user
Fieldname should be replaced by the name of the first field/column of each table Tablename



Hope someone can help me ...


Thx, Mac
Re: Dynamic Select fields [message #185728 is a reply to message #185718] Thu, 03 August 2006 05:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

The simplest Option is is to use this script ..

SELECT 'SELECT DISTINCT("'||COLUMN_NAME||'") FROM "'||TABLE_NAME||'";'
 FROM USER_TAB_COLUMNS WHERE COLUMN_ID=1


This will provide the Sqlqueriws you are in need of .

You can spooll it into a file and can execue ...

Thumbs Up
Rajuvan
Re: Dynamic Select fields [message #185731 is a reply to message #185728] Thu, 03 August 2006 06:06 Go to previous message
mstern
Messages: 2
Registered: August 2006
Junior Member
Great! Thank you very much. This helped me a lot.
Previous Topic: Connect to other database in PL/SQL
Next Topic: XMLType and Oracle 9i
Goto Forum:
  


Current Time: Sun Dec 04 02:15:56 CST 2016

Total time taken to generate the page: 0.06793 seconds