In this article, I'll show you how you can update data from table to another with select statement.
The target table.  --2--Do the update now with the select.  --3-- See the result .  |
Wednesday, 23 November 2016
Update with select in Oracle
Friday, 13 July 2012
Import export d'un CLOB
1- Export d'un CLOB
le présent article vous montre une méthode simple pour l'export du contenu d'un type CLOB sur un système de fichier.
Premiérement on doit crée un repértoire(directory) qui pointera sur le clob à exporter.
CREATE OR REPLACE DIRECTORY fichier AS 'C:\Images\';
SQL> Directory created.
|
On va lire le contenu du CLOB pour l'écrire(Enregistrer) dans le répertoire crée précédemment.
SQL> SET SERVEROUTPUT ON
SQL>DECLARE
2 l_file UTL_FILE.FILE_TYPE;
3 l_clob CLOB;
4 l_buffer VARCHAR2(32767);
5 l_amount BINARY_INTEGER := 32767;
6 l_pos INTEGER := 1;
6 BEGIN
7 SELECT col1
8 INTO l_clob
9 FROM tab1
10 WHERE rownum = 1;
11 l_file := UTL_FILE.fopen('FICHIER', 'Test01.txt', 'w', 32767);
12 LOOP
13 DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
14 UTL_FILE.put(l_file, l_buffer);
15 l_pos := l_pos + l_amount;
16 END LOOP;
17 EXCEPTION
18 WHEN NO_DATA_FOUND THEN
19 UTL_FILE.fclose(l_file);
20 WHEN OTHERS THEN
21 UTL_FILE.fclose(l_file);
22 RAISE;
23 END;
/
PL/SQL procedure successfully completed.
|
Remarque: l’arrêt du processus est fait par le biais de l'exception NO_DATA_FOUND.D'autre exception pouvent causer l'arrêt
de l'écriture dans le répertoire, pour cela vous pouvez vous referez au package:UTL_FILE pour gérer d'autre exception
2- Import d'un CLOB
On va utiliser le répertoire crée précédent pour faire enregistrer le fichier dans le CLOB.On va crèer la table qui va servir comme sauvgarde de notre fichier text.
SQL>CREATE TABLE tab1 (
id_file NUMBER,
clob_data CLOB
);
Table created
|
On va importer notre fichier text et l'insérer dans la table par le processu suivant:
SQL>DECLARE
2 l_bfile BFILE;
3 l_clob CLOB;
4 BEGIN
5 INSERT INTO tab1 (id_file, clob_date)
6 VALUES (1, empty_clob())
7 RETURN clob_data INTO l_clob;
8 l_bfile := BFILENAME('FICHIER', 'Test01.txt');
9 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
10 DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
11 DBMS_LOB.fileclose(l_bfile);
12 COMMIT;
13 END;
/
PL/SQL procedure successfully completed.
|
Wednesday, 11 July 2012
Cryptage de mot de passe (Storing Passwords in an Oracle Database)
Quand on parle de gestion de la sécurité des applications,il y'a souvent un besoin de stocker des mots de passe
dans une table de base de données. Ceci peut en soi mener aux questions de sécurité, puisque des utilisateurs
avec des privilèges appropriés peuvent lire le contenu des ces tables . Une approche de sécurité consiste en
le cryptage des mots de passe avant leurs stockages,mais un mécanisme de décryptage pourrait vous exposer a une
faille de sécurité. Une alternative plus sûr est de stocker le hash code du nom utilisateur et de son mot de passe
comme mot passe de sécurité.Dans cette article, je vous montre un processus utilisant le package DBMS_OBFUSCATION_TOOLKIT
qui est disponible sur Oracle9i ou bien en sha_1 avec Oracle 10
Premiérement on va crée une table pour sauvgarder le nom utilisateur et sont mot de passe.
Example
> SQLPLUS scott/tiger Oracle Database 10g Release 10.2.0.1.0 - Production SQL> SET LINESIZE 130 SQL> CREATE TABLE demo_users ( id_user NUMBER(12) NOT NULL, username VARCHAR2(128) NOT NULL, password VARCHAR2(128) NOT NULL ); SQL> Table created. SQL>ALTER TABLE demo_users ADD ( CONSTRAINT id_users_pk PRIMARY KEY (id_user) ); SQL> Table altered. SQL>ALTER TABLE demo_users ADD ( CONSTRAINT users_name_uk UNIQUE (username) ); SQL> Table altered. SQL>CREATE SEQUENCE demo_users_seq; SQL> sequence created. --Création du package pour la sécurisation des informations des utilisateur SQL> CREATE OR REPLACE PACKAGE demo_user_security AS FUNCTION GET_HASH (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN VARCHAR2; PROCEDURE add_user (p_username IN VARCHAR2, p_password IN VARCHAR2); PROCEDURE change_password (p_username IN VARCHAR2, p_old_password IN VARCHAR2, p_new_password IN VARCHAR2); PROCEDURE valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2); FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN; END; SQL> package created. SQL>CREATE OR REPLACE PACKAGE BODY demo_user_security AS FUNCTION GET_HASH (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN VARCHAR2 AS v_secur VARCHAR2(30) := 'Test'; BEGIN -- Pre Oracle 10g RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => UPPER(p_username) || v_secur || UPPER(p_password)); -- Oracle 10g+ : Require EXECUTE on DBMS_CRYPTO --RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) --|| v_secur || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1); END; PROCEDURE add_user (p_username IN VARCHAR2, p_password IN VARCHAR2) AS BEGIN INSERT INTO demo_users ( id_user, username, password ) VALUES ( demo_users_seq.NEXTVAL, UPPER(p_username), GET_HASH(p_username, p_password) ); COMMIT; END; PROCEDURE change_password (p_username IN VARCHAR2, p_old_password IN VARCHAR2, p_new_password IN VARCHAR2) AS v_rowid ROWID; BEGIN SELECT rowid INTO v_rowid FROM demo_users WHERE username = UPPER(p_username) AND password = get_hash(p_username, p_old_password) FOR UPDATE; UPDATE demo_users SET password = get_hash(p_username, p_new_password) WHERE rowid = v_rowid; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, 'nom utilisateur/mot de passe incorrect.'); END; PROCEDURE valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) AS v_numy VARCHAR2(1); BEGIN SELECT '1' INTO v_numy FROM demo_users WHERE username = UPPER(p_username) AND password = get_hash(p_username, p_password); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000, 'nom utilisateur/mot de passe incorrect.'); END; FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN AS BEGIN valid_user(p_username, p_password); RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; END; / SQL> package body created. |
La surcharge de VALID_USER permet un contrôle de sécurité de façon différente.La fonction de GET_HASH est
utilisée pour hacher la combinaison du nom d'utilisateur et du mot de passe. Il rend toujours un VARCHAR2
indépendamment de la longueur des paramètres de saisie.DBMS_OBFUSCATION_TOOLKIT.MD5 permet de vous générer un
hash code en MD5.
--Exemple:
--Création des utilisateurs
SQL> exec demo_user_security.add_user('Smith','secret');
PL/SQL procedure successfully completed.
SQL> exec demo_user_security.add_user('William','terces121');
PL/SQL procedure successfully completed.
SQL> select * from demo_users;
ID USERNAME PASSWORD
----------- -----------------------------------------------
1 Smith ã XFõˆC„®W3–E
2 William b¾õmûMÀ *愶}ˆ
--Ensuite on va vérifier la procédure VALID_USER
SQL> EXEC demo_user_security.valid_user('Smith','secret');
PL/SQL procedure successfully completed.
SQL> EXEC demo_user_security.valid_user('William','bblsld');
BEGIN app_user_security.valid_user('william','bblsld'); END;
*
ERROR at line 1:
ORA-20000: nom utilisateur/mot de passe incorrect.
ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 66
ORA-06512: at line 1
--Ensuite on vérifiéra la fonction VALID_USER.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF demo_user_security.valid_user('Smith','secret') THEN
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
TRUE
PL/SQL procedure successfully completed.
SQL> BEGIN
2 IF demo_user_security.valid_user('William','bblsld') THEN
3 DBMS_OUTPUT.PUT_LINE('TRUE');
4 ELSE
5 DBMS_OUTPUT.PUT_LINE('FALSE');
6 END IF;
7 END;
8 /
FALSE
PL/SQL procedure successfully completed.
SQL>
--Au final on vérifiéra la procédure CHANGE_PASSWORD.
SQL> exec demo_user_security.change_password('Smith','secret','tresect');
PL/SQL procedure successfully completed.
SQL> exec demo_user_security.change_password('William','vfrdtg','vcftg12');
BEGIN app_user_security.change_password('William','vfrdtg','vfrd14g'); END;
*
ERROR at line 1:
ORA-20000: nom utilisateur/mot de passe incorrect.
ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 52
ORA-06512: at line 1
|
Friday, 27 April 2012
CLOB Oracle
cette procédure vous permettra de parser un contenu clob de type script SQL
Ensuite, elle va éxécuter le contenu SQL, par le bias du
package DBMS_SQL. On peut aussi subsitutuer le contenu du clob en un
ensemble d'instruction et utilisé : EXECUTE IMMEDIATE.
dans cette exemple, je vous montre la première option.
declare v_sql CLOB; v_num NUMBER := 0; v_upperbound NUMBER; v_sql DBMS_SQL.VARCHAR2S; v_cur INTEGER; v_ret NUMBER; begin -- Build a very large SQL statement in the CLOB LOOP IF v_num = 0 THEN v_sql := 'CREATE VIEW vw_tmp AS SELECT ''le nombre de ligne est : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL'; ELSE v_sql := v_sql || ' UNION ALL SELECT ''Le nombre de ligne est : '||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL'; END IF; v_num := v_num + 1; EXIT WHEN DBMS_LOB.GETLENGTH(v_sql) > 40000 OR v_num > 800; END LOOP; DBMS_OUTPUT.PUT_LINE('Length:'||DBMS_LOB.GETLENGTH(v_sql)); DBMS_OUTPUT.PUT_LINE('Num:'||v_num); -- décomposer le clob en bloc de 256 caractères et les mètres dans un tableau VARCHAR2S v_upperbound := CEIL(DBMS_LOB.GETLENGTH(v_sql)/256); FOR i IN 1..v_upperbound LOOP v_sql(i) := DBMS_LOB.SUBSTR(v_sql,256 -- amount ,((i-1)*256)+1 -- offset ); END LOOP; -- --Parsé puis éxécuter le script sql v_cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE); v_ret := DBMS_SQL.EXECUTE(v_cur); DBMS_OUTPUT.PUT_LINE('View Created'); end; |
Sunday, 15 January 2012
Merge sur Oracle
Merge help you to update and insert a new record with the same instruction.
the following present the syntax of it.
MERGE INTO Table1 T1
USING (SELECT Id, fields FROM Table2) T2
ON ( T1.Id = T2.Id ) -- Matching condition
WHEN MATCHED THEN -- true
UPDATE SET T1.fields = T2.fields --many fields with separator (,)
WHEN NOT MATCHED THEN -- false
INSERT (T1.ID, T1.fields) VALUES ( T2.ID, T2.fields);
Example
>SQLPLUS scott/tiger Oracle Database 10g Release 10.2.0.1.0 - Production SQL> SET LINESIZE 130 SQL> CREATE Table product ( 2 Id Number (10), 3 Ref VARCHAR2 (16), 4 Price NUMBER (12,2)); SQL> Table created SQL>CREATE SEQUENCE Seq_Id_Pro START WITH 1 INCREMENT BY 1; SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '001', 5.50); SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '002', 3.5); SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '004', 5.99); SQL>COMMIT; --the second table SQL> CREATE Table Temp_Product ( 2 Ref VARCHAR2 (16), 3 Price NUMBER (12,2)); SQL> Table created SQL>INSERT INTO Temp_Product VALUES ('001', 6.99); SQL>INSERT INTO Temp_Product VALUES ('002',2.5); SQL>INSERT INTO Temp_Product VALUES ('003', 2.99); SQL>INSERT INTO Temp_Product VALUES ('004', 5.5); SQL>INSERT INTO Temp_Product VALUES ('005', 4.9); SQL>COMMIT; SQL> MERGE INTO Product P 2 USING (SELECT Ref, price FROM Temp_Product) T 3 ON (P.Ref = T.Ref) 4 WHEN MATCHED THEN 5 UPDATE SET P.Price = T.Price -- you can other fields 6 WHEN NOT MATCHED THEN 7 INSERT (P.Id, P.Ref, P.Price) VALUES (Seq_Id_Pro.NextVal, T.Ref, T.Price); SQL>5 records matched. --when you do select on table product you will find update of the price and a new records inserted. ; SQL>select * from product; ID REF PRICE ---------- ---------------- ---------- 1 001 6,99 2 004 5,5 3 002 2,5 7 003 2,99 8 005 4,9 --know we have upadate and new records at the same times. |
Sunday, 27 November 2011
Gestion Utilisateur sous Oracle
1 - Profil et utilisateur.
Afin d'augmenter la sécurité de la base de données il peut être très interessant
de mettre en place une gestion des mots de passe comme le nombre maximal de tentatives
de connexion à la base, le temps de vérouillage d'une compte, etc...
Il peut parfois aussi être intéressant de limiter les ressources système
allouées à un utilisateur afin d'éviter une surcharge inutile du serveur.
dans cette exemple, je vous montre la création des utilisateur,role sous Oracle.
1:Créer deux utilisateurs; Create user yani identified by saw ; Create user sami identified by tiger ; 2:Consulter le dictionnaire pour visualiser ces users; select username, account_status from dba_users where username in('YANI','SAMI'); 3:Accorder le privelege connect au premier user; Grant connect to yani; 4: Consulter le dictionnaire de YANI desc user_objects; select object_name,object_type from user_objects; Lancer un script; create table avion ( id number(5), nom varchar2(25)); e table avion R à la ligne 1 : 1031: privilèges insuffisants --On n'a pas attribuer le privelge ressource 5: Les rôles CONNECT et RESOURCE avec le droit d’accorder ses privilèges 2éme user; Grant connect, resource to sami; 6: Quel profil a été accordé à ces utilisateurs: select * from dba_profiles where profile='DEFAULT'; 7: profile utilisateurs desc dba_users select profile from dba_users where username in ('YANI','SAMI'); 11: Autoriser le premier utilisateur à créer des tables -accorder son privilège au deuxième utilisateur GRANT CREATE ANY TABLE TO YANI WITH ADMIN OPTION; Grant connect, resource to yani; 12 Retirer le droit de create de table REVOKE CREATE ANY TABLE FROM YANI; Creation de rôle create role gestion; 2-associer des privelge a ce role grant connect, create any table, create view to gestion; Afficher les roles sys 1 select * from dba_sys_privs 2* where grantee='GESTION' 3 ; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- GESTION CREATE VIEW NO GESTION CREATE ANY TABLE NO --les resources select * from dba_sys_privs where grantee='RESOURCE'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO -limit les utilisateurs sur les profils; -Modification du profil par défaut -limiter le temps de connexion d'une session non active à 60 mn ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60; -création d'un profil pour la gestion des connexions -limiter à 5 essais la tentative de connexion -le nombre de changements de mots de passe avant de pouvoir réutiliser -un mot de passe qui a déjà été employé -Nombre de jours qui doivent s'écouler avant qu'un mot de passe puisse -être réutilisé 1--Profile 01 CREATE PROFILE connexion LIMIT FAILED_LOGIN_ATTEMPS 4 PASSWORD_REUSE_MAX 3 PASSWORD_REUSE TIME UNLIMITED; 2-- Profile 02 CREATE PROFILE connexion01 LIMIT SESSIONS_PER_USER 3 --- Accorder De 03 tentatives Pour Utilisateurs CPU_PER_SESSION DEFAULT --- CPU Par User CPU_PER_CALL DEFAULT CONNECT_TIME DEFAULT IDLE_TIME 60 --- 60 Jours LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT COMPOSITE_LIMIT DEFAULT PRIVATE_SGA DEFAULT ; --Creation de fonction CREATE OR REPLACE FUNCTION complexite_pwd (username VARCHAR2,password VARCHAR2,old_password VARCHAR2) RETURN boolean IS BEGIN IF (length(password)<06) THEN raise_application_error(-20009, 'ERROR: Le mot de passe doit être supérieur à 06 caractére'); END IF; return True; END; / Activer cette fonction avec modification commande. ALTER PROFILE connexion01 LIMIT PASSWORD_VERIFY_FUNCTION complexite_pwd; VERIFIER Create user sami1 identified by tiger01 PROFILE connexion01 PASSWORD EXPIRE ; Grant connect,resource to sami1 3-- Profile 03 create profile prof_connexion limit sessions_per_user cpu_per_session 10000 : centiéme de seconde cpu_per_call 1 : centiéme de seconde connect_time unlimited : minutes idle_time 30 : minutes logical_reads_per_session default : db blocks logical_reads_per_call default : db blocks private_sga 20M failed_login_attempts 3 :Nombre d'erreurs permises à la saisie du mot de passe avant que le compte soit verrouillé password_life_time 60 : duréé de vie mot passe puis changé (jours) password_reuse_time 12 : peut pas réutiliser le mot de passe déja utiliser avant 12 jours password_reuse_max unlimited :Nombre de changement de mots de passe requis avant de pouvoir ré-utiliser un mot de passe déjà utilisé password_lock_time default :Durée (en jours) pendant laquelle un compte sera verrouillé après qu'il ait atteint le nombre d'erreurs permises à la saisie de son mot de passe (FAILED_LOGIN_ATTEMPTS),days password_grace_time 2 : En cas de péremption d'un mot de passe dû à un délai fixé par l'administrateur cette option permet de paramétrer une durée (en jours) pendant laquelle l'utilisateur pourra tout de même se connceter, mais recevra un avertissementdays password_verify_function null ; --permet de préciser une fonction (PL/SQL) vérifiant la compexité du mot de passe. Exemple de fonction CREATE OR REPLACE FUNCTION restrict_pwd_change (username VARCHAR2, password VARCHAR2, old_password VARCHAR2) RETURN boolean IS BEGIN raise_application_error(-20009, 'ERROR: Modification du mot de passe impossible'); END; / 4--Consulter les profils SELECT profile, resource_name, limit FROM Dba_Profiles WHERE resource_type = 'PASSWORD' ORDER BY profile; Activer cette fonction par la commande suivante : ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION restrict_pwd_change; --Pour désactiver cette restriction, utilisez cette commande : ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION null; --associer le profile au utilisateurs ALTER USER Yani PROFILE connexion01; |
|
Monday, 3 October 2011
SQL Loader Oracle
Chargement Multiple de données délimitées avec SQL Loader.
Nous allons voir ici avec sqlldr comment, à partir d'un fichier de données unique
on importe dans plusieurs tables Oracle avec plusieurs clause INTO TABLE.
1 - Chargement SQLLDR dans 2 Tables.
Dans l'exemple de chargement ci-dessous, nous n'utiliserons pas d'Input Data File.
nous allons mettre les données à charger directement dans le Fichier de contrôle après la Clause
BEGINDATA pour une meilleure visibilité de l'exemple.
Structure des 2 tables cibles.
CREATE TABLE EMP ( EMPNO NUMBER(4) NULL, ENAME VARCHAR2(10 BYTE) NULL, JOB VARCHAR2(9 BYTE) NULL, MGR NUMBER(4) NULL, HIREDATE DATE NULL, SAL NUMBER(7,2) NULL, COMM NUMBER(7,2) NULL, DEPTNO NUMBER(2) NULL ) TABLESPACE USERS; |
CREATE TABLE BONUS
(
ENAME VARCHAR2(10 BYTE) NULL,
JOB VARCHAR2(10 BYTE) NULL,
SAL NUMBER NULL,
COMM NUMBER NULL
)
TABLESPACE USERS;
|
Structure du Control File SQLLDR ( INTO MULTIPLE TABLE ).
OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE *
BADFILE 'test-ora.bad'
DISCARDFILE 'test-ora.dsc'
TRUNCATE
INTO TABLE EMP
FIELDS terminated by ";" Optionally enclosed by '"'
(
empno INTEGER EXTERNAL,
ename CHAR "UPPER(:ename)",
job CHAR "RTRIM(:job)",
mgr INTEGER EXTERNAL NULLIF (mgr="NULL"),
hiredate DATE "MM/DD/YYYY HH24:MI:SS",
sal DECIMAL EXTERNAL,
comm DECIMAL EXTERNAL NULLIF (comm="NULL"),
deptno INTEGER EXTERNAL OPTIONALLY ENCLOSED BY "'"
)
INTO TABLE BONUS
FIELDS terminated by ";" Optionally enclosed by '"'
(
empno FILLER POSITION(1),
ename CHAR "UPPER(:ename)",
job CHAR "RTRIM(:job)",
mgr FILLER ,
hiredate FILLER ,
sal DECIMAL EXTERNAL,
comm DECIMAL EXTERNAL NULLIF (comm="NULL"),
deptno FILLER
)
BEGINDATA
7369;smith;CLERK ;7902;;800,50;;30
7499;"Allen";"SALESMAN ";NULL;"02/20/1981 00:00:00";1600;300;'30'
7521;"WARD";"SALESMAN";7698;"02/22/1981 00:00:00";1250;500,56;30
|
- Nous avons deux clauses INTO TABLE.
- L'option TRUNCATE est placée en haut par défaut.
L'option s'applique pour les deux tables. On peut définir deux options differentes,
dans ce cas on place l'option TRUNCATE juste après la clause INTO TABLE. -
INTO TABLE EMP TRUNCATE ... INTO TABLE BONUS TRUNCATE - Dans la deuxièmes clauses INTO TABLE, nous désactivons les champs
qui ne correspondent pas à notre structure de la table Bonus avec le type FILLER. - Vous remarquerez le mot clé POSITION avec la valeur 1 sur la première colonne.
Ceci est obligatoire, pour réinitialiser le pointeur dans SQLLOADER.
Ici la valeur est 1 car nous voulons qu'il commence la lecture à partir du début de la ligne.
Si vous omettez ce mot clé, la deuxième table ne sera pas mise à jour.
Chargement avec la commande SQLLDR.
ici le fichier de contrôle test-ora.ctl est dans le dossier D:\SQLLOADER
C:\SQLLOADER>SQLLDR scott/tiger@orcl CONTROL=D:\test-ora.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Vendredi. Septembre 30 19:36:17 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Chargement terminé - calcul enregistrement(s) logique(s) 5. C:\SQLLOADER> |
C:\SQLLOADER>SQLPLUS scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Vendredi. Septembre 30 19:42:17 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connecté à : Oracle Database 10g Release 10.2.0.1.0 - Production SQL> SET LINESIZE 130 SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 800,5 30 7499 ALLEN SALESMAN 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500,56 30 SQL> SELECT * FROM BONUS; ENAME JOB SAL COMM ---------- ---------- ---------- ---------- SMITH CLERK 800,5 ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500,56 SQL> |
Vérification du fichier LOG
C:\SQLLOADER>TYPE test-ora.log SQL*Loader: Release 10.2.0.1.0 - Production on Vendredi. Septembre 30 19:45:17 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Fichier de contrôle : test-ora.ctl Fichier de données : test-ora.ctl Fichier BAD : test-ora.bad Fichier DISCARD : test-ora.dsc (Allouer tous les rebuts) Nombre à charger : ALL Nombre à sauter: 0 Erreurs permises: 50 Continuation : aucune spécification Chemin utilisé: Direct Table EMP, chargé à partir de chaque enregistrement physique. Option d'insertion en vigueur pour cette table : TRUNCATE .... ... .. Table BONUS, chargé à partir de chaque enregistrement physique. Option d'insertion en vigueur pour cette table : TRUNCATE ..... ... .. Table EMP : Chargement réussi de 5 Lignes. 0 Lignes chargement impossible dû à des erreurs de données. 0 Lignes chargement impossible car échec de toutes les clauses WHEN. 0 Lignes chargement impossible car tous les champs étaient non renseignés. Table SCOTT."BONUS" : Chargement réussi de 5 Lignes. 0 Lignes chargement impossible dû à des erreurs de données. 0 Lignes chargement impossible car échec de toutes les clauses WHEN. 0 Lignes chargement impossible car tous les champs étaient non renseignés. Nombre total d'enregistrements logiques ignorés : 0 Nombre total d'enregistrements logiques lus : 5 Nombre total d'enregistrements logiques rejetés : 0 Nombre total d'enregistrements logiques mis au rebut : 0 C:\SQLLOADER> |
Saturday, 1 October 2011
Installation Appex 4.1
Installation Apex 4.1 sous window 7
1- Técharger Apex 4.1 sur ce lien: Apex 4.1 2- Démpresser le dossier dans un dossier (exemple :D:\LOGICIEL\apex_4.1\apex) 3- Sous Dos: D:\LOGICIEL\apex_4.1\apex 3-1- Placer vous sous le repertoire ou vous avez mis le dossier apex 3-2- Connectez sous sqlplus comme SYS. Éxécutez apepxins pour commencer l'installation D:\> sqlplus sys as sydba password: ******** sql> @appexins sysaux sysaux temp /i/ //création du reférentiel pour le dévloppement de nos application L'installation va commencer vous allez voir le diffellement de creation de table utilisateur, beaucoup d'objet oracle ca prend 20 à 25 min vous devez avoir a la fin les lignes suivantes en cas de bonne installation , et la déconexion automatique de la base de données ...6 types ...0 type bodies ...0 operators ...0 index types ...Begin key object existence check 17:30:34 ...Completed key object existence check 17:30:34 ...Setting DBMS Registry 17:30:34 ...Setting DBMS Registry Complete 17:30:34 ...Exiting validate 17:30:34 timing for: Validate Installation Elapsed: 00:04:54.06 timing for: Development Installation Elapsed: 00:18:14.34 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr oduction With the Partitioning, OLAP and Data Mining options Discounected from Oracle Database 10g Entreprise Edition D:\LOGICIEL\apex_4.1\apex> ʸecution de apxldimg.sql 4- Reconecter sur sqlplus en tant que administrateur, on va éxécuter le script(apxldmg.sql) D:\LOGICIEL\apex_4.1\apex>sqlplus sys as sysdba SQL*Plus: Release 10.1.0.4.2 - Production on Sat Oct 1 17:41:58 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> @apxldimg D:\LOGICIEL\apex_4.1 PL/SQL procedure successfully completed. old 1: create directory APEX_IMAGES as '&1/apex/images' new 1: create directory APEX_IMAGES as 'D:\LOGICIEL\apex_4.1/apex/images' Directory created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete. timing for: Load Images Elapsed: 00:01:23.81 Directory dropped. SQL> -5- Execution du script de configuration(apxconf.sql) pour terminer notre installation definir un mot de passe pour l''administrateur user changer le port par défaut, si ce dérnier et pris par un autre programme SQL> @apxconf PORT ---------- 8080 Enter values below for the XDB HTTP listener port and the password for the Appli cation Express ADMIN user. Default values are in brackets [ ]. Press Enter to accept the default value. Enter a password for the ADMIN user [] Enter a port for the XDB HTTP listener [ 8080] 8090 ...changing HTTP Port PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. ...changing password for ADMIN PL/SQL procedure successfully completed. Commit complete. SQL> 6-Ouvrez votre Browser http://localhost:port/apex/apex_admin localhost: 8080/apex/apex_admin changer le mot de passe administrateur Reconnecter vous avez l''interface APEX. Commencer à Travailler |
Monday, 5 September 2011
Tablespace Oracle
Définition: Un tablespace est un espace logique qui contient les objets stockés dans la base de données comme les tables ou les indexes. Un tablespace est composé d'au moins un datafile, c'est à dire un fichier de données qui est physiquement présent sur le serveur à l'endroit stipulé lors de sa création. Chaque datafile est constitué de segments d'au moins un extent (ou page) lui-même constitué d'au moins 3 blocs : l'élément le plus petit d'une base de données. Exemple de création de table space SQL>CREATE TABLESPACE tabledata DATAFILE 'C:\oracle\oradata\oradba\tabledata.ora' SIZE 75M DEFAULT storage (INITIAL 100k NEXT 100k minextents 1 MAXEXTENTS unlimited pctincrease 0); Remarque : changer le chemin selon votre installation de la base de données dans mon cas ('C:\oracle\oradata\oradba'). SQL> CREATE TABLESPACE tableindex DATAFILE 'C:\oracle\oradata\oradba\tableindex.ora' size 100M default storage (INITIAL 100k NEXT 100k minextents 1 MAXEXTENTS unlimited pctincrease 0); SQL>Select * from dba_tablespace_usage_metrics order by used_percent desc; TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT ------------------------------ ---------------------- ---------------------- ------------- SYSTEM 91048 2338840 3.892 SYSAUX 84656 2306840 3.66 TABLEINDX 128 12800 1 TABLEDATA01 128 19200 0.667 EXAMPLE 9960 2228760 0.446 UNDOTBS1 1968 2221028 0.088 USERS 968 2217080 0.043 TEMP 0 2216548 0 8 rows selected Pour Modifier la taille du table space SQL>ALTER DATABASE datafile 'C:\oracle\oradata\oradba\tabledata.ora' resize 150M; Pour modifier le tablespace system SQL>ALTER TABLESPACE SYSTEM add datafile 'C:\oracle\oradata\oradba\tableadd.ora' size 140M; Définition d'un tablespace temporaire Un tablespace temporaire est un tablespace spécifique aux opérations de tri pour lesquelles la SORT_AREA_SIZE ne serait pas suffisamment grande. Ce tablespace n'est pas destiné à accueillir des objets de la base de données et son usage est réservé au système. Pour créer un table space Temporaire, utilisez le mot clé Tempfile au lieu du mot Datafile SQL>CREATE TEMPORARY TABLESPACE tabletemp TEMPFILE 'C:\oracle\oradata\oradba\tabletempuser.ora' size 10M ; Création d'un utilisateur et attacher à un tablespace. Attribuer un nom utilisateur et votre mot de passe. SQL>CREATE USER NOM_USER identified by MOTPASSE DEFAULT TABLESPACE tabledata TEMPORARY TABLESPACE tabletemp; |
Friday, 29 July 2011
Oracle forms10g
Gestion d'un écran de connexion forms
utilisation du déclencheur When-Button-pressed pour acceder par un formulaire oracle forms10g
DECLARE
userid compte.identifiant%type;
pwd compte.motpasse%type;
alert number;
CURSOR C_connect IS
SELECT identifiant, motpasse FROM compte;
BEGIN
OPEN C_connect ;
FETCH C_connect INTO userid,pwd;
IF((userid=:compte.identifiant) and (pwd=:compte.motpasse)) THEN
-- aller sur la fenetre d'acceuil de l' application
BELL;
CLEAR_FORM(NO_COMMIT, FULL_ROLLBACK);
NEW_FORM('ACCEUIL.fmx',FULL_ROLLBACK, NO_QUERY_ONLY ,SHARE_LIBRARY_DATA,'default');
ELSE
set_alert_property('Message',alert_message_text,'Erreur de conncexion:
verifier votre idtentifiant et mot de passe');
alert := show_alert('Message');
go_item('nom');
:control.nbre:=:control.nbre+1;
END IF;
CLOSE C_connect;
IF (:control.nbre)>=3 Then
set_alert_property('Message',alert_message_text,'Erreur de conncexion:
nombre de tentatavie atteint');
alert := show_alert('Message');
exit_form(No_COMMIT,NO_ROLLBACK);
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
|
Saturday, 16 July 2011
Connexion a une base de donneé Oracle avec JAVA
Oracle JAVA: acces Base de donnee
Cette Classe vous permet de vous connecter à une base de donnée Oracle pour faire un SELECT, et si la meme chose pour les autres commande du LMD
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package jdbc1_cours09;
/**
*
* @author Hakim akkache
*/
import java.sql.*;
public class connexion {
/**
* @param args the command line arguments
*/
public static void main(String[] args)throws SQLException, ClassNotFoundException,
java.io.IOException {
{
//charger le driver
Class.forName("oracle.jdbc.OracleDriver");
Connection connexion=null;
Statement stmt=null;
try
{
connexion=DriverManager.getConnection("jdbc:oracle:thin:@Localhost:1521:BaseTets, "
+"nomUser","password");
stmt=connexion.createStatement();
ResultSet rset=stmt.executeQuery("SELECT department_name,count(*)"
+"from employees,departments "
+"where employees.department_id=departments.department_id "
+"group by department_name "
+"order by department_name ");
//parcourir le r?ltat de la requete pour affichage
while (rset.next()){
System.out.println(" le département :"+rset.getNString(1)
+" dispose de "+rset.getInt(2)+ " employes");
}
}finally {
if (stmt!=null){
stmt.close();
}
if(connexion!=null){
connexion.close(); //Fermer la connexion apres avoir terminer vos requetes.
}
}
}
}
}
le resultat de l'exécution est le suivant
le département :Administration dispose de 1 employes
le département :Executive dispose de 3 employes
le département :Finance dispose de 6 employes
le département :Human Resources dispose de 1 employes
BUILD SUCCESSFUL (total time: 3 seconds).
|
Sunday, 26 June 2011
les Packages PL/SQL
Oracle PL-SQL : Utilisation des Packages
Un package permet de stocker dans le meme objet , un ensemble de procedure, fonction, curseur, triggers afin de permettre une bonne gestion des objets d'un utilisateur. dans cette exemple, je vous montre un package avec une fonction et une procedure à l'éxecution, l'appel de la fonction ou bien de la procedure est indixé par le nom du package Nompackage.nomFonctoin.
SQL> SET SERVEROUTPUT ON SQL> CREATE OR REPLACE PACKAGE PkTeste IS TYPE Typ_rec IS RECORD (Nom employe.empnom%TYPE, Fonction employe.fonction%TYPE); -- declaration du type tableau pour les noms et fonctions TYPE TypeFonct IS TABLE OF Typ_rec INDEX BY BINARY_INTEGER; TabFonc TypeFonct; AucuneFonct EXCEPTION; --déclaration de la fonction qui retourne les fonctions des employés -- ayant un certain salaire fourni en paramétre FUNCTION FonctionEmp (Salaire NUMBER DEFAULT 1500) RETURN TypeFonct; PROCEDURE AffichageResFct; END PkTeste; / CREATE OR REPLACE PACKAGE BODY PkTeste IS FUNCTION FonctionEmp (Salaire NUMBER DEFAULT 1500) RETURN TypeFonct IS CURSOR CurFoncEmp IS SELECT empnom,fonction FROM employe WHERE sal=Salaire; Indice BINARY_INTEGER:=1; BEGIN FOR EnrCur in CurFoncEmp LOOP -- chargement du tableau à partir du curseur TabFonc(Indice).Nom:=EnrCur.empnom; TabFonc(Indice).Fonction:=EnrCur.fonction; Indice:=Indice+1; END LOOP; RETURN TabFonc; END FonctionEmp; PROCEDURE AffichageResFct IS BEGIN --appel de la fonction avec valeur par defaut TabFonc:=FonctionEmp; --affichage des noms et fonctions des employés IF TabFonc.COUNT=0 THEN RAISE AucuneFonct; ELSE DBMS_OUTPUT.PUT_LINE('Les employés touchant le salaire de 1500 occupent les fonctions'); DBMS_OUTPUT.PUT_LINE(' Nom '||' Fonction '); DBMS_OUTPUT.PUT_LINE('******************************'); FOR Ind IN 1 ..TabFonc.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TabFonc(Ind).Nom||' '||TabFonc(Ind).Fonction); END LOOP; END IF; --appel de la fonction avec la valeur 2500 TabFonc:=FonctionEmp(2500); --affichage des fonctions des employés IF TabFonc.COUNT=0 THEN RAISE AucuneFonct; ELSE DBMS_OUTPUT.PUT_LINE('Les employes touchant le salaire de 2500 ' ||' occupent les fonctions'); DBMS_OUTPUT.PUT_LINE(' Nom '||' Fonction '); DBMS_OUTPUT.PUT_LINE('******************************'); FOR Ind IN 1 ..TabFonc.COUNT LOOP DBMS_OUTPUT.PUT_LINE(TabFonc(Ind).Nom||' '||TabFonc(Ind).Fonction); END LOOP; END IF; EXCEPTION WHEN AucuneFonct THEN DBMS_OUTPUT.PUT_LINE('Il n''y a aucun employé qui touche ce salaire '); END AffichageResFct; END PkTeste; PL/SQL successfully completed. |
Wednesday, 8 June 2011
Oracle PL-SQL: Division par zero
Oracle PL/SQL Gestion des éxception Utilisateur avec le RAISE.
Definition d'une EXCEPTION non associee à une erreur Oracle ?
PL/SQL permet de definir ses propres EXCEPTIONS, avec la commande RAISE qui interrompt le programme et transfère au gestionnaire d'EXCEPTION.
La déclaration du nom de l'exception doit se trouver dans la partie déclarative.
Voici un exemple (avec et sans gestion des erreurs) ou PLSQL soulève :
? EXCEPTION UTILISATEUR e_dept_innexistant car le département40 n'existe pas dans la base.
On s’aperçoit que pour la même requête nous avons deux messages bien différent.
APPEL EXCEPTION UTILISATEUR avec RAISE.
SQL> SET SERVEROUTPUT ON; SQL> BEGIN 2 DELETE FROM emp WHERE deptno = 40; 3 COMMIT; 4 dbms_output.put_line('Lignes departement 40 supprimees'); 5 END; 6 / Lignes departement 40 supprimees PL/SQL procedure successfully completed. |
SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 dept_InnexistantEXCEPTION; 3 BEGIN 4 DELETE FROM emp WHERE deptno =40; 5 IF sql%NOTFOUND THEN 6 RAISE dept_Innexistant; 7 END IF; 8 COMMIT; 9 dbms_output.put_line('Lignes departement40 supprimees'); 10 11 EXCEPTION 12 WHEN dept_InnexistantTHEN 13 dbms_output.put_line('Departement40 innexistant'); 14 WHEN OTHERS THEN 15 dbms_output.put_line('Autres Erreurs'); 16 END; 17 / Departement 40 innexistant PL/SQL procedure successfully completed. |
----------------------------------------------------------------------------------- ------------------------------------------------------------------------------------
Oracle PL/SQL ERROR CURSOR ZERO_DIVIDE EXCEPTIONS.
Comment gérer l'érreur Oracle ORA-01476: divisor is equal to zero dans un bloc EXCEPTION PLSQL ?
PL/SQL dispose d'un mécanisme de gestion des érreurs qui permet de traiter ces évènements dans le BLOC EXCEPTION.
Ce curseur c_emp renvoit des enregistrements dont le champ v_emp.comm qui renvoit des valeurs égales à 0. Céla provoque une division par zéro.
Voici un exemple (avec et sans gestion des érreurs) ou PLSQL soulève :
? EXCEPTION prédéfinie ZERO_DIVIDE si v_emp.comm=0.
EXCEPTION prédéfinie ZERO_DIVIDE.
SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 v_eval_prime emp.comm%type; 3 v_emp emp%rowtype; 4 CURSOR c_emp IS 5 SELECT ename, job, comm, sal 6 FROM emp 7 WHERE deptno = 20; 8 BEGIN 9 OPEN c_emp; 10 LOOP 11 FETCH c_emp INTO v_emp.ename, v_emp.job, v_emp.comm, v_emp.sal; 12 EXIT WHEN c_emp%NOTFOUND; 13 v_eval_prime := v_emp.comm + (v_emp.sal/v_emp.comm); 14 dbms_output.put_line('Name = '||v_emp.ename || ' Job = ' || v_emp.job || 15 ' Nouvelle Prime = ' ||v_eval_prime); 16 END LOOP; 17 CLOSE c_emp; 18 END; 19 / Name = ALLEN Job = SALESMAN Nouvelle Prime = 305.33 Name = WARD Job = SALESMAN Nouvelle Prime = 480.5 Name = MARTIN Job = SALESMAN Nouvelle Prime = 1400.89 Name = BLAKE Job = MANAGER Nouvelle Prime = DECLARE * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 13 |
SQL> SET SERVEROUTPUT ON; SQL> DECLARE 2 v_eval_prime emp.comm%type; 3 v_emp emp%rowtype; 4 CURSOR c_emp IS 5 SELECT ename, job, comm, sal 6 FROM scott.emp 7 WHERE deptno = 30; 8 BEGIN 9 OPEN c_emp; 10 LOOP 11 FETCH c_emp INTO v_emp.ename, v_emp.job, v_emp.comm, v_emp.sal; 12 EXIT WHEN c_emp%NOTFOUND; 13 v_eval_prime := v_emp.comm + (v_emp.sal/v_emp.comm); 14 dbms_output.put_line('Name = '||v_emp.ename || ' Job = ' || v_emp.job || 15 ' Re-evaluation Prime = ' ||v_eval_prime); 16 END LOOP; 17 CLOSE c_emp; 18 EXCEPTION 19 WHEN ZERO_DIVIDE THEN 20 dbms_output.put_line(SQLERRM(SQLCODE)||' SALARIE SANS PRIME !!'); 21 WHEN OTHERS THEN 22 dbms_output.put_line('Autres Erreurs'); 23 END; 24 / Name = ALLEN Job = SALESMAN Re-evaluation Prime = 305.33 Name = WARD Job = SALESMAN Re-evaluation Prime = 480.5 Name = MARTIN Job = SALESMAN Re-evaluation Prime = 1400.89 Name = BLAKE Job = MANAGER Re-evaluation Prime = ORA-01476: divisor is equal to zero SALARIE SANS PRIME !! PL/SQL procedure successfully completed. |
Quand le programme prend en compte l’érreur dans une entrée WHEN, les instructions de cette entrée sont exécutées et le programme se termine.