Discussion :

[Débéa]

Bonjour,

J'aimerai executer un SQL sur les vues du dico sur des bases de données 7.3.4, 8.0.5, 8.1.7, 9i et 10g.

Mon problème est que les vues du dictionnaire peuvent avoir évoluées d'une version d'oracle à l'autre.
Par exemple un simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select to_char(startup_time,'dd/mm/yyyy hh24:mi') "Startup time", status "Status", to_char(sysdate,'dd/mm/yyyy hh24:mi') "sysdate" from v$instance;

n'est pas possible sur une base 7.

J'aimerai faire un test en fonction de la version d'oracle du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
BEGIN
  if VERSION in ('8','9','10') then
    Select to_char(startup_time,'dd/mm/yyyy hh24:mi') "Startup time", status "Status", to_char(sysdate,'dd/mm/yyyy hh24:mi') "sysdate" from v$instance;
  endif;
END;

Je peux récupérer la version dans v$version mais ce n'est pas simple car le champ banner contient plein d'info. Existe-t-il une façon plus simple ?

Merci par avance pour vos réponses.

[orafrance]

c'est aussi dans v$instance

[Débéa]

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
SQL> desc v$instance
 Name                            Null?    Type
 ------------------------------- -------- ----
 KEY                                      VARCHAR2(64)
 VALUE                                    NUMBER
 
SQL> Select banner from v$version;
 
BANNER
----------------------------------------------------------------
Oracle7 Workgroup Server Release 7.3.4.2.0 - Production
PL/SQL Release 2.3.4.4.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.1 - Production

Pas sous la même forme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SQL> select value from v$instance where key='STARTUP TIME - JULIAN';
 
     VALUE
----------
   2454340

[laurentschneider]

tu es en sqlplus?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
col _startup_time new_v startup_time nopri
set ver off
alter session set nls_date_format='dd/mm/yyyy hh24:mi';
 
select 'startup_time,' "_startup_time"
from v$version
where substr(banner, instr(banner,'Release')+8,
  instr(banner,'.')-(instr(banner,'Release')+8)) in ('8','9','10')
and banner like 'Oracle%';
 
select &startup_time status,sysdate from v$instance;

test en 10g

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
 
SQL> col _startup_time new_v startup_time nopri
SQL> set ver off
SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi';
 
Session altered.
 
SQL> select
  2  'startup_time,' "_startup_time"
  3  from v$version
  4  where
  5  substr(banner, instr(banner,'Release')+8,
  6    instr(banner,'.')-(instr(banner,'Release')+8)) in ('8','9','10')
  7  and banner like 'Oracle%';
 
 
 
 
SQL> 
SQL> select &startup_time status,sysdate
  2  from v$instance;
 
STARTUP_TIME     STATUS       SYSDATE
---------------- ------------ ----------------
19/07/2007 13:50 OPEN         28/08/2007 09:48

[kalyparker]

Pourquoi ne pas faire le test sur les 7 premiers caractères de v$version ?
en v7 -> Oracle7
en v8 -> Oracle8
en v9 -> Oracle9
en v10 -> Oracle10 (là je suis pas sure )

[laurentschneider]

bon, je n'avais pas Oracle7 pour tester. mais j'espère que ça t'a donné une piste

[orafrance]

c'est pas plus simple de vérifier la présence de la colonne VERSION dans v$instance ?

[laurentschneider]

oui, le problème c'est que version n'est apparue qu'en 8.0.5 dans la vue v$instance...

[orafrance]

donc, si version n'existe pas il est en V7

[laurentschneider]

pas bête... pour faire encore plus simple :

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
BEGIN
  for f in (
    Select to_char(startup_time,'dd/mm/yyyy hh24:mi') Startup, status, 
      to_char(sysdate,'dd/mm/yyyy hh24:mi') d from v$instance
    ) loop
      dbms_output.put_line('Startup time     Status       Sysdate');
      dbms_output.put_line('---------------- ------------ ----------------');
      dbms_output.put_line(f.startup||' '||rpad(f.status,13)||f.d);
  end loop;
exception  when others then null;
END;
/
Startup time     Status       Sysdate
---------------- ------------ ----------------
19/07/2007 13:50 OPEN         28/08/2007 10:43

[Débéa]

Problème en 7 il ne connait pas les champs :

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
SQL> BEGIN
  2    for f in (
  3      Select to_char(startup_time,'dd/mm/yyyy hh24:mi') Startup, status,
  4        to_char(sysdate,'dd/mm/yyyy hh24:mi') d from v$instance
  5      ) loop
  6        dbms_output.put_line('Startup time     Status       Sysdate');
  7        dbms_output.put_line('---------------- ------------ ----------------');
  8        dbms_output.put_line(f.startup||' '||rpad(f.status,13)||f.d);
  9    end loop;
 10  exception  when others then null;
 11  END;
 12  /
    Select to_char(startup_time,'dd/mm/yyyy hh24:mi') Startup, status,
                   *
