Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.
ORA-06502 Cause
The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things:
- A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
- A non-numeric value is being assigned to a numeric variable.
- A value of NULL is being assigned to a variable which has a NOT NULL constraint.
Let’s take a look at the solutions for each of these causes.
The solution for this error will depend on the cause.
Let’s see an example of each of the three causes mentioned above.
Solution 1: Value Larger than Variable (Number Precision Too Large)
In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.
Let’s create this procedure which declares and then sets a variable:
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(3);
BEGIN
testNumber := 4321;
END;If we compile it, it compiles with no errors.
Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;We get an error:
Error starting at line : 8 in command - EXEC TestLargeNumber Error report - ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
The error we’ve gotten is “ORA-06502: PL/SQL: numeric or value error: number precision too large”. It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.
This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we’re setting it to a few lines later is 4 digit long (4321).
So, the value is too large for the variable.
To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).
In our example , we can change the size of the variable.
CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
testNumber NUMBER(4);
BEGIN
testNumber := 4321;
END;Procedure TESTLARGENUMBER compiled
Now, let’s run the procedure.
EXEC TestLargeNumber;PL/SQL procedure successfully completed.
The procedure runs successfully. We don’t get any output (because we didn’t code any in), but there are no errors.
Read more on the Oracle data types here.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Solution 2: Non-Numeric Value
Another way to find and resolve this error is by ensuring you’re not setting a numeric variable to a non-numeric value.
For example, take a look at this function.
CREATE OR REPLACE PROCEDURE TestNonNumeric
AS
testNumber NUMBER(4);
BEGIN
testNumber := 'Yes';
END;Procedure TESTNONNUMERIC compiled
The procedure compiles successfully. Now, let’s fun the function.
EXEC TestNonNumeric;Error starting at line : 8 in command - EXEC TestNonNumeric Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
The error we get is “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”.
This happens because our variable testNumber is set to a NUMBER, but a few lines later, we’re setting it to a string value which cannot be converted to a number
To resolve this error:
- Ensure the value coming in is a number and not a string.
- Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
- Convert your string to the ASCII code that represents the string using the ASCII function.
- Change the data type of your variable (but check that your code is getting the right value first).
The solution you use will depend on your requirements.
Solution 3: NOT NULL Variable
This error can appear if you try to set a NULL value to a NOT NULL variable.
Let’s take a look at this code here:
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
testNumber := nullValue;
END;Procedure TESTNONNULL compiled
Now, the reason we’re using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it’s probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.
Let’s run this function now.
Error starting at line : 9 in command - EXEC TestNonNull Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SYSTEM.TESTNONNULL", line 6 ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
We get the ORA-06502 error.
This error message doesn’t give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.
To be sure, we can output some text in our demo when it is null.
CREATE OR REPLACE PROCEDURE TestNonNull
AS
testNumber NUMBER(4) NOT NULL := 10;
nullValue NUMBER(4) := NULL;
BEGIN
IF (nullValue IS NULL) THEN
dbms_output.put_line('Value is null!');
ELSE
testNumber := nullValue;
END IF;
END;Now let’s call the procedure.
EXEC TestNonNull;Value is null!
The output shows the text message, indicating the value is null.
ORA-06502 character string buffer too small
This version of the error can occur if you set a character variable to a value larger than what it can hold.
When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.
For example:
DECLARE
charValue VARCHAR2(5);
BEGIN
charValue := 'ABCDEF';
END;If I compile this code, I get an error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
This happens because the variable is 5 characters long, and I’m setting it to a value which is 6 characters long.
You could also get this error when using CHAR data types.
DECLARE
charValue CHAR(5);
BEGIN
charValue := 'A';
charValue := charValue || 'B';
END;ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 5
This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.
When you try to concatenate a value of B to it, the resulting value is ‘A B’, which is 6 characters.
To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.
ORA-06502: pl/sql: numeric or value error: null index table key value
Sometimes you might get this error message with the ORA-06502 error:
ORA-06502: pl/sql: numeric or value error: null index table key value
This means that either:
- Your index variable is not getting initialized, or
- Your index variable is getting set to NULL somewhere in the code.
Check your code to see that neither of these two situations are happening.
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
You might also get this specific error message:
ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind
This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.
For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.
You may also get this error because some data types in PL/SQL have different lengths in SQL.
To resolve this, declare your variables as the same type as the SQL table:
type t_yourcol is table of yourtable.yourcol%TYPE;So, that’s how you resolve the ORA-06502 error.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
I am trying to dispatch data from from a temporary table into target tables.
The code is already written by third party developers, but never tested before they left.
I managed to rectify other errors, and now the very first line gives me the following errors :
ORA-06502: PL/SQL: numeric or value error: NULL index table key value.
DECLARE
-- in-memory cache table type
TYPE CODE_ID_MAP IS TABLE OF NUMBER(20) INDEX BY VARCHAR2(1200);
-- in-memory chache tables
magasins_map CODE_ID_MAP;
type_canal_map CODE_ID_MAP;
partenaires_map CODE_ID_MAP;
types_adresses_map CODE_ID_MAP;
types_magasins_map CODE_ID_MAP;
statut_donnees_partenaire_map CODE_ID_MAP;
-- cursor on input data
CURSOR imp_cur IS
SELECT
extern.*,
c.id AS idcarteu, -- correspond a "extern.numero_carte"
m0.id AS old_membres_id0,
m0.idrxp AS idrxp0,
c0.id AS idcarteu0,
m1.id AS old_membres_id1,
m1.idrxp AS idrxp1,
c1.id AS idcarteu1,
m2.id AS old_membres_id2,
m2.idrxp AS idrxp2,
c2.id AS idcarteu2
FROM batch_rxp2bmc extern
LEFT JOIN donnees_carteu c ON extern.numero_carte = c.numero_carte
LEFT JOIN membres m0 ON extern.membre_idrxp0 = m0.idrxp
LEFT JOIN donnees_carteu c0 ON m0.donnees_carteu_id = c0.id
LEFT JOIN membres m1 ON extern.membre_idrxp1 = m1.idrxp
LEFT JOIN donnees_carteu c1 ON m1.donnees_carteu_id = c1.id
LEFT JOIN membres m2 ON extern.membre_idrxp2 = m2.idrxp
LEFT JOIN donnees_carteu c2 ON m2.donnees_carteu_id = c2.id;
TYPE IMP_TBL_TYPE IS TABLE OF imp_cur%ROWTYPE;
imp_row imp_cur%ROWTYPE;
imp_tbl IMP_TBL_TYPE;
imp_cur_max PLS_INTEGER := 5000;
PROCEDURE log (level VARCHAR2, lino NUMBER, msg VARCHAR2) IS BEGIN
dbms_output.put_line('[' || level || '][#' || lino || '] ' || msg);
END log;
PROCEDURE update_membre (
lino NUMBER, -- numero de ligne
m imp_cur%ROWTYPE, -- donnees deversees
old_membres_id membres.id%TYPE, -- sujet de la mise-a-jour
old_carteu_id donnees_carteu.id%TYPE -- carte-u du sujet de la mise-a-jour
)
IS
new_carteu_id donnees_carteu.id%TYPE;
BEGIN
log('INFO', lino, 'mise-a-jour du membre ' || m.membre_identifiant);
-- rendre l'id variable -- imp_row "m" est en lecture seule
new_carteu_id := m.idcarteu;
-- insertion de la carte si elle n'existe pas
IF new_carteu_id IS NULL THEN
new_carteu_id := seq_donnees_carteu_id.nextval;
INSERT INTO donnees_carteu (id, numero_carte) VALUES (new_carteu_id, m.numero_carte);
END IF;
-- alerte si le client change de carte-u car l'ancienne
-- n'est pas supprime en cascade et devient orphelin.
IF new_carteu_id != old_carteu_id THEN
log('WARN', lino,
m.membre_identifiant || ' change de carte-u : ' ||
'la ' || new_carteu_id || ' remplace la ' || old_carteu_id || ' devenant orphelin'
);
END IF;
-- mise-a-jour du membre
UPDATE membres SET
idrxp = m.membre_idrxp0, -- on ecrase tjrs l'id historique
email = m.membre_email,
identifiant = m.membre_identifiant,
civilite = m.membre_civilite,
nom = m.membre_nom,
prenom = m.membre_prenom,
naissancedate = m.membre_naissance,
profession = m.membre_profession,
telfixe = m.membre_tel_fixe,
telmobile = m.membre_tel_mobile,
situationfamiliale = m.membre_situ_fam,
carteutypeporteur = m.membre_type_porteur,
carteu_porteur_principal = m.membre_porteur_principal,
donnees_carteu_id = new_carteu_id
WHERE id = old_membres_id;
-- mise-a-jour/insertion de l'adresse de domicile
MERGE INTO donnees_adresses trg
USING (
SELECT
old_membres_id AS membres_id,
types_adresses_map(m.adresse_type) AS types_adresses_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_adresses_id = src.types_adresses_id)
WHEN MATCHED THEN
UPDATE SET
adresse = m.adresse_street,
complement_adresse = m.adresse_complement,
code_postal = m.adresse_code_postal,
ville = m.adresse_ville,
pays = m.adresse_pays
WHEN NOT MATCHED THEN
INSERT (
membres_id,
types_adresses_id,
adresse,
complement_adresse,
code_postal,
ville,
pays
)
VALUES (
old_membres_id,
types_adresses_map(m.adresse_type),
m.adresse_street,
m.adresse_complement,
m.adresse_code_postal,
m.adresse_ville,
m.adresse_pays
);
-- mise-a-jour/insertion des souscriptions sms, email, courrier
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_sms_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_sms,
date_derniere_maj = m.optin_sms_date,
source_maj = m.optin_sms_source
WHERE accepte_canal != m.optin_sms -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_sms,
m.optin_sms_date,
m.optin_sms_source,
type_canal_map(m.optin_sms_canal)
);
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_email_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_email,
date_derniere_maj = m.optin_email_date,
source_maj = m.optin_email_source
WHERE accepte_canal != m.optin_email -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_email,
m.optin_email_date,
m.optin_email_source,
type_canal_map(m.optin_email_canal)
);
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_courrier_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_courrier,
date_derniere_maj = m.optin_courrier_date,
source_maj = m.optin_courrier_source
WHERE accepte_canal != m.optin_courrier -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_courrier,
m.optin_courrier_date,
m.optin_courrier_source,
type_canal_map(m.optin_courrier_canal)
);
-- remplacement des enfants (nous sommes pas en mesure de faire le lien entre
-- des enfant deja en base et ceux qui sont deverses)
DELETE membre_enfants WHERE membre_id = old_membres_id;
INSERT ALL
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_1_prenom, m.enfant_1_sexe, m.enfant_1_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_2_prenom, m.enfant_2_sexe, m.enfant_2_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_3_prenom, m.enfant_3_sexe, m.enfant_3_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_4_prenom, m.enfant_4_sexe, m.enfant_4_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_5_prenom, m.enfant_5_sexe, m.enfant_5_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_6_prenom, m.enfant_6_sexe, m.enfant_6_naissance)
SELECT * FROM dual;
-- mise-a-jour/insertion des magasins favorites
MERGE INTO membres_types_magasins trg
USING (
SELECT
old_membres_id AS membres_id,
magasins_map(m.fav_magasinsu) AS magasins_id,
types_magasins_map(m.fav_magasinsu_type) AS types_magasins_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_magasins_id = src.types_magasins_id)
WHEN MATCHED THEN
UPDATE SET
magasins_id = src.magasins_id
WHERE magasins_id != src.magasins_id
WHEN NOT MATCHED THEN
INSERT (membres_id, magasins_id, types_magasins_id)
VALUES (src.membres_id, src.magasins_id, src.types_magasins_id);
MERGE INTO membres_types_magasins trg
USING (
SELECT
old_membres_id AS membres_id,
magasins_map(m.fav_mobileapp) AS magasins_id,
types_magasins_map(m.fav_mobileapp_type) AS types_magasins_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_magasins_id = src.types_magasins_id)
WHEN MATCHED THEN
UPDATE SET
magasins_id = src.magasins_id
WHERE magasins_id != src.magasins_id
WHEN NOT MATCHED THEN
INSERT (membres_id, magasins_id, types_magasins_id)
VALUES (src.membres_id, src.magasins_id, src.types_magasins_id);
-- suppression du consentement s'il n'est pas renseigne
IF m.consentement_date IS NULL THEN
DELETE membre_consentement WHERE id_membre = old_membres_id;
-- creation du consentement s'il n'existe pas encore
ELSE
MERGE INTO membre_consentement trg
USING (
SELECT
old_membres_id AS id_membre,
m.consentement_date AS date_consentement,
partenaires_map(m.consentement_partenaire) AS id_source_consentement
FROM dual
) src
ON (trg.id_membre = src.id_membre)
WHEN NOT MATCHED THEN
INSERT (id_membre, date_consentement, id_source_consentement)
VALUES (src.id_membre, src.date_consentement, src.id_source_consentement);
END IF;
-- point de vigilence : munissez-vous d'un schema de la BMC
-- la relation entre MEMBRES et DONNEES_PARTENAIRE et de cardinalite
-- one-to-many et non many-to-many comme le sugere le tableau intermediare
-- MEMBRES_DONNEES_PARTENAIRE. une adhesion membre-service (representee
-- par DONNEES_PARTENAIRE) ne peut pas etre partage entre plusieurs membres.
-- c'est une erreur dans la conception de la bdd.
DECLARE
donnees_partenaire_id donnees_partenaire.id%TYPE;
BEGIN
SELECT dp.id INTO donnees_partenaire_id FROM donnees_partenaire dp
INNER JOIN membres_donnees_partenaire mdp
ON mdp.donnees_partenaire_id = dp.id
WHERE mdp.membres_id = old_membres_id
AND dp.partenaires_id = partenaires_map(m.adhesion_magasinsu_partenaire);
UPDATE donnees_partenaire dp
SET statut_donnees_partenaire_id = statut_donnees_partenaire_map(m.adhesion_magasinsu_statut)
WHERE id = donnees_partenaire_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
log('ERROR', lino, m.membre_identifiant || ' n''est pas associe a magasins-u');
END;
DECLARE
donnees_partenaire_id donnees_partenaire.id%TYPE;
BEGIN
SELECT dp.id INTO donnees_partenaire_id FROM donnees_partenaire dp
INNER JOIN membres_donnees_partenaire mdp
ON mdp.donnees_partenaire_id = dp.id
WHERE mdp.membres_id = old_membres_id
AND dp.partenaires_id = partenaires_map(m.adhesion_mobileapp_partenaire);
UPDATE donnees_partenaire dp
SET statut_donnees_partenaire_id = statut_donnees_partenaire_map(m.adhesion_mobileapp_statut)
WHERE id = donnees_partenaire_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_magasinsu_statut),
partenaires_map(m.adhesion_magasinsu_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
old_membres_id,
donnees_partenaire_id
);
END;
END update_membre;
PROCEDURE create_membre (
lino NUMBER, -- numero de ligne versee
m imp_cur%ROWTYPE -- donnees derversees
) AS
idcarteu donnees_carteu.id%TYPE; -- id de la carte associe au numero deverse
membres_id membres.id%TYPE; -- cle primaire du nouveau membre
donnees_partenaire_id donnees_partenaire.id%TYPE; -- cle primaire des adhesions appli du client
BEGIN
log('WARN', lino, m.membre_identifiant || ' est nouvellement cree. l''utilisateur n''a pas de mdp.');
-- insertion de la carte-u si elle n'existe pas encore
IF idcarteu IS NULL THEN
idcarteu := seq_donnees_carteu_id.nextval;
INSERT INTO donnees_carteu (id, numero_carte)
VALUES (idcarteu, m.numero_carte);
END IF;
-- cle primaire du nouveau membre est cle etrangere pour les infos associees
membres_id := seq_membres_id.nextval;
-- insertion du client lui-meme
INSERT INTO membres (
id,
idrxp,
email,
identifiant,
civilite,
nom,
prenom,
naissancedate,
profession,
telfixe,
telmobile,
situationfamiliale,
carteutypeporteur,
carteu_porteur_principal,
donnees_carteu_id
) VALUES (
membres_id,
m.membre_idrxp0,
m.membre_email,
m.membre_identifiant,
m.membre_civilite,
m.membre_nom,
m.membre_prenom,
m.membre_naissance,
m.membre_profession,
m.membre_tel_fixe,
m.membre_tel_mobile,
m.membre_situ_fam,
m.membre_type_porteur,
m.membre_porteur_principal,
idcarteu
);
-- insertion de l'adresse domicile
INSERT INTO donnees_adresses (
membres_id,
types_adresses_id,
adresse,
complement_adresse,
code_postal,
ville,
pays
) VALUES (
membres_id,
types_adresses_map(m.adresse_type),
m.adresse_street,
m.adresse_complement,
m.adresse_code_postal,
m.adresse_ville,
m.adresse_pays
);
-- insertion des souscriptions aux animations commerciales du client
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_sms,
m.optin_sms_date,
m.optin_sms_source,
type_canal_map(m.optin_sms_canal)
);
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_email,
m.optin_email_date,
m.optin_email_source,
type_canal_map(m.optin_email_canal)
);
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_courrier,
m.optin_courrier_date,
m.optin_courrier_source,
type_canal_map(m.optin_courrier_canal)
);
-- insertion des enfants
IF m.enfant_1_prenom IS NOT NULL OR
m.enfant_1_sexe IS NOT NULL OR
m.enfant_1_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_1_prenom, m.enfant_1_sexe, m.enfant_1_naissance);
END IF;
IF m.enfant_2_prenom IS NOT NULL OR
m.enfant_2_sexe IS NOT NULL OR
m.enfant_2_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_2_prenom, m.enfant_2_sexe, m.enfant_2_naissance);
END IF;
IF m.enfant_3_prenom IS NOT NULL OR
m.enfant_3_sexe IS NOT NULL OR
m.enfant_3_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_3_prenom, m.enfant_3_sexe, m.enfant_3_naissance);
END IF;
IF m.enfant_4_prenom IS NOT NULL OR
m.enfant_4_sexe IS NOT NULL OR
m.enfant_4_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_4_prenom, m.enfant_4_sexe, m.enfant_4_naissance);
END IF;
IF m.enfant_5_prenom IS NOT NULL OR
m.enfant_5_sexe IS NOT NULL OR
m.enfant_5_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_5_prenom, m.enfant_5_sexe, m.enfant_5_naissance);
END IF;
IF m.enfant_6_prenom IS NOT NULL OR
m.enfant_6_sexe IS NOT NULL OR
m.enfant_6_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_6_prenom, m.enfant_6_sexe, m.enfant_6_naissance);
END IF;
-- insertion des magasins favorites
IF m.fav_magasinsu IS NOT NULL THEN
INSERT INTO membres_types_magasins (membres_id, magasins_id, types_magasins_id)
VALUES (membres_id, magasins_map(m.fav_magasinsu), types_magasins_map(m.fav_magasinsu_type));
END IF;
IF m.fav_mobileapp IS NOT NULL THEN
INSERT INTO membres_types_magasins (membres_id, magasins_id, types_magasins_id)
VALUES (membres_id, magasins_map(m.fav_mobileapp), types_magasins_map(m.fav_mobileapp_type));
END IF;
-- insertion du consentement transfert hors UE de donnees du client
IF m.consentement_date IS NOT NULL THEN
INSERT INTO membre_consentement (id_membre, date_consentement, id_source_consentement)
VALUES (membres_id, m.consentement_date, partenaires_map(m.consentement_partenaire));
END IF;
-- insertion des inscriptions aux differentes applications
IF m.adhesion_magasinsu_statut IS NOT NULL THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_magasinsu_statut),
partenaires_map(m.adhesion_magasinsu_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
membres_id,
donnees_partenaire_id
);
END IF;
IF m.adhesion_mobileapp_statut IS NOT NULL THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_mobileapp_statut),
partenaires_map(m.adhesion_mobileapp_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
membres_id,
donnees_partenaire_id
);
END IF;
END;
PROCEDURE delete_membre (lino NUMBER, m imp_cur%ROWTYPE) IS BEGIN
log('INFO', lino, m.membre_identifiant || ' est supprime par fusion');
DELETE FROM donnees_adresses WHERE membres_id = m.old_membres_id2;
DELETE FROM membres_types_magasins WHERE membres_id = m.old_membres_id2;
DELETE FROM membre_consentement WHERE id_membre = m.old_membres_id2;
-- cette action supprime en cascade les donnees associees des tableaux suivantes
-- - donnees_personnelles
-- - mot_de_passe_token
-- - membre_enfants
-- - donnees_communication
-- - membres_donnees_partenaire
DELETE FROM membres WHERE id = m.old_membres_id2;
/* IF m.numero_carte != m.numero_carte2 THEN
log('WARN', lino,
m.membre_identifiant || ' change de carte-u: la ' ||
m.numero_carte || ' remplace la ' ||
m.numero_carte2 || ' devenant orphelin.'
);
END IF;*/
END delete_membre;
-- -----------------------------------------------------------------------------
BEGIN
dbms_output.enable(NULL); -- unlimited output
-- mettre des donnees statiques / le referentiel en cache
FOR row IN (SELECT id, code FROM magasins) LOOP magasins_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM type_canal) LOOP type_canal_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM partenaires) LOOP partenaires_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM types_adresses) LOOP types_adresses_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM types_magasins) LOOP types_magasins_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM statut_donnees_partenaire) LOOP statut_donnees_partenaire_map(row.code) := row.id; END LOOP;
-- bulk collect afin de reduire le cout associe au changement
-- de contexte entre le moteur sql et celui de scripting
OPEN imp_cur;
LOOP FETCH imp_cur BULK COLLECT INTO imp_tbl LIMIT imp_cur_max;
EXIT WHEN imp_tbl.COUNT = 0;
FOR lino IN 1..imp_tbl.COUNT LOOP
imp_row := imp_tbl(lino);
-- la centralisation de la donnee membre a pour consequence la fusion
-- des anciennes entrees dont l'arbre decisionnel se trouve ci-dessous.
IF imp_row.adhesion_magasinsu_statut IS NULL THEN
log('ERROR', lino, imp_row.membre_identifiant || ' n''est pas associe a l''application magasins-u');
-- #3
ELSIF imp_row.membre_idrxp0 IS NULL THEN
log('ERROR', lino, '1st rxp id is null');
-- #2
ELSIF imp_row.idrxp0 IS NOT NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id0, imp_row.idcarteu0);
ELSIF imp_row.idrxp0 IS NULL THEN
IF imp_row.membre_idrxp1 IS NULL THEN
-- #12
IF imp_row.membre_idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #9
ELSIF imp_row.idrxp2 IS NULL THEN
log('ERROR', lino, 'fusion d''ids RXP non-valide : 0=non-existant 1=vide 2=non-existant');
-- #6
ELSIF imp_row.idrxp2 IS NOT NULL THEN
log('ERROR', lino,
'fusion d''ids RXP non-valide : 0=non-existant 1=vide 2=existe. ' ||
'ce 3eme (idx=2) est juge obsolete et donc rejete'
);
END IF;
ELSIF imp_row.idrxp1 IS NULL THEN
-- #11
IF imp_row.membre_idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #8
ELSIF imp_row.idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #5
ELSIF imp_row.idrxp2 IS NOT NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id2, imp_row.idcarteu2);
END IF;
ELSIF imp_row.idrxp1 IS NOT NULL THEN
-- #10
IF imp_row.membre_idrxp2 IS NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
-- #7
ELSIF imp_row.idrxp2 IS NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
-- #4
ELSIF imp_row.idrxp2 IS NOT NULL THEN
delete_membre(lino, imp_row);
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
CLOSE imp_cur;
END;
Нельзя присвоить значение NULL переменной объявленной с ограничением NOT NULL.
06502, 00000, «PL/SQL: numeric or value error%s»
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
Здесь:
a varchar2(10) not null := '',
пустая строка интерпретируется как NULL. Тут подробнее, почему.
v_recx(1).a := 'BBB';
Эта строка будет скомпилирована, так как компилятор не проверяет присваиваемых значений во время компиляции. При выполнении блока, ещё до присваивания полю значения 'BBB', поля записи будут инициализированы, где и произойдёт попытка присвоить полю a с ограничением NOT NULL значения NULL.
I am trying to dispatch data from from a temporary table into target tables.
The code is already written by third party developers, but never tested before they left.
I managed to rectify other errors, and now the very first line gives me the following errors :
ORA-06502: PL/SQL: numeric or value error: NULL index table key value.
DECLARE
-- in-memory cache table type
TYPE CODE_ID_MAP IS TABLE OF NUMBER(20) INDEX BY VARCHAR2(1200);
-- in-memory chache tables
magasins_map CODE_ID_MAP;
type_canal_map CODE_ID_MAP;
partenaires_map CODE_ID_MAP;
types_adresses_map CODE_ID_MAP;
types_magasins_map CODE_ID_MAP;
statut_donnees_partenaire_map CODE_ID_MAP;
-- cursor on input data
CURSOR imp_cur IS
SELECT
extern.*,
c.id AS idcarteu, -- correspond a "extern.numero_carte"
m0.id AS old_membres_id0,
m0.idrxp AS idrxp0,
c0.id AS idcarteu0,
m1.id AS old_membres_id1,
m1.idrxp AS idrxp1,
c1.id AS idcarteu1,
m2.id AS old_membres_id2,
m2.idrxp AS idrxp2,
c2.id AS idcarteu2
FROM batch_rxp2bmc extern
LEFT JOIN donnees_carteu c ON extern.numero_carte = c.numero_carte
LEFT JOIN membres m0 ON extern.membre_idrxp0 = m0.idrxp
LEFT JOIN donnees_carteu c0 ON m0.donnees_carteu_id = c0.id
LEFT JOIN membres m1 ON extern.membre_idrxp1 = m1.idrxp
LEFT JOIN donnees_carteu c1 ON m1.donnees_carteu_id = c1.id
LEFT JOIN membres m2 ON extern.membre_idrxp2 = m2.idrxp
LEFT JOIN donnees_carteu c2 ON m2.donnees_carteu_id = c2.id;
TYPE IMP_TBL_TYPE IS TABLE OF imp_cur%ROWTYPE;
imp_row imp_cur%ROWTYPE;
imp_tbl IMP_TBL_TYPE;
imp_cur_max PLS_INTEGER := 5000;
PROCEDURE log (level VARCHAR2, lino NUMBER, msg VARCHAR2) IS BEGIN
dbms_output.put_line('[' || level || '][#' || lino || '] ' || msg);
END log;
PROCEDURE update_membre (
lino NUMBER, -- numero de ligne
m imp_cur%ROWTYPE, -- donnees deversees
old_membres_id membres.id%TYPE, -- sujet de la mise-a-jour
old_carteu_id donnees_carteu.id%TYPE -- carte-u du sujet de la mise-a-jour
)
IS
new_carteu_id donnees_carteu.id%TYPE;
BEGIN
log('INFO', lino, 'mise-a-jour du membre ' || m.membre_identifiant);
-- rendre l'id variable -- imp_row "m" est en lecture seule
new_carteu_id := m.idcarteu;
-- insertion de la carte si elle n'existe pas
IF new_carteu_id IS NULL THEN
new_carteu_id := seq_donnees_carteu_id.nextval;
INSERT INTO donnees_carteu (id, numero_carte) VALUES (new_carteu_id, m.numero_carte);
END IF;
-- alerte si le client change de carte-u car l'ancienne
-- n'est pas supprime en cascade et devient orphelin.
IF new_carteu_id != old_carteu_id THEN
log('WARN', lino,
m.membre_identifiant || ' change de carte-u : ' ||
'la ' || new_carteu_id || ' remplace la ' || old_carteu_id || ' devenant orphelin'
);
END IF;
-- mise-a-jour du membre
UPDATE membres SET
idrxp = m.membre_idrxp0, -- on ecrase tjrs l'id historique
email = m.membre_email,
identifiant = m.membre_identifiant,
civilite = m.membre_civilite,
nom = m.membre_nom,
prenom = m.membre_prenom,
naissancedate = m.membre_naissance,
profession = m.membre_profession,
telfixe = m.membre_tel_fixe,
telmobile = m.membre_tel_mobile,
situationfamiliale = m.membre_situ_fam,
carteutypeporteur = m.membre_type_porteur,
carteu_porteur_principal = m.membre_porteur_principal,
donnees_carteu_id = new_carteu_id
WHERE id = old_membres_id;
-- mise-a-jour/insertion de l'adresse de domicile
MERGE INTO donnees_adresses trg
USING (
SELECT
old_membres_id AS membres_id,
types_adresses_map(m.adresse_type) AS types_adresses_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_adresses_id = src.types_adresses_id)
WHEN MATCHED THEN
UPDATE SET
adresse = m.adresse_street,
complement_adresse = m.adresse_complement,
code_postal = m.adresse_code_postal,
ville = m.adresse_ville,
pays = m.adresse_pays
WHEN NOT MATCHED THEN
INSERT (
membres_id,
types_adresses_id,
adresse,
complement_adresse,
code_postal,
ville,
pays
)
VALUES (
old_membres_id,
types_adresses_map(m.adresse_type),
m.adresse_street,
m.adresse_complement,
m.adresse_code_postal,
m.adresse_ville,
m.adresse_pays
);
-- mise-a-jour/insertion des souscriptions sms, email, courrier
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_sms_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_sms,
date_derniere_maj = m.optin_sms_date,
source_maj = m.optin_sms_source
WHERE accepte_canal != m.optin_sms -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_sms,
m.optin_sms_date,
m.optin_sms_source,
type_canal_map(m.optin_sms_canal)
);
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_email_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_email,
date_derniere_maj = m.optin_email_date,
source_maj = m.optin_email_source
WHERE accepte_canal != m.optin_email -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_email,
m.optin_email_date,
m.optin_email_source,
type_canal_map(m.optin_email_canal)
);
MERGE INTO donnees_communication trg
USING (
SELECT
old_membres_id AS membres_id,
type_canal_map(m.optin_courrier_canal) AS canal_communication_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.canal_communication_id = src.canal_communication_id)
WHEN MATCHED THEN
UPDATE SET
accepte_canal = m.optin_courrier,
date_derniere_maj = m.optin_courrier_date,
source_maj = m.optin_courrier_source
WHERE accepte_canal != m.optin_courrier -- mise-a-jour si ca change qqc
WHEN NOT MATCHED THEN
INSERT (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
)
VALUES (
old_membres_id,
m.optin_courrier,
m.optin_courrier_date,
m.optin_courrier_source,
type_canal_map(m.optin_courrier_canal)
);
-- remplacement des enfants (nous sommes pas en mesure de faire le lien entre
-- des enfant deja en base et ceux qui sont deverses)
DELETE membre_enfants WHERE membre_id = old_membres_id;
INSERT ALL
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_1_prenom, m.enfant_1_sexe, m.enfant_1_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_2_prenom, m.enfant_2_sexe, m.enfant_2_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_3_prenom, m.enfant_3_sexe, m.enfant_3_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_4_prenom, m.enfant_4_sexe, m.enfant_4_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_5_prenom, m.enfant_5_sexe, m.enfant_5_naissance)
INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (old_membres_id, m.enfant_6_prenom, m.enfant_6_sexe, m.enfant_6_naissance)
SELECT * FROM dual;
-- mise-a-jour/insertion des magasins favorites
MERGE INTO membres_types_magasins trg
USING (
SELECT
old_membres_id AS membres_id,
magasins_map(m.fav_magasinsu) AS magasins_id,
types_magasins_map(m.fav_magasinsu_type) AS types_magasins_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_magasins_id = src.types_magasins_id)
WHEN MATCHED THEN
UPDATE SET
magasins_id = src.magasins_id
WHERE magasins_id != src.magasins_id
WHEN NOT MATCHED THEN
INSERT (membres_id, magasins_id, types_magasins_id)
VALUES (src.membres_id, src.magasins_id, src.types_magasins_id);
MERGE INTO membres_types_magasins trg
USING (
SELECT
old_membres_id AS membres_id,
magasins_map(m.fav_mobileapp) AS magasins_id,
types_magasins_map(m.fav_mobileapp_type) AS types_magasins_id
FROM dual
) src
ON (trg.membres_id = src.membres_id AND trg.types_magasins_id = src.types_magasins_id)
WHEN MATCHED THEN
UPDATE SET
magasins_id = src.magasins_id
WHERE magasins_id != src.magasins_id
WHEN NOT MATCHED THEN
INSERT (membres_id, magasins_id, types_magasins_id)
VALUES (src.membres_id, src.magasins_id, src.types_magasins_id);
-- suppression du consentement s'il n'est pas renseigne
IF m.consentement_date IS NULL THEN
DELETE membre_consentement WHERE id_membre = old_membres_id;
-- creation du consentement s'il n'existe pas encore
ELSE
MERGE INTO membre_consentement trg
USING (
SELECT
old_membres_id AS id_membre,
m.consentement_date AS date_consentement,
partenaires_map(m.consentement_partenaire) AS id_source_consentement
FROM dual
) src
ON (trg.id_membre = src.id_membre)
WHEN NOT MATCHED THEN
INSERT (id_membre, date_consentement, id_source_consentement)
VALUES (src.id_membre, src.date_consentement, src.id_source_consentement);
END IF;
-- point de vigilence : munissez-vous d'un schema de la BMC
-- la relation entre MEMBRES et DONNEES_PARTENAIRE et de cardinalite
-- one-to-many et non many-to-many comme le sugere le tableau intermediare
-- MEMBRES_DONNEES_PARTENAIRE. une adhesion membre-service (representee
-- par DONNEES_PARTENAIRE) ne peut pas etre partage entre plusieurs membres.
-- c'est une erreur dans la conception de la bdd.
DECLARE
donnees_partenaire_id donnees_partenaire.id%TYPE;
BEGIN
SELECT dp.id INTO donnees_partenaire_id FROM donnees_partenaire dp
INNER JOIN membres_donnees_partenaire mdp
ON mdp.donnees_partenaire_id = dp.id
WHERE mdp.membres_id = old_membres_id
AND dp.partenaires_id = partenaires_map(m.adhesion_magasinsu_partenaire);
UPDATE donnees_partenaire dp
SET statut_donnees_partenaire_id = statut_donnees_partenaire_map(m.adhesion_magasinsu_statut)
WHERE id = donnees_partenaire_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
log('ERROR', lino, m.membre_identifiant || ' n''est pas associe a magasins-u');
END;
DECLARE
donnees_partenaire_id donnees_partenaire.id%TYPE;
BEGIN
SELECT dp.id INTO donnees_partenaire_id FROM donnees_partenaire dp
INNER JOIN membres_donnees_partenaire mdp
ON mdp.donnees_partenaire_id = dp.id
WHERE mdp.membres_id = old_membres_id
AND dp.partenaires_id = partenaires_map(m.adhesion_mobileapp_partenaire);
UPDATE donnees_partenaire dp
SET statut_donnees_partenaire_id = statut_donnees_partenaire_map(m.adhesion_mobileapp_statut)
WHERE id = donnees_partenaire_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_magasinsu_statut),
partenaires_map(m.adhesion_magasinsu_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
old_membres_id,
donnees_partenaire_id
);
END;
END update_membre;
PROCEDURE create_membre (
lino NUMBER, -- numero de ligne versee
m imp_cur%ROWTYPE -- donnees derversees
) AS
idcarteu donnees_carteu.id%TYPE; -- id de la carte associe au numero deverse
membres_id membres.id%TYPE; -- cle primaire du nouveau membre
donnees_partenaire_id donnees_partenaire.id%TYPE; -- cle primaire des adhesions appli du client
BEGIN
log('WARN', lino, m.membre_identifiant || ' est nouvellement cree. l''utilisateur n''a pas de mdp.');
-- insertion de la carte-u si elle n'existe pas encore
IF idcarteu IS NULL THEN
idcarteu := seq_donnees_carteu_id.nextval;
INSERT INTO donnees_carteu (id, numero_carte)
VALUES (idcarteu, m.numero_carte);
END IF;
-- cle primaire du nouveau membre est cle etrangere pour les infos associees
membres_id := seq_membres_id.nextval;
-- insertion du client lui-meme
INSERT INTO membres (
id,
idrxp,
email,
identifiant,
civilite,
nom,
prenom,
naissancedate,
profession,
telfixe,
telmobile,
situationfamiliale,
carteutypeporteur,
carteu_porteur_principal,
donnees_carteu_id
) VALUES (
membres_id,
m.membre_idrxp0,
m.membre_email,
m.membre_identifiant,
m.membre_civilite,
m.membre_nom,
m.membre_prenom,
m.membre_naissance,
m.membre_profession,
m.membre_tel_fixe,
m.membre_tel_mobile,
m.membre_situ_fam,
m.membre_type_porteur,
m.membre_porteur_principal,
idcarteu
);
-- insertion de l'adresse domicile
INSERT INTO donnees_adresses (
membres_id,
types_adresses_id,
adresse,
complement_adresse,
code_postal,
ville,
pays
) VALUES (
membres_id,
types_adresses_map(m.adresse_type),
m.adresse_street,
m.adresse_complement,
m.adresse_code_postal,
m.adresse_ville,
m.adresse_pays
);
-- insertion des souscriptions aux animations commerciales du client
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_sms,
m.optin_sms_date,
m.optin_sms_source,
type_canal_map(m.optin_sms_canal)
);
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_email,
m.optin_email_date,
m.optin_email_source,
type_canal_map(m.optin_email_canal)
);
INSERT INTO donnees_communication (
membres_id,
accepte_canal,
date_derniere_maj,
source_maj,
canal_communication_id
) VALUES (
membres_id,
m.optin_courrier,
m.optin_courrier_date,
m.optin_courrier_source,
type_canal_map(m.optin_courrier_canal)
);
-- insertion des enfants
IF m.enfant_1_prenom IS NOT NULL OR
m.enfant_1_sexe IS NOT NULL OR
m.enfant_1_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_1_prenom, m.enfant_1_sexe, m.enfant_1_naissance);
END IF;
IF m.enfant_2_prenom IS NOT NULL OR
m.enfant_2_sexe IS NOT NULL OR
m.enfant_2_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_2_prenom, m.enfant_2_sexe, m.enfant_2_naissance);
END IF;
IF m.enfant_3_prenom IS NOT NULL OR
m.enfant_3_sexe IS NOT NULL OR
m.enfant_3_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_3_prenom, m.enfant_3_sexe, m.enfant_3_naissance);
END IF;
IF m.enfant_4_prenom IS NOT NULL OR
m.enfant_4_sexe IS NOT NULL OR
m.enfant_4_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_4_prenom, m.enfant_4_sexe, m.enfant_4_naissance);
END IF;
IF m.enfant_5_prenom IS NOT NULL OR
m.enfant_5_sexe IS NOT NULL OR
m.enfant_5_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_5_prenom, m.enfant_5_sexe, m.enfant_5_naissance);
END IF;
IF m.enfant_6_prenom IS NOT NULL OR
m.enfant_6_sexe IS NOT NULL OR
m.enfant_6_naissance IS NOT NULL
THEN
INSERT INTO membre_enfants (membre_id, prenom, sexe, naissancedate)
VALUES (membres_id, m.enfant_6_prenom, m.enfant_6_sexe, m.enfant_6_naissance);
END IF;
-- insertion des magasins favorites
IF m.fav_magasinsu IS NOT NULL THEN
INSERT INTO membres_types_magasins (membres_id, magasins_id, types_magasins_id)
VALUES (membres_id, magasins_map(m.fav_magasinsu), types_magasins_map(m.fav_magasinsu_type));
END IF;
IF m.fav_mobileapp IS NOT NULL THEN
INSERT INTO membres_types_magasins (membres_id, magasins_id, types_magasins_id)
VALUES (membres_id, magasins_map(m.fav_mobileapp), types_magasins_map(m.fav_mobileapp_type));
END IF;
-- insertion du consentement transfert hors UE de donnees du client
IF m.consentement_date IS NOT NULL THEN
INSERT INTO membre_consentement (id_membre, date_consentement, id_source_consentement)
VALUES (membres_id, m.consentement_date, partenaires_map(m.consentement_partenaire));
END IF;
-- insertion des inscriptions aux differentes applications
IF m.adhesion_magasinsu_statut IS NOT NULL THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_magasinsu_statut),
partenaires_map(m.adhesion_magasinsu_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
membres_id,
donnees_partenaire_id
);
END IF;
IF m.adhesion_mobileapp_statut IS NOT NULL THEN
donnees_partenaire_id := seq_donnees_part_id.nextval;
INSERT INTO donnees_partenaire (
id,
statut_donnees_partenaire_id,
partenaires_id
) VALUES (
donnees_partenaire_id,
statut_donnees_partenaire_map(m.adhesion_mobileapp_statut),
partenaires_map(m.adhesion_mobileapp_partenaire)
);
INSERT INTO membres_donnees_partenaire (
membres_id,
donnees_partenaire_id
) VALUES (
membres_id,
donnees_partenaire_id
);
END IF;
END;
PROCEDURE delete_membre (lino NUMBER, m imp_cur%ROWTYPE) IS BEGIN
log('INFO', lino, m.membre_identifiant || ' est supprime par fusion');
DELETE FROM donnees_adresses WHERE membres_id = m.old_membres_id2;
DELETE FROM membres_types_magasins WHERE membres_id = m.old_membres_id2;
DELETE FROM membre_consentement WHERE id_membre = m.old_membres_id2;
-- cette action supprime en cascade les donnees associees des tableaux suivantes
-- - donnees_personnelles
-- - mot_de_passe_token
-- - membre_enfants
-- - donnees_communication
-- - membres_donnees_partenaire
DELETE FROM membres WHERE id = m.old_membres_id2;
/* IF m.numero_carte != m.numero_carte2 THEN
log('WARN', lino,
m.membre_identifiant || ' change de carte-u: la ' ||
m.numero_carte || ' remplace la ' ||
m.numero_carte2 || ' devenant orphelin.'
);
END IF;*/
END delete_membre;
-- -----------------------------------------------------------------------------
BEGIN
dbms_output.enable(NULL); -- unlimited output
-- mettre des donnees statiques / le referentiel en cache
FOR row IN (SELECT id, code FROM magasins) LOOP magasins_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM type_canal) LOOP type_canal_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM partenaires) LOOP partenaires_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM types_adresses) LOOP types_adresses_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM types_magasins) LOOP types_magasins_map(row.code) := row.id; END LOOP;
FOR row IN (SELECT id, code FROM statut_donnees_partenaire) LOOP statut_donnees_partenaire_map(row.code) := row.id; END LOOP;
-- bulk collect afin de reduire le cout associe au changement
-- de contexte entre le moteur sql et celui de scripting
OPEN imp_cur;
LOOP FETCH imp_cur BULK COLLECT INTO imp_tbl LIMIT imp_cur_max;
EXIT WHEN imp_tbl.COUNT = 0;
FOR lino IN 1..imp_tbl.COUNT LOOP
imp_row := imp_tbl(lino);
-- la centralisation de la donnee membre a pour consequence la fusion
-- des anciennes entrees dont l'arbre decisionnel se trouve ci-dessous.
IF imp_row.adhesion_magasinsu_statut IS NULL THEN
log('ERROR', lino, imp_row.membre_identifiant || ' n''est pas associe a l''application magasins-u');
-- #3
ELSIF imp_row.membre_idrxp0 IS NULL THEN
log('ERROR', lino, '1st rxp id is null');
-- #2
ELSIF imp_row.idrxp0 IS NOT NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id0, imp_row.idcarteu0);
ELSIF imp_row.idrxp0 IS NULL THEN
IF imp_row.membre_idrxp1 IS NULL THEN
-- #12
IF imp_row.membre_idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #9
ELSIF imp_row.idrxp2 IS NULL THEN
log('ERROR', lino, 'fusion d''ids RXP non-valide : 0=non-existant 1=vide 2=non-existant');
-- #6
ELSIF imp_row.idrxp2 IS NOT NULL THEN
log('ERROR', lino,
'fusion d''ids RXP non-valide : 0=non-existant 1=vide 2=existe. ' ||
'ce 3eme (idx=2) est juge obsolete et donc rejete'
);
END IF;
ELSIF imp_row.idrxp1 IS NULL THEN
-- #11
IF imp_row.membre_idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #8
ELSIF imp_row.idrxp2 IS NULL THEN
create_membre(lino, imp_row);
-- #5
ELSIF imp_row.idrxp2 IS NOT NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id2, imp_row.idcarteu2);
END IF;
ELSIF imp_row.idrxp1 IS NOT NULL THEN
-- #10
IF imp_row.membre_idrxp2 IS NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
-- #7
ELSIF imp_row.idrxp2 IS NULL THEN
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
-- #4
ELSIF imp_row.idrxp2 IS NOT NULL THEN
delete_membre(lino, imp_row);
update_membre(lino, imp_row, imp_row.old_membres_id1, imp_row.idcarteu1);
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
CLOSE imp_cur;
END;
Learn the cause and how to resolve the ORA-06502 error message in Oracle.
Description
When you encounter an ORA-06502 error, the following error message will appear:
- ORA-06502: PL/SQL: numeric or value error
Cause
You tried to execute a statement that resulted in an arithmetic, numeric, string, conversion, or constraint error.
The common reasons for this error are:
- You tried to assign a value to a numeric variable, but the value is larger than the variable can handle.
- You tried to assign a non-numeric value to a numeric variable and caused a conversion error.
Resolution
Let’s look at three options on how to resolve the ORA-06502 error:
Option #1 — Value too large
In our first option, this error occurs when you try to assign a value to a numeric variable, but the value is larger than the variable can handle.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
The first line of the error message (ie: ORA-06502) indicates the error that occurred, while the second line of the error message (ie: ORA-06512) indicates that the error occurred at line 5 of the PLSQL code.
In this example, you’ve tried to assign a 3 digit number to a variable called v_number that can only handle 2 digits. You could correct this error by redefining the v_number variable as number(3).
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(3); 4 BEGIN 5 v_number := 100; 6 END; 7 / Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #2 — Conversion error
In our second option, this error occurs if you are trying to assign a non-numeric value to a numeric variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_number number(2); 4 BEGIN 5 v_number := 'a'; 6 END; 7 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
SQL> execute TestProc(); BEGIN TestProc(); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "EXAMPLE.TESTPROC", line 5 ORA-06512: at line 1
In this example, the value of ‘a’ does not properly convert to a numeric value. You can correct this error by assigning the variable called v_number a proper numeric value.
SQL> CREATE OR REPLACE PROCEDURE TestProc
2 AS
3 v_number number(2);
4 BEGIN
5 v_number := ASCII('a');
6 END;
7 /
Procedure created.
And now when we execute our TestProc procedure, the ORA-06502 error has been resolved.
SQL> execute TestProc(); PL/SQL procedure successfully completed.
Option #3 — Assigning NULL to a NOT NULL constrained variable
In our third option, this error occurs if you are trying to assign a NULL value to a NOT NULL constrained variable.
For example, if you created a procedure called TestProc as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) NOT NULL := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
This procedure was successfully created. But when we try to execute this procedure, we will get an ORA-06502 error as follows:
ORA-06502: PL/SQL: numeric or value error
In this example, you can not assign a NULL value to the variable called v_non_nullable_variable. You can correct this error removing NOT NULL from the variable declaration of the v_non_nullable_variable as follows:
SQL> CREATE OR REPLACE PROCEDURE TestProc 2 AS 3 v_non_nullable_variable VARCHAR2(30) := '5'; 4 v_null_variable VARCHAR2(30) := NULL; 5 BEGIN 6 v_non_nullable_variable := v_null_variable; 7 EXCEPTION 8 WHEN OTHERS THEN 9 dbms_output.put_line(SQLERRM); 10 END; 11 / Procedure created.
Hi,
Can anyone let me know what is the issue on below code and how can we avoid that? i am getting the error like below
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
v_while_loop := 1;
v_delete_char_id := v_tabtype_result_values.FIRST;
WHILE v_while_loop <> v_tabtype_result_values.COUNT
LOOP
IF check_duplicate_nc_data (v_result_view,
v_tabtype_result_values(v_delete_char_id).value,
v_collection_id,
v_occurrence,
v_plan_id,
v_delete_char_id
)
THEN
DBMS_OUTPUT.PUT_LINE(v_delete_char_id);
v_tabtype_result_values.DELETE(v_delete_char_id);
ELSE
DBMS_OUTPUT.PUT_LINE(v_delete_char_id||','||v_tabtype_result_values(v_delete_char_id).id||','
||v_tabtype_result_values(v_delete_char_id).value||','
||v_tabtype_result_values(v_delete_char_id).validation_flag
);
END IF;
v_while_loop := v_while_loop + 1;
v_delete_char_id := v_tabtype_result_values.NEXT(v_delete_char_id);
END LOOP;