FAQ OracleConsultez toutes les FAQ
Nombre d'auteurs : 17, nombre de questions : 139, dernière mise à jour : 30 mai 2016 Ajouter une question
Cette F.A.Q. a été réalisée à partir des questions fréquemment posées sur le forum Oracle de www.developpez.com et de l'expérience personnelle des auteurs. Elle pourra traiter de tout type de questions portant sur les technologies Oracle.
Nous espérons que cette F.A.Q. saura répondre à un maximum de vos questions. Nous vous souhaitons une bonne lecture.
L'équipe Oracle de Developpez.
- Comment obtenir le nombre de lignes impactées par le dernier ordre SQL (DML) ?
- Comment afficher les erreurs de compilation ?
- Comment compter le nombre d'occurrences dans une chaîne sans l'aide d'une boucle itérative ?
- Comment obtenir le temps d'exécution instruction par instruction de vos codes PL/SQL ?
- Comment faire un REPLACE sur un CLOB ?
- Comment savoir si une chaine de caractères correspond à un nombre ?
- Comment vérifier si une chaine vérifie un certain format ?
- Ma requête fonctionne sous SQL*Plus mais lorsque j'essaie de l'utiliser dans une procédure PL/SQL, celle-ci ne compile pas !
- Comment générer un fichier de trace pour une session en cours ?
- Je n'ai pas de documentation Oracle, ni d'accès Internet, comment puis-je trouver la documentation d'un package Oracle ?
- Comment convertir une valeur hexadécimale en valeur décimale ?
- Comment afficher la différence de temps entre deux dates en nombre de jours, heures, minutes et secondes ?
- Comment exporter le résultat d'une requête au format XML ?
- Comment exécuter une instruction du DDL dans un block PL/SQL ?
- Comment implémenter une colonne à valeur auto-incrémentée ?
- Comment créer une vue paramétrée ?
- Comment exécuter un shell depuis le PL/SQL ?
- Comment crypter mon code ?
La variable SQL%ROWCOUNT contient le nombre de lignes impactées par le dernier ordre DML exécuté.
Vous pouvez interroger cette variable après tout ordre INSERT, UPDATE ou DELETE
Code sql : | Sélectionner tout |
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 | SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 LN$Cpt PLS_INTEGER ; 3 BEGIN 4 SELECT COUNT(*) INTO LN$Cpt FROM test ; 5 DBMS_OUTPUT.PUT_LINE( To_char( LN$Cpt ) || ' enregistrements') ; 6 -- Mise à jour -- 7 UPDATE test 8 SET nom = nom 9 WHERE ROWNUM <= 3 ; 10 DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements mis à jour') ; 11 -- Insertion -- 12 INSERT INTO test 13 SELECT * FROM test ; 14 DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements insérés') ; 15 -- Suppression -- 16 DELETE FROM test 17 WHERE ROWNUM < 3 ; 18 DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements supprimés') ; 19 ROLLBACK ; 20 END ; 21 / 6 enregistrements 3 enregistrements mis à jour 6 enregistrements insérés 2 enregistrements supprimés Procédure PL/SQL terminée avec succès. |
Les erreurs de compilation sont stockées dans la vue USER_ERRORS
Vous pouvez, après compilation d'un objet, afficher les éventuelles erreurs de compilation de deux façons :
- SHOW ERROR
- SELECT ... FROM USER_ERRORS
Exemple (SUBTR au lieu de SUBSTR) :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> CREATE OR REPLACE FUNCTION debut 2 ( PC$Chaine IN VARCHAR2) RETURN VARCHAR2 IS 3 BEGIN 4 RETURN SUBTR( PC$Chaine, 1, 5 ) ; 5 END; 6 / Avertissement : Fonction créée avec erreurs de compilation. SQL> SHOW ERRORS Erreurs pour FUNCTION DEBUT : LINE/COL ERROR -------- ----------------------------------------------------------------- 4/3 PL/SQL: Statement ignored 4/10 PLS-00201: l'identificateur 'SUBTR' doit être déclaré |
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> SQL> SELECT * 2 FROM user_errors 3 WHERE name = 'DEBUT' 4 AND type = 'FUNCTION' 5 ORDER BY SEQUENCE 6 / NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- DEBUT FUNCTION 1 4 10 PLS-00201: l'identificateur 'SUBTR' doit être déclaré DEBUT FUNCTION 2 4 3 PL/SQL: Statement ignored |
En utilisant les fonctions LENGTH() et REPLACE() de la façon suivante :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION CPT_OCCURRENCES ( PC$Entree IN VARCHAR2, -- Chaîne en entrée PC$Recherche IN VARCHAR2 -- Chaîne à rechercher ) RETURN PLS_INTEGER IS BEGIN RETURN ( (LENGTH(PC$Entree) - LENGTH(REPLACE(PC$Entree,PC$Recherche,NULL)) ) / NVL(LENGTH(PC$Recherche),1) ) ; END; SQL> SELECT CPT_OCCURRENCES( 'le et le et le', 'le' ) "Nombre d'occurrences" FROM DUAL ; Nombre d'occurrences -------------------- 3 |
En installant l'outil Oracle : Profiler
Vous devez être connecté avec le privilège SYSDBA
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 | -------------------------------------- -- Installation de l'outil profiler -- -------------------------------------- connect / as sysdba @<ORACLE_HOME>/rdbms/admin/proftab.sql @<ORACLE_HOME>/rdbms/admin/profload.sql @<ORACLE_HOME>/plsql/demo/profrep.sql |
Code sql : | Sélectionner tout |
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | ---------------------------------------------- -- Test de la procédure stockée : F_TRACE() -- ---------------------------------------------- SQL> 1 DECLARE 2 Pgm NUMBER; 3 BEGIN 4 DBMS_PROFILER.START_PROFILER('test','test1',Pgm); 5 FD.F_TRACE('Hello Oracle world','T'); 6 DBMS_PROFILER.STOP_PROFILER; 7 DBMS_PROFILER.ROLLUP_RUN(Pgm); 8 PROF_REPORT_UTILITIES.PRINT_RUN(Pgm); 9* END; SQL> / ===========================Results for run #1 made on 23-OCT-04 14:20:55 ========================= (test) Run total time: .16 seconds Unit #1: SYS.DBMS_PROFILER - Total time: .00 seconds Unit #2: <anonymous>.<anonymous> - Total time: .00 seconds Unit #3: FD.F_TRACE - Total time: .02 seconds 1 procedure F_TRACE ( PC$Message in VARCHAR2, PC$Output in VARCHAR2 DEFAULT 'S' ) 2 Is 3 PRAGMA AUTONOMOUS_TRANSACTION ; 4 LC$String Varchar2(4000) ; 5 LN$Slices PLS_INTEGER ; 6 LN$Rest PLS_INTEGER ; 7 1 ,00001660 ,00001660 LN$Pos PLS_INTEGER := 1 ; 8 LN$Inc PLS_INTEGER ; 9 Begin 10 11 1 ,00009573 ,00009573 If Upper( PC$Output ) = 'S' Then 12 -- Screen output (DBMS_OUTPUT) -- 13 0 0 LN$Inc := 255 ; 14 0 0 LN$Slices := Length( PC$Message ) / LN$Inc ; 15 0 0 LN$Rest := MOD( Length( PC$Message ), LN$Inc ) ; 16 0 0 If LN$Rest > 0 Then LN$Slices := LN$Slices + 1 ; End if ; 17 18 -- output -- 19 0 0 For i in 1..LN$Slices Loop 20 0 0 LC$String := Substr( PC$Message, LN$Pos, LN$Inc ) ; 21 0 0 DBMS_OUTPUT.PUT_LINE( LC$String ) ; 22 0 0 LN$Pos := LN$Pos + LN$Inc ; 23 End loop ; 24 25 Else 26 -- Table output (INSERT) -- 27 1 ,00000095 ,00000095 LN$Inc := 4000 ; 28 1 ,00003589 ,00003589 LN$Slices := Length( PC$Message ) / LN$Inc ; 29 1 ,00001622 ,00001622 LN$Rest := MOD( Length( PC$Message ), LN$Inc ) ; 30 1 ,00000411 ,00000411 If LN$Rest > 0 Then LN$Slices := LN$Slices + 1 ; End if ; 31 32 -- output -- 33 2 ,00004027 ,00002013 For i in 1..LN$Slices Loop 34 1 ,00001587 ,00001587 LC$String := Substr( PC$Message, LN$Pos, LN$Inc ) ; 35 1 ,01580613 ,01580613 Insert into TRACE (LIGNE) Values ( LC$String ) ; 36 1 ,00094689 ,00094689 Commit ; 37 1 ,00000270 ,00000270 LN$Pos := LN$Pos + LN$Inc ; 38 End loop ; 39 End if ; 40 41 End; Procédure PL/SQL terminée avec succès. |
Le package DBMS_LOB ne fournit pas de procédure ou fonction REPLACE pour les CLOBs. En voici une :
Code sql : | Sélectionner tout |
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 40 41 42 43 44 45 46 47 48 49 50 51 52 | CREATE OR REPLACE PROCEDURE lob_replace(p_dest_clob IN OUT CLOB, p_search VARCHAR2, p_replace VARCHAR2) AS v_clob_temp CLOB; v_end_offset INTEGER := 1; v_start_offset INTEGER := 1; v_occurence NUMBER := 1; v_replace_len NUMBER := length(p_replace); v_clob_temp_len NUMBER := 0; v_dest_clob_len NUMBER := 0; BEGIN IF dbms_lob.ISOPEN(p_dest_clob) = 0 THEN NULL; END IF; dbms_lob.createtemporary(v_clob_temp, TRUE, dbms_lob.session); LOOP v_end_offset := dbms_lob.instr(p_dest_clob, p_search, 1, v_occurence); IF v_end_offset = 0 THEN v_clob_temp_len := dbms_lob.getlength(v_clob_temp); v_dest_clob_len := dbms_lob.getlength(p_dest_clob) - v_start_offset + 1; IF v_dest_clob_len > 0 THEN dbms_lob.copy(v_clob_temp, p_dest_clob, v_dest_clob_len, v_clob_temp_len + 1, v_start_offset); END IF; EXIT; END IF; v_clob_temp_len := dbms_lob.getlength(v_clob_temp); IF (v_end_offset - v_start_offset) > 0 THEN dbms_lob.copy(v_clob_temp, p_dest_clob, (v_end_offset - v_start_offset), v_clob_temp_len + 1, v_start_offset); END IF; v_start_offset := v_end_offset + length(p_search); v_occurence := v_occurence + 1; IF p_replace IS NOT NULL THEN dbms_lob.writeappend(v_clob_temp, v_replace_len, p_replace); END IF; END LOOP; IF length(p_search) > length(p_replace) THEN dbms_lob.TRIM(p_dest_clob, dbms_lob.getlength(v_clob_temp)); END IF; dbms_lob.copy(p_dest_clob, v_clob_temp, dbms_lob.getlength(v_clob_temp), 1, 1); EXCEPTION WHEN OTHERS THEN htp.prn('[Error in lob_replace : ' || SQLCODE || ' ' || SQLERRM || ']'); END; |
Plutôt que de coder une fonction qui traite tous les cas de figure correspondant aux différents formats que peut prendre un nombre (entier, nombre à virgule, nombre avec exposant…), il vaut mieux laisser Oracle gérer la conversion et nous signaler s'il a rencontré une erreur comme dans la fonction suivante :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 | create or replace function isNumeric(x in varchar2) return number as -- renvoie 1 si le paramètre correspond à un nombre -- 0 sinon nb number; begin nb := to_number(x); return 1; exception when others then return 0; end; / |
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> SELECT x, 2 isNumeric(x), 3 CASE 4 WHEN isNumeric(x) = 1 THEN 1000 * TO_NUMBER(x) 5 END x_fois_1000 6 FROM TEST; X ISNUMERIC(X) X_FOIS_1000 ---------- ------------ ----------- 1 1 1000 1,10 1 1100 1,234E3 1 1234000 -1,23E-3 1 -1,23 1 toto 0 |
Pour vérifier qu'une chaine de caractère vérifie un format donné, on peut utiliser, à partir d'Oracle 10g, la fonction MATCH du OWA_PATTERN qui permet de manipuler des expressions régulières. Cette fonction retourne un booléen indiquant si le format est vérifié ou pas; elle ne peut donc être appelée qu'en PL/SQL.
Par exemple, pour vérifier que les numéros de téléphones en base suivent le format français sur 10 chiffres avec comme séparateur des tirets, on peut faire :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> select * 2 from TELEPHONE; TELEPHONE# -------------------------------------------------------------------------------- 07.08.09.02.02 07-08-09-02-02 01 02 02 02 02 TOTO 07/07/07/07/07 SQL> 1 begin 2 for tel in (select * from TELEPHONE) 3 loop 4 if owa_pattern.match(tel.telephone#, '^\d{2}-\d{2}-\d{2}-\d{2}-\d{2}$') then 5 dbms_output.put_line(tel.telephone#); 6 end if; 7 end loop; 8* end; SQL> / 07-08-09-02-02 PL/SQL procedure successfully completed. |
- ^ : début de la ligne
- $ : saut de ligne ou fin de ligne
- \n : saut de ligne
- . : tout caractères sauf le saut de ligne
- \t : tabulation
- \d : chiffre (équivalent à [0-9])
- \D : tout caractère sauf un chiffre (équivalent à [not 0-9])
- \w : tout caractère alphanumérique (chiffres, lettres, « _ »)
- \W : tout caractère sauf un caractère alphanumérique
- \s : tout espace (espace, tabulation, saut de ligne)
- \S : tout caractère sauf un espace
- \b : délimiteur de mots (entre un caractère qui vérifie \w et un autre qui vérifie \W)
- \xnn : caractère dont le code ASCII est en hexadécimal nn
- \nnn : caractère dont le code ASCII est en octal nnn
Les éléments ci-dessus peuvent être suivi par les indicateurs de cardinalité suivants :
- ? : 0 ou 1 occurrence
- * : 0 ou plus occurrences
- + : 1 ou plus occurrences
- {n} : exactement n occurrences
- {n,} : au moins n occurrences
- {n,m} : entre n et m occurrences
L'expression régulière qu'on a utilisé pour décrire notre format de numéro de téléphone ^\d{2}-\d{2}-\d{2}-\d{2}-\d{2}$ se décrypte donc de la manière suivante :
- \d{2} : on cherche deux chiffres
- - : suivis d'un tiret
- \d{2}- : suivi de deux chiffres et d'un tiret
- \d{2}- : suivi de deux chiffres et d'un tiret
- \d{2}- : suivi de deux chiffres et d'un tiret
- \d{2} : suivi de deux chiffres
Comme notre expression régulière commence par ^, elle décrit le début de la chaîne; comme elle se termine par $, elle décrit aussi sa fin.
Sous Oracle 8i, le moteur SQL utilisé dans les procédures PL/SQL est différent et est en retard sur le moteur SQL utilisé pour les requêtes. Certaines fonctionnalités disponibles en SQL ne le sont donc pas en PL/SQL comme :
- les structures CASE ... WHEN
- les fonctions analytiques
La solution est d'utiliser du SQL dynamique pour masquer la requête au moteur PL/SQL en utilisant par exemple EXECUTE IMMEDIATE :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 | begin execute immediate ' insert into TEST select case when deptno = 10 then ''Dept 10'' when deptno = 20 then ''Dept 20'' else ''Departement inconnu'' end from TEST1'; end; |
Quand une procédure est en cours d'exécution, il est possible de générer un fichier de trace (pour exploitation par TkProf par exemple) en utilisant la routine set_sql_trace_in_session du package DBMS_SYSTEM. Les paramètres de cette routine sont les suivants :
- SID : identifiant de la session en provenance de V$SESSION
- SERIAL# : second identifiant de la session en provenance de V$SESSION
- TRACE: booléen; il faut entrer TRUE pour demander à Oracle de générer le fichier de trace, et FALSE pour arrêter sa génération
Vous trouverez plus de détails dans cet article : Différentes façon de tracer l'activité de sessions
Pour avoir immédiatement la documentation minimale d'un package Oracle installé, on peut utiliser la requête suivante sous SQL*Plus :
Code sql : | Sélectionner tout |
1 2 3 4 5 | SELECT text FROM DBA_SOURCE WHERE type = 'PACKAGE' AND name like UPPER('%&package%') ORDER BY line; |
Code sql : | Sélectionner tout |
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 | SQL> SELECT text 2 FROM dba_source 3 WHERE type = 'PACKAGE' 4 AND name like UPPER('%&package%'); Enter value for package: dbms_output old 4: name like UPPER('%&package%') new 4: name like UPPER('%dbms_output%') ------------ -- OVERVIEW -- -- These procedures accumulate information in a buffer (via "put" and -- "put_line") so that it can be retrieved out later (via "get_line" or -- "get_lines"). If this package is disabled then all -- calls to this package are simply ignored. This way, these routines -- are only active when the client is one that is able to deal with the -- information. This is good for debugging, or SP's that want to want -- to display messages or reports to sql*dba or plus (like 'describing -- procedures', etc.). The default buffer size is 20000 bytes. The -- minimum is 2000 and the maximum is 1,000,000. ... procedure put_line(a varchar2); pragma restrict_references(put_line,WNDS,RNDS); procedure put_line(a number); pragma restrict_references(put_line,WNDS,RNDS); -- Put a piece of information in the buffer followed by an end-of-line -- marker. When retrieved by get_line(s), the number and date items -- will be formated with to_char using the default formats. If you -- want another format then format it explicitly. get_line(s) return -- "lines" as delimited by "newlines". So every call to put_line or -- new_line will generate a line that will be returned by get_line(s). -- Input parameters: -- a -- Item to buffer -- Errors raised: -- -20000, ORU-10027: buffer overflow, limit of <buf_limit> bytes. -- -20000, ORU-10028: line length overflow, limit of 255 bytes per line |
Simplement grâce la fonction TO_NUMBER :
En voici un exemple d'utilisation :
Code sql : | Sélectionner tout |
1 2 3 4 5 | SQL> SELECT TO_NUMBER('ff','XXXXXXXX'),TO_NUMBER('fff','XXXXXXXX'),TO_NUMBER('ffff','XXXXXXXX') from dual; TO_NUMBER('FF','XXXXXXXX') TO_NUMBER('FFF','XXXXXXXX') TO_NUMBER('FFFF','XXXXXXXX') -------------------------- --------------------------- ---------------------------- 255 4095 65535 |
Grâce la fonction suivante :
Code sql : | Sélectionner tout |
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 | CREATE OR REPLACE FUNCTION Diff_Temps ( LD$Date_Deb IN DATE DEFAULT SYSDATE ,LD$Date_Fin IN DATE DEFAULT SYSDATE ,LN$JJ OUT PLS_INTEGER ,LN$HH OUT PLS_INTEGER ,LN$MI OUT PLS_INTEGER ,LN$SS OUT PLS_INTEGER ) Return NUMBER IS dif NUMBER ; Begin If LD$Date_Fin < LD$Date_Deb Then Return ( -1 ) ; End if ; Select LD$Date_Fin - LD$Date_Deb Into dif From DUAL ; Select trunc ( LD$Date_Fin - LD$Date_Deb) Into LN$JJ From DUAL ; Select trunc ( (LD$Date_Fin - LD$Date_Deb) * 24) - ( LN$JJ * 24 ) Into LN$HH From DUAL ; Select trunc ( (LD$Date_Fin - LD$Date_Deb) * 1440) - ( (LN$HH * 60) + ( LN$JJ * 1440) ) Into LN$MI From DUAL ; Select trunc ( (LD$Date_Fin - LD$Date_Deb) * 86400) - ( (LN$MI * 60) + (LN$HH * 3600) + ( LN$JJ * 3600 * 24 ) ) Into LN$SS From DUAL ; Return( dif ) ; End ; / |
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> set serveroutput on SQL> declare 2 dd pls_integer; 3 hh pls_integer; 4 mi pls_integer; 5 ss pls_integer; 6 dif number ; 7 Begin 8 dif := diff_temps ( sysdate, sysdate + 10.523, dd,hh,mi,ss ) ; 9 dbms_output.put_line( 10 '(' || ltrim(to_char(dif,'99999.99999')) || ')' || ' ' 11 || to_char(dd,'99999') || 'j ' 12 || to_char(hh,'00') ||':' 13 || to_char(mi,'00') ||':' 14 || to_char(ss,'00') 15 ) ; 16 End; 17 / (10.52300) 10j 12: 33: 07 Procédure PL/SQL terminée avec succès. |
À partir d'Oracle 9i, vous pouvez utiliser le package DBMS_XMLQuery. En voici deux exemples d'utilisation :
- Avec Utl_File :
Code sql : | Sélectionner tout |
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 | SET SERVEROUTPUT ON DECLARE XmlFic Utl_File.File_Type; XmlData CLOB; Fin BOOLEAN := TRUE; BEGIN -- Créer des données au format XML à partir d'une requête : XmlData := DBMS_XMLQuery.GetXML ('SELECT * FROM EMP'); -- Copie les données au format XML dans un fichier : XmlFic := Utl_File.FOpen ('C:\TMP\XML', 'TEST.xml', 'W'); WHILE FIN LOOP Utl_File.Put (XmlFic, SUBSTR (XmlData, 1, 32767)); IF LENGTH (XmlData) > 32767 THEN XmlData := SUBSTR (XmlData, 32768); ELSE FIN := FALSE; END IF; END LOOP; Utl_File.FClose (XmlFic); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM,1,255)); Utl_File.FClose (XmlFic); END; / |
- Sans Utl_File : compilez d'abord la procédure suivante :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE OR REPLACE PROCEDURE OutPut_Fichier (fichier IN OUT NOCOPY CLOB) IS ContenuXML VARCHAR2 (32767); Ligne VARCHAR2 (2000); BEGIN ContenuXML := dbms_lob.SUBSTR(fichier, 32767); LOOP EXIT WHEN ContenuXML IS NULL; -- découpe en lignes : Ligne := SUBSTR(ContenuXML, 1, INSTR(ContenuXML, CHR(10)) - 1); dbms_output.put_line (Ligne); ContenuXML := SUBSTR(ContenuXML, INSTR(ContenuXML, CHR(10)) + 1); END LOOP; END; / |
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 | SET SERVEROUTPUT ON SIZE 1000000 DECLARE fichier CLOB; BEGIN fichier := dbms_XMLQuery.getXML ('SELECT * FROM EMP'); OutPut_Fichier (fichier); END; / |
Si vous souhaitez exécuter un ordre du DDL (Data Description Language) pour créer une table, un index, altérer un objet existant, utilisez l'instruction EXECUTE IMMEDIATE.
Par exemple, pour forcer la recréation d'un index:
Code sql : | Sélectionner tout |
1 2 3 4 5 6 | Declare LC$OrdreSql Varchar2(2000) ; Begin LC$OrdreSql := 'ALTER INDEX my_index REBUILD' ; EXECUTE IMMEDIATE LC$OrdreSql ; End; |
Les champs de type « Auto-Increment » n'existe pas en Oracle. Pour émuler ce comportement, vous avez besoin
- d'une séquence pour incrémenter la valeur de 1 à chaque insertion
- d'un trigger pour renseigner automatiquement le champ auto-incrémenté de votre table.
Voici comment procéder :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE SEQUENCE MaSequence START WITH 1 MAXVALUE 999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; CREATE TRIGGER MonTrigger BEFORE INSERT ON MaTable FOR EACH ROW BEGIN SELECT MaSequence.NEXTVAL INTO :NEW.macolonne_PK FROM DUAL; END; / |
Il est également possible de se passer du trigger, et d'opter pour la syntaxe suivante :
Code sql : | Sélectionner tout |
1 2 | INSERT INTO MaTable (MaColonne_PK, ...) VALUES (maSequence.NextVal, ...); |
Il peut être utile de rendre certains objets dynamiques, pour plus de souplesse dans vos applications. Or, les vues sont souvent « créées en dur » dans la base, ce qui peut se révéler ennuyeux.
Voici donc une méthode pour les rendre dynamiques :
Créez d'abord un package, avec un paramètre global param1 :
Code sql : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE PACKAGE orafrance AS param1 VARCHAR2 (30); FUNCTION get_param1 RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY orafrance AS FUNCTION get_param1 RETURN VARCHAR2 AS BEGIN RETURN param1; END; END; / |
Code sql : | Sélectionner tout |
1 2 3 4 5 | CREATE OR REPLACE VIEW v_orafrance AS SELECT * FROM all_tables WHERE table_name = UPPER (orafrance.get_param1); |
Code sql : | Sélectionner tout |
SQL> execute orafrance.param1 := 't_orafrance';
Code sql : | Sélectionner tout |
1 2 3 4 5 | SQL> select table_name from v_orafrance; TABLE_NAME ------------------------------ T_ORAFRANCE |
Cette méthode est valable jusqu'en version 9 :
Pour exécuter une commande système depuis du PL/SQL nous allons utiliser une classe Java. La création d'un classe java exécutable en PL/SQL passe par trois étapes :
- tout d'abord on créé la Java Source
- ensuite on publie la spécification d'appel de cette source
- et enfin on accorde les privilèges nécessaires à l'exécution de cette classe Java
Donc on va tout d'abord créer la classe Java permettant d'exécuter une commande système :
Code java : | Sélectionner tout |
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS import java.io.*; public class Host { public static void executeCommand(String command) { try { String[] finalCommand; if (isWindows()) { finalCommand = new String[4]; finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; finalCommand[1] = "/y"; finalCommand[2] = "/c"; finalCommand[3] = command; } else { finalCommand = new String[3]; finalCommand[0] = "/bin/sh"; finalCommand[1] = "-c"; finalCommand[2] = command; } final Process pr = Runtime.getRuntime().exec(finalCommand); new Thread(new Runnable() { public void run() { try { BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream())); String buff = null; while ((buff = br_in.readLine()) != null) { System.out.println("Process out :" + buff); try {Thread.sleep(100); } catch(Exception e) {} } br_in.close(); } catch (IOException ioe) { System.out.println("Exception caught printing process output."); ioe.printStackTrace(); } } }).start(); new Thread(new Runnable() { public void run() { try { BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream())); String buff = null; while ((buff = br_err.readLine()) != null) { System.out.println("Process err :" + buff); try {Thread.sleep(100); } catch(Exception e) {} } br_err.close(); } catch (IOException ioe) { System.out.println("Exception caught printing process error."); ioe.printStackTrace(); } } }).start(); } catch (Exception ex) { System.out.println(ex.getLocalizedMessage()); } } public static boolean isWindows() { if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1) return true; else return false; } }; / show errors java source "Host" |
Code sql : | Sélectionner tout |
1 2 3 4 | CREATE OR REPLACE PROCEDURE Host_Command (p_command IN VARCHAR2) AS LANGUAGE JAVA NAME 'Host.executeCommand (java.lang.String)'; / |
Code sql : | Sélectionner tout |
1 2 3 4 5 | EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', '<>', 'read ,write, execute, delete'); EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', ''); EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', ''); |
Ensuite on lance notre commande
Code sql : | Sélectionner tout |
1 2 3 4 5 6 | SET SERVEROUTPUT ON SIZE 1000000 CALL DBMS_JAVA.SET_OUTPUT(1000000); BEGIN Host_Command (p_command => 'move C:\test1.txt C:\test2.txt'); END; / |
Code : | Sélectionner tout |
$ORACLE_HOME\javavm\install\initjvm.sql
Il est parfois nécessaire, lors du déploiement de vos applicatifs, de crypter votre code afin de le protéger : il suffit pour cela de sauvegarder votre code sous forme de fichiers sql et d'utiliser le programme wrap.exe, se trouvant dans le répertoire {Oracle_Home}\Bin\.
Le programme wrap accepte les deux arguments suivants :
- iname : nom du fichier en entrée
- oname : nom du fichier de sortie
Voici un exemple avec le fichier suivant :
Code sql : | Sélectionner tout |
1 2 3 4 5 | CREATE OR REPLACE FUNCTION debut (PC$Chaine IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR( PC$Chaine, 1, 5 ) ; END; |
Code : | Sélectionner tout |
1 2 3 4 5 6 7 | ...\BIN> wrap iname=c:\temp\debut.pls oname=c:\temp\debut.plb PL/SQL Wrapper: Release 10.2.0.1.0- Production on Dim. Oct. 15 23:52:50 2006 Copyright (c) 1993, 2004, Oracle. All rights reserved. Processing c:\temp\debut.pls to c:\temp\debut.plb |
Attention, prenez évidemment soin de sauvegarder vos sources, cette procédure est bien entendu irréversible ! |
Code sql : | Sélectionner tout |
1 2 3 | SQL> @c:\temp\debut.plb
Fonction créée. |
Attention, ceci ne fonctionne pas sur les triggers ou les blocs PL/SQL anonymes, cf la documentation Oracle |
Code : | Sélectionner tout |
1 2 3 4 5 6 7 8 9 10 11 12 13 | The following CREATE statements are obfuscated: CREATE [OR REPLACE] FUNCTION function_name CREATE [OR REPLACE] PROCEDURE procedure_name CREATE [OR REPLACE] PACKAGE package_name CREATE [OR REPLACE] PACKAGE BODY package_name CREATE [OR REPLACE] TYPE type_name AS OBJECT CREATE [OR REPLACE] TYPE type_name UNDER type_name CREATE [OR REPLACE] TYPE BODY type_name The CREATE [OR REPLACE] TRIGGER statement, and [DECLARE] BEGIN..END anonymous blocks, are not obfuscated. All other SQL statements are passed unchanged to the output file. All comment lines in the unit being wrapped are deleted, except for those in a CREATE OR REPLACE header and C-style comments (delimited by /* */). |
Proposer une nouvelle réponse sur la FAQ
Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour çaLes sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2024 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.