Looking for data browsers

From: Stephane Hamel <shamel_at_glock.mais.hydro.qc.ca>
Date: Wed, 4 May 1994 22:15:07 GMT
Message-ID: <CpAtt7.66q_at_ireq.hydro.qc.ca>

I have been assigned the mission of finding a good Oracle data browser with the following indications:

• Must work under Solaris 2.3/Oracle 7.0.15 (or up)
• Must accept large and complex queries (see partial example bellow...) Some tables have more than 500 000 records... and still growing!
• Should have the possibility to export data in various popular formats (Lotus, Excel, flat files etc...)
• User friendly, GUI based, Customizable...

Here's an example of the kind of query I had to do (I choose OraPerl, which seems to be the fastest solution other than writting the whole damn thing in Pro*C!)

Actually, most of those things are done with reports (either Pro*C or SQL*Report), but there is always the need to export data to other applications. Special queries are also often required.

Here's a "partial" example of one of our query:

stuff removed...

# Fetch NOM_RESERVOIR for OUVRAGFE
$csr = &ora_open($lda,<<EOQ,1) || die "$Name: $ora_errstr\n";
	SELECT NOM_RESERVOIR,SYSTEME_METRIQUE
	FROM INTG.OUVRAGE
	WHERE CODE_SITE = \'$code_site\'
        AND CODE_OUVRAGE = \'$code_ouvrage\'

EOQ stuff removed...
# Fetch first INSPECTION before reference DATE $csr = &ora_open($lda,<<EOQ,1) || die "$Name: $ora_errstr\n"; SELECT TO_CHAR(T2.DATE_INSPECTION,'YY MM DD'),

       TO_NUMBER(TO_CHAR(T2.DATE_INSPECTION,'J'))-2415019 +
	DECODE(T2.HEURE_INSPECTION,24,0,T2.HEURE_INSPECTION)/24,
       T2.NO_SEQ_INSPECTION

FROM GOVT.INSPECTION T2

WHERE  T2.CODE_OUVRAGE = \'$code_ouvrage\'
AND    T2.DATE_INSPECTION <= TO_DATE(\'$date_ref\','YY/MM/DD')
ORDER BY T2.NO_SEQ_INSPECTION DESC

EOQ stuff removed...
$csr2 = &ora_open($lda,<<EOQ,25) || die "$Name: $ora_errstr\n";

SELECT T1.NO_SEQ_OBSERVATION,
       T1.REGROUPEMENT,
       T1.FRACTIONNEMENT,
       T1.ACTIF,
       T1.A_SURVEILLER,
       T1.REVISEE,
       TO_CHAR(T1.REPARATION,'YYMMDD'),
       T12.NOM_LOCALISATION,
       DECODE(\'$metric\','N',
          REPLACE(TO_CHAR(T1.CHAINAGE_DEBUT/0.3048,'990,00.0'),',','+'),
          REPLACE(TO_CHAR(T1.CHAINAGE_DEBUT,'0,000.09'),',','+')),
       DECODE(\'$metric\','N',
          REPLACE(TO_CHAR(T1.CHAINAGE_FIN/0.3048,'990,00.0'),',','+'),
          REPLACE(TO_CHAR(T1.CHAINAGE_FIN,'0,000.09'),',','+')),
       T13.NOM_TERME,
       T10.NOM_TYPE,
       T14.NOM_QUALIFICATIF,
       T1.LONGUEUR_OBSERVATION,
       T1.LARGEUR_OBSERVATION,
       T1.HAUTEUR_OBSERVATION,
       T1.ECART_AXE_MIN,
       T1.ECART_AXE_MAX,
       T1.DIST_PP,
       T1.NO_DEVERSOIR,
       T11.DESC_CAP_PORTANTE,
       T1.NIVEAU_NAPPE,
       T1.COTE_INF,
       T1.ALGUES,
       T1.RENARD,
       T1.BOULANCE,
       T1.BULLES_AIR,
       T1.COMMENTAIRE,
       T15.DESC_DEBIT
FROM GOVT.DEBIT T15,
     GOVT.QUALIFICATIF T14,
     GOVT.TERME T13,
     GOVT.LOCALISATION T12,
     GOVT.CAP_PORTANTE T11,
     GOVT.TYPE_OBSERVATION T10,
     GOVT.OBSERVATION T1

WHERE T1.CODE_OUVRAGE = \'$code_ouvrage\' AND T1.NO_SEQ_INSPECTION = (

     SELECT MAX(T2.NO_SEQ_INSPECTION)
     FROM   GOVT.OBSERVATION T2
     WHERE  T2.CODE_OUVRAGE = T1.CODE_OUVRAGE
     AND    T2.NO_SEQ_OBSERVATION = T1.NO_SEQ_OBSERVATION
     AND    T2.NO_SEQ_INSPECTION <= $noi)
AND T15.CODE_DEBIT (+) = T1.CODE_DEBIT
AND T14.CODE_QUALIFICATIF (+) = T1.CODE_QUALIFICATIF
AND T13.CODE_TERME (+) = T1.CODE_TERME
AND T12.CODE_LOCALISATION (+) = T1.CODE_LOCALISATION

AND T11.CODE_CAP_PORTANTE (+) = T1.CODE_CAP_PORTANTE AND T10.CODE_TYPE (+) = T1.CODE_TYPE
ORDER BY DECODE(T1.CODE_LOCALISATION,1,1,2,1,3,1,4,2,5,2,6,2),

      T1.CHAINAGE_DEBUT,
      T1.CODE_LOCALISATION,
      T1.NO_SEQ_OBSERVATION

EOQ for each record found...

	stuff deleted
	call a routine that gets another value which must be the nearest one
	before or after the specified date...

&post_process($Wrk,$outfile);

--
Stephane Hamel <shamel_at_mais.hydro.qc.ca>
SysAdmin/DBA mais.hydro.qc.ca
Hydro-Quebec/TDSB

Received on Thu May 05 1994 - 00:15:07 CEST