ERREUR Ó la ligne 3 :
ORA-06550: line 3, column 20:
PLS-00201: identifier 'STARTUP_TIME' must be declared
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 28:
PLS-00364: loop index variable 'F' use is invalid
ORA-06550: line 8, column 7:
PL/SQL: Statement ignored

[orafrance]

Laurent a oublié que le parsing se fout royalement de l'exception

DBA_TAB_COLUMNS existe en 7 ?

[Débéa]

Oui

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
SQL> desc DBA_TAB_COLUMNS ;
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                     NOT NULL VARCHAR2(30)
 DATA_TYPE                                VARCHAR2(9)
 DATA_LENGTH                     NOT NULL NUMBER
 DATA_PRECISION                           NUMBER
 DATA_SCALE                               NUMBER
 NULLABLE                                 VARCHAR2(1)
 COLUMN_ID                       NOT NULL 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

On peut faire du SQL dynamique en 7 ?

[orafrance]

pas besoin de SQL dynamique.

Par contre, v$instance n'est pas une table donc dba_tab_columns c'est pas bon

[laurentschneider]

Laurent a oublié que le parsing se fout royalement de l'exception

bien sûr!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
set serverout on
DECLARE
  startup varchar2(16);  d varchar2(16);  status varchar2(12);
BEGIN
  execute immediate 'Select to_char(startup_time,''dd/mm/yyyy hh24:mi''), status, to_char(sysdate,''dd/mm/yyyy hh24:mi'') from v$instance'
    into startup,status,d;
  dbms_output.put_line('Startup time     Status       Sysdate');
  dbms_output.put_line('---------------- ------------ ----------------');
  dbms_output.put_line(startup||' '||rpad(status,13)||d);
exception
  when others then null;
END;
/

PS: dba_tab_columns contient aussi les vues

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select count(*) from dba_tab_columns where table_name='V_$INSTANCE'
16

[laurentschneider]

je n'ai pas de Oracle 7, je ne sais pas si EXECUTE IMMEDIATE va fonctionner. Sinon il faut employer DBMS_SQL

[laurentschneider]

mais au lieu de perdre du temps à maintenir des scripts pour Oracle7, tu pourrais simplement faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
PROMPT Oracle7 is not supported
select status from v$instance;

[orafrance]

c'est une idée en effet

[orafrance]

PS: dba_tab_columns contient aussi les vues

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
select count(*) from dba_tab_columns where table_name='V_$INSTANCE'
16

En effet, alors :

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
CREATE OR REPLACE FUNCTION IsVersion7 
dummy NUMBER;
BEGIN
  SELECT 1
     INTO dummy
    FROM dba_tab_columns 
  WHERE table_name='V_$INSTANCE'
      AND column_name = 'VERSION';
 
  RETURN FALSE;
EXCEPTION
WHEN NO_DATA_FOUND THEN 
  RETURN TRUE;
END;
/

Sous SQL*Plus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SPOOL exec_verif_instance.sql
BEGIN
  IF NOT IsVersion7 THEN
    dbms_output.put_line('Select to_char(startup_time,''dd/mm/yyyy hh24:mi''), status, to_char(sysdate,''dd/mm/yyyy hh24:mi'') from v$instance');
  ELSE
    dbms_output.put_line('PROMPT Version 7 not supported');
  ENDIF;  
END;
/

Et on execute exec_verif_instance.sql... c'est à peine lourd

[laurentschneider]

dans le genre lourd je verrais bien :

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
 
col INSTANCE_NUMBER nopri               
col INSTANCE_NAME nopri                 
col HOST_NAME nopri                     
col VERSION nopri                       
col STARTUP_TIME pri                  
col STATUS pri                        
col PARALLEL nopri                      
col THREAD# nopri                       
col ARCHIVER nopri                      
col LOG_SWITCH_WAIT nopri               
col LOGINS nopri                        
col SHUTDOWN_PENDING nopri              
col DATABASE_STATUS nopri               
col INSTANCE_ROLE nopri                 
col ACTIVE_STATE nopri                  
col BLOCKED nopri
alter session set nls_date_format='DD/MM/YYYY HH24:MI';
select i.*, sysdate 
from v$instance i, v$version 
where banner like 'Oracle%' and banner not like 'Oracle7%' ;