Articles avec #oracle tag

Publié le 19 Mai 2010

Pour exécuter une fonction définie dans un package Oracle sous sqlplus, il suffit de l'appeler au travers d'un "select from dual" de la manière suivante:

 

SELECT MON_PACKAGE.MA_FONCTION(mes, parametres) FROM DUAL;

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 12 Mai 2010

Aujourd'hui une petite astuce pour améliorer le temps d'exécution d'une requête SQL Oracle lorsqu'on travaille sur de gros volumes de données, du genre plusieurs millions d'enregistrements.

 

Dans ce cas un simple SELECT peut prendre plusieurs minutes pour s'exécuter avant de retourner un résultat.

 

Voici donc comment paralléliser la requête et gagner du temps d'exécution:

 

SELECT mon_champ /*+ PARALLEL(ma_table 5)*/ FROM ma_table WHERE ....

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 23 Avril 2010

Tout d'abord il faut un fichier SQL (appelons le patatos.sql) qui va aller effectuer une requête en base de données :

 
SET ServerOutput ON;
DECLARE
   tmp INTEGER;
BEGIN
 SELECT COUNT(*) INTO tmp FROM my_table WHERE my_key='patatos';
 DBMS_OUTPUT.PUT_LINE('PATATOS=' || tmp);
END;
/
EXIT;

 

Ensuite il faut un script shell (patatos.ksh) qui va exécuter le fichier SQL et récupérer la valeur retournée:


#!/usr/bin/ksh
# on efface le log d'une utilisation précédente éventuelle

rm sqlplus.log > /dev/null 2>&1
# on exécute le fichier SQL
sqlplus -L ${USER}/${PASSWORD}@${DATABASE} @patatos.sql > sqlplus.log
# on analyse si le fichier de log contient des erreurs
RESULTAT=$(cat sqlplus.log | grep -v "ORA-28002" | grep 'ORA-' | wc -l)
if [ $RESULTAT != 0 ]
then
   echo "   ----    Erreur(s) SQL"
   cat sqlplus.log | grep -v "ORA-28002" | grep 'ORA-'
   echo "   ----    Verifier sqlplus.log pour plus d'information"
   exit 1
fi
# pas d'erreur: on récupère la valeur retournée par la commande SQL
NB_PATATOS=$(cat sqlplus.log | grep 'PATATOS=' | cut -d'=' -f2)
echo "Il y a $NB_PATATOS patatos en base de donnees"

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 7 Avril 2010

Si vous avez besoin de connaître le nombre de lignes traitées (par exemple le nombre de ligne crée ou modifiée dans une requête) il faut utiliser SQL%ROWCOUNT

 

INSERT INTO ma_table (ma_cle, ma_valeur) VALUES ('key_1', 'value_1');
IF SQL%ROWCOUNT = 0 THEN
   DBMS_OUTPUT.PUT_LINE('Aucune ligne créée');
ELSE
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' lignes créées');
END IF;

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 6 Avril 2010

J'ai eu récemment besoin d'insérer des données dans une nouvelle table d'une base Oracle. Ces données provenait, pour l'essentiel, d'une autre table existante.

 

Heueusement il y a moyen d'imbriquer un SELECT dans un INSERT afin de récupérer les informations nécessaires directement dans une seule requête SQL.

 

INSERT INTO ma_nouvelle_table (numero_client, date, info1) SELECT '000' || id_client, sysdate, info1 FROM ma_table;

 

Et voilà cette requête permet de copier les enregistrement de la table ma_table vers ma_nouvelle_table.

 

En plus le numero_client de ma_nouvelle_table n'a pas le même format que l'id_client de ma_table. Ici on ajoute le préfixe '000' à id_client pour former numero_client.

 

C'est pas très compliqué mais ça rend bien service!!

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 2 Avril 2010

Cette erreur se produit lorsque dans une procédure on effectue un select avec une clause WHERE qui ne retourne aucune donnée.

 

Dans ce cas le résultat n'est pas NULL mais une exception NO_DATA_FOUND (ORA-01403) est générée.

 

Le traitement s'effectue donc de la manière suivante (ici on vérifie si la valeur en base est identique à la nouvelle valeur):

 

FUNCTION saveData(
            maCle     IN MA_TABLE.CLE%TYPE,
            maValeur  IN MA_TABLE.VALEUR%TYPE,
            erreurMessage OUT VARCHAR) RETURN NUMBER
