Home » SQL & PL/SQL » SQL & PL/SQL » Single query using different column from different table (oracle 10g)
Single query using different column from different table [message #360531] Fri, 21 November 2008 05:08 Go to next message
maddyr
Messages: 16
Registered: November 2008
Junior Member
Hi,

I got around 2 queries which i need to run on all the tables of my database...The queries run successfully when they take one column as input..Now i want to use those queries for the entire database tables...I got a table named 'user_tab_columns' which contains the names of all the columns of all the tables...Also only those columns should be fed which have a datatype of varchar2...Hope someone can please help me find a solution..

Regards,
Re: Single query using different column from different table [message #360534 is a reply to message #360531] Fri, 21 November 2008 05:13 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5203164092530

You can find lots of such stuff in Orafaq also. Please search.

Smile
Rajuvan.
Re: Single query using different column from different table [message #360561 is a reply to message #360534] Fri, 21 November 2008 06:42 Go to previous messageGo to next message
maddyr
Messages: 16
Registered: November 2008
Junior Member
Hey,

Thanks for your interest...But not exactly the thing i wanted...i want to take the column names which have a datatype of only varchar2...also the query has to execute on all the tables of the database...The details of all the column names have been stored in a seperate table calles 'user_tab_columns'...now i want to create a procedure which will pick the columns of all tables one by one and execute the query on those columns which have datatype varchar2 or varchar...hope now my prob will be much more clearer..

Regards,
Re: Single query using different column from different table [message #360566 is a reply to message #360561] Fri, 21 November 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search for Barbara Boehmer's last posts in this forum.
She gave code for this kind of question last days.

Regards
Michel
Re: Single query using different column from different table [message #360640 is a reply to message #360561] Fri, 21 November 2008 14:04 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
User_tab_columns is an Oracle data dictionary view that contains not just the table names and column names, but also the data type and other information. You can see what information is available by describing the view:

SCOTT@orcl_11g> DESCRIBE user_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)

SCOTT@orcl_11g>


So, you can select the table names, column names, and data types of whatever tables you want like so:

SCOTT@orcl_11g> COLUMN data_type FORMAT A15
SCOTT@orcl_11g> SELECT data_type, table_name, column_name
  2  FROM   user_tab_columns
  3  WHERE  table_name IN ('DEPT', 'EMP')
  4  ORDER  BY data_type, table_name, column_name
  5  /

DATA_TYPE       TABLE_NAME                     COLUMN_NAME
--------------- ------------------------------ ------------------------------
DATE            EMP                            HIREDATE
NUMBER          DEPT                           DEPTNO
NUMBER          EMP                            COMM
NUMBER          EMP                            DEPTNO
NUMBER          EMP                            EMPNO
NUMBER          EMP                            MGR
NUMBER          EMP                            SAL
VARCHAR2        DEPT                           DNAME
VARCHAR2        DEPT                           LOC
VARCHAR2        EMP                            ENAME
VARCHAR2        EMP                            JOB

11 rows selected.

SCOTT@orcl_11g>


You can select only those that are varchar2 data type by adding a filter condition to the where clause to specify that:

SCOTT@orcl_11g> SELECT table_name, column_name
  2  FROM   user_tab_columns
  3  WHERE  data_type = 'VARCHAR2'
  4  AND    table_name IN ('DEPT', 'EMP')
  5  ORDER  BY table_name, column_name
  6  /

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DEPT                           DNAME
DEPT                           LOC
EMP                            ENAME
EMP                            JOB

SCOTT@orcl_11g>


If you want to create a query using each of those columns, like:

SELECT column_name FROM table_name WHERE some_condition;

then you can loop through an implicit cursor that selects those varchar2 columns and dynamically create those select statements for each such table and column:

SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> DECLARE
  2    v_query VARCHAR2(32767);
  3  BEGIN
  4    FOR r IN
  5  	 (SELECT table_name, column_name
  6  	  FROM	 user_tab_columns
  7  	  WHERE  data_type = 'VARCHAR2'
  8  	  AND	 table_name IN ('DEPT', 'EMP')
  9  	  ORDER  BY table_name, column_name)
 10    LOOP
 11  	 v_query :=
 12  	    'SELECT ' || r.column_name ||
 13  	   ' FROM '   || r.table_name ||
 14  	   ' WHERE ' || r.column_name || ' LIKE ''%AR%''';
 15  	 DBMS_OUTPUT.PUT_LINE (v_query);
 16    END LOOP;
 17  END;
 18  /
SELECT DNAME FROM DEPT WHERE DNAME LIKE '%AR%'
SELECT LOC FROM DEPT WHERE LOC LIKE '%AR%'
SELECT ENAME FROM EMP WHERE ENAME LIKE '%AR%'
SELECT JOB FROM EMP WHERE JOB LIKE '%AR%'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>


What you do with those select statements depends on what you want. In the following PL/SQL block, I have dynamically opened a ref cursor for each of those select statements, fetched the values, and output them after the table names and column names they came from.

SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> DECLARE
  2    v_query	VARCHAR2(32767);
  3    v_refcur SYS_REFCURSOR;
  4    v_value	VARCHAR2(255);
  5  BEGIN
  6    FOR r IN
  7  	 (SELECT table_name, column_name
  8  	  FROM	 user_tab_columns
  9  	  WHERE  data_type = 'VARCHAR2'
 10  	  AND	 table_name IN ('DEPT', 'EMP')
 11  	  ORDER  BY table_name, column_name)
 12    LOOP
 13  	 v_query :=
 14  	    'SELECT ' || r.column_name ||
 15  	   ' FROM '   || r.table_name ||
 16  	   ' WHERE ' || r.column_name || ' LIKE ''%AR%''';
 17  	 OPEN v_refcur FOR V_query;
 18  	 LOOP
 19  	   FETCH v_refcur INTO v_value;
 20  	   EXIT WHEN v_refcur%NOTFOUND;
 21  	   DBMS_OUTPUT.PUT_LINE ('table:   ' || r.table_name);
 22  	   DBMS_OUTPUT.PUT_LINE ('column:  ' || r.column_name);
 23  	   DBMS_OUTPUT.PUT_LINE ('value:   ' || v_value);
 24  	   DBMS_OUTPUT.PUT_LINE ('---------------------------');
 25  	 END LOOP;
 26    END LOOP;
 27  END;
 28  /
table:   DEPT
column:  DNAME
value:   RESEARCH
---------------------------
table:   EMP
column:  ENAME
value:   WARD
---------------------------
table:   EMP
column:  ENAME
value:   MARTIN
---------------------------
table:   EMP
column:  ENAME
value:   CLARK
---------------------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


In order to provide a small example, I limited the above to the dept and emp demo tables and used a simple where condition, and used a pl/sql block. You could run it on all the tables with any query you like and put it in a procedure if you like. If you need further help, please post what you have tried, what results or errors you got, and what output you want.













Previous Topic: ORA-01406: fetched column value was truncated
Next Topic: System Event Trigger vs Database Event Trigger
Goto Forum:
  


Current Time: Tue Feb 11 20:28:22 CST 2025