Single query using different column from different table [message #360531] |
Fri, 21 November 2008 05:08  |
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 #360561 is a reply to message #360534] |
Fri, 21 November 2008 06:42   |
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 #360640 is a reply to message #360561] |
Fri, 21 November 2008 14:04  |
 |
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.
|
|
|