Amis DBA et développeurs bonjour,

Je voudrais, pour un fichier .sql contenant X SELECT, avoir en sortie QUE les SELECTs qui retournent une ligne. Est-ce possible?

Voici le contenu du fichier .sql : généré automatiquement par un autre SELECT.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
-- Le SELECT
SQL> select 'SELECT count(*), ''' || T.table_name || ''', ''' || T.column_name || '''' || ' FROM ' || O.object_name || ' WHERE upper(' || T.column_name || ') = ''TEXAS'';' FROM dba_objects O, dba_tab_cols T where T.table_name = O.object_name and O.object_type = 'TABLE' and T.owner = 'HR' and T.data_type = 'VARCHAR2' order by T.table_name, T.column_name ;
 
-- Contenu du fichier .sql
'SELECTCOUNT(*),'''||T.TABLE_NAME||''','''||T.COLUMN_NAME||''''||'FROM'||O.OBJECT_NAME||'WHEREUPPER('||T.COLUMN_NAME||')=''TEXAS'';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'REGIONS', 'REGION_NAME' FROM REGIONS WHERE upper(REGION_NAME) = 'TEXAS';
SELECT count(*), 'COUNTRIES', 'COUNTRY_NAME' FROM COUNTRIES WHERE upper(COUNTRY_NAME) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STREET_ADDRESS' FROM LOCATIONS WHERE upper(STREET_ADDRESS) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'POSTAL_CODE' FROM LOCATIONS WHERE upper(POSTAL_CODE) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'CITY' FROM LOCATIONS WHERE upper(CITY) = 'TEXAS';
SELECT count(*), 'LOCATIONS', 'STATE_PROVINCE' FROM LOCATIONS WHERE upper(STATE_PROVINCE) = 'TEXAS';
SELECT count(*), 'DEPARTMENTS', 'DEPARTMENT_NAME' FROM DEPARTMENTS WHERE upper(DEPARTMENT_NAME) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_ID' FROM JOBS WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOBS', 'JOB_TITLE' FROM JOBS WHERE upper(JOB_TITLE) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'FIRST_NAME' FROM EMPLOYEES WHERE upper(FIRST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'LAST_NAME' FROM EMPLOYEES WHERE upper(LAST_NAME) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'EMAIL' FROM EMPLOYEES WHERE upper(EMAIL) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'PHONE_NUMBER' FROM EMPLOYEES WHERE upper(PHONE_NUMBER) = 'TEXAS';
SELECT count(*), 'EMPLOYEES', 'JOB_ID' FROM EMPLOYEES WHERE upper(JOB_ID) = 'TEXAS';
SELECT count(*), 'JOB_HISTORY', 'JOB_ID' FROM JOB_HISTORY WHERE upper(JOB_ID) = 'TEXAS';
 
30 rows selected.

Une fois que j'exécute ce fichier, j'obtiens ceci à l'écran : plein de lignes avec 0...
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
...
  COUNT(*) 'LOCATION 'POSTAL_COD
---------- --------- -----------
	 0 LOCATIONS POSTAL_CODE
 
 
  COUNT(*) 'LOCATION 'POSTAL_COD
---------- --------- -----------
	 0 LOCATIONS POSTAL_CODE
 
 
  COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
	 1 LOCATIONS STATE_PROVINCE
...

Existe t-il une façon d'obtenir juste ceci comme résultat? :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
  COUNT(*) 'LOCATION 'STATE_PROVINC
---------- --------- --------------
	 1 LOCATIONS STATE_PROVINCE

Par avance merci pour votre aide.