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>
# 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'),
FROM GOVT.INSPECTION T2
EOQ stuff removed...
$csr2 = &ora_open($lda,<<EOQ,25) || die "$Name: $ora_errstr\n";
WHERE T1.CODE_OUVRAGE = \'$code_ouvrage\' AND T1.NO_SEQ_INSPECTION = (
ORDER BY DECODE(T1.CODE_LOCALISATION,1,1,2,1,3,1,4,2,5,2,6,2),
EOQ for each record found...
&post_process($Wrk,$outfile);
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_LOCALISATIONAND 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/TDSBReceived on Thu May 05 1994 - 00:15:07 CEST