IS
   retCode := 0;
   miseAJour := 0;
BEGIN
   erreurMessage := NULL;
   SELECT DECODE(CLE, maCle, 0, 1) INTO miseAJour FROM MA_TABLE WHERE CLE=maCle;
   IF miseAJour = 1 THEN
      /* Les valeurs ne sont pas les mêmes -> UPDATE */
      UPDATE MA_TABLE SET VALEUR=maValeur WHERE CLE=maCle;
   END IF;
   /* Ne pas faire de IF miseAJour = NULL THEN car ce IF n'est jamais atteint: L'Exception est générée au moment du select */
   RETURN(retCode);
EXCEPTION
   WHEN
NO_DATA_FOUND THEN
   BEGIN

      /* la cle n'existe pas -> INSERTION */
      INSERT INTO MA_TABLE (CLE, VALEUR) VALUES (maCle, maValeur);
      RETURN(retCode);
   END;
   WHEN OTHERS THEN
       errorMsg := SQLERRM;
       RETURN(SQLCODE);
END saveData;

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 19 Mars 2010

Pour compléter l'article sur "comment exécuter un fichier SQL depuis un script avec SQL*Plus", voici comment passer une variable au fichier SQL.

Il faut rajouter les paramètres à la fin de la ligne de commande après avoir spécifié le fichier sql :

sqlplus -L <username>/<password>@<database> @<fichier_sql> <param1> <param2> <etc>

Ensuite on récupère les paramètres dans le sql en utilisant la syntaxe suivante :

ma_variable_numerique := &1;
ma_variable_string := '&2';


Attention à ne pas oublier les guillements simples si le paramètre passée est une string (VARCHAR).

Voici par exemple un fichier SQL qui effectue un simple select :

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
   mycond1  VARCHAR2(255);
   mycond2  NUMBER;
   myresult VARCHAR2(255);
BEGIN
   mycond1 := '&1';
   mycond2 := &2;
   SELECT name INTO myresult FROM films WHERE realisateur=mycond1 AND annee=mycond2;
   DBMS_OUTPUT.PUT_LINE('Titre : ' || myresult);
END;
/

Puis pour l'appeler on utilise sqlplus:

sqlplus -L <username>/<password>@<database> @mon_fichier.sql cameron 2009



Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 9 Mars 2010

Alors voilà une erreur qui m'aura pris pas mal de temps avant de comprendre ce qui se passe.

J'ai un projet sous Eclipse qui utilise JDBC pour se connecter à une base Oracle.
Jusque là tout va bien, le projet compile sans problème mais lorsque je l'exécute j'obtient une exception

java.lang.NoSuchMethod

juste sur la dernière ligne:

OracleDataSource ods = new OracleDataSource();

// Sets the user name
ods.setUser(properties.getProperty("oracle.username"));

// Sets the password
ods.setPassword(properties.getProperty("oracle.password"));

// Sets the connection URL
ods.setURL(properties.getProperty("oracle.url"));

// Enable Caching
connectionPoolDS.setConnectionCachingEnabled(true);


Pourtant la méthode existe bien puisque que mon projet compile.

Et oui mais l'erreur venait du classpath qui contenait en fait 2 jars "classes12.jar" différents et le premier jar sur le classpath était en fait le jar d'une ancienne version d'oracle qui ne contient pas la méthode setConnectionCachingEnabled().

Cette méthode est en fait disponible à partir d'Oracle 10g.

Un petit nettoyage du classpath s'imposait donc. Il peut s'effectuer soit dans "Configure Build Path ..." disponible à partir d'un clic droit sur le projet Eclipse, soit directement en modifiant le fichier .classpath du projet.

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 24 Février 2010

Pour obtenir la liste des package Oracle, il faut utiliser la commande SQL suivante (sous SQL*Plus par exemple):

SQL> select * from user_objects where object_type='PACKAGE';

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0

Publié le 2 Février 2010

Il est aussi possible d'exécuter un fichier SQL directement depuis la ligne de commande de sqlplus.

SQL> start mes_requetes.sql;

Et voilà c'est plus facile que de taper toutes les lignes du fichiers SQL à chaque fois.

Voir les commentaires

Rédigé par Bliz

Publié dans #Oracle

Repost 0