Oracle 11g
Hi,
i'im trying to update two colums from SUPPORT (SUPPORT_X, SUPPORT_Y) table with two colums of POST_HTA_BT( POSTHTABT_GPS_X, POSTHTABT_GPS_Y ) table
Understand that the two tables have the colum below:
SUPPORT(SUPPORT_ID,SUPPORT_PLAQUE,POSTHTABT_ID,SUPPORT_X, SUPPORT_Y,....)
POST_HTA_BT (POSTHTABT_ID,POSTHTABT_GPS_X, POSTHTABT_GPS_Y,..)
The SUPPORT_PLAQUE has the varachar type. Except the keys, other colums are varchar type in the two tables.
The point here is to update the support_x, support_y with posthtabt_gps_x and posthtabt_gps_y.But before update we must cheik if the fifth number of support plaque is a character number between "0" to "9' and if the rest of the caracter of the support_plaque is '00000'
Please note that the support_plaque is stored in the table with the form: "0025800000"!!!
So i did the script below i'm trying to execute in sql developper.
SET SERVEROUTPUT ON
DECLARE
chiffre_liste varchar(200):= '0','1','2','3','4','5','6','7','8','9';
CURSOR CUR_GPS_SUPPORT IS
select SUPPORT.SUPPORT_X,SUPPORT.SUPPORT_Y,POSTE_HTA_BT.POSTHTABT_ID,SUPPORT.EXPL_ID,
SUPPORT.SUPPORT_PLAQUE,POSTHTABT_GPS_X,POSTHTABT_GPS_Y
from SUPPORT,
POSTE_HTA_BT
where
SUPPORT.SUPPORT_X IS NULL and
SUPPORT.SUPPORT_Y IS NULL and
SUPPORT.POSTHTABT_ID = POSTE_HTA_BT.POSTHTABT_ID and
SUPPORT.EXPL_ID = POSTE_HTA_BT.EXPL_ID
order by SUPPORT.POSTHTABT_ID;
w_POSTHTABT_ID POSTE_HTA_BT.POSTHTABT_ID%type;
w_SUPPORT_X SUPPORT.SUPPORT_X%TYPE;
w_SUPPORT_Y SUPPORT.SUPPORT_Y%TYPE;
w_EXPL_ID SUPPORT.EXPL_ID%TYPE;
w_SUPPORT_PLAQUE SUPPORT.SUPPORT_PLAQUE%TYPE;
w_POSTHTABT_GPS_X POSTE_HTA_BT.POSTHTABT_GPS_X%TYPE;
w_POSTHTABT_GPS_Y POSTE_HTA_BT.POSTHTABT_GPS_Y%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Chargement des coordoonnées GPS -Mise à jour Coord GPS des supports de Départ');
FOR CUR IN CUR_GPS_SUPPORT LOOP
w_POSTHTABT_ID := cur.POSTHTABT_ID;
w_SUPPORT_PLAQUE := cur.SUPPORT_PLAQUE;
w_SUPPORT_X := cur.SUPPORT_X;
w_SUPPORT_Y := cur.SUPPORT_Y;
w_POSTHTABT_GPS_X := cur.POSTHTABT_GPS_X;
w_POSTHTABT_GPS_Y := cur.POSTHTABT_GPS_X;
if substr(cur.support_plaque,5,1) in chiffre_liste and substr(cur.support_plaque,6,5)='00000'
w_SUPPORT_X := CUR.POSTHTABT_GPS_X
w_SUPPORT_Y := CUR.POSTHTABT_GPS_Y
END if;
EXCEPTION WHEN NO_DATA_FOUND THEN w_SUPPORT_X := NULL and w_SUPPORT_Y := NULL;
END;
--Update de la table des supports
update SUPPORT
set SUPPORT_X = w_SUPPORT_X,
SUPPORT_Y = w_SUPPORT_Y
where SUPPORT_PLAQUE = w_SUPPORT_PLAQUE;
-- On valide imm?diatement
commit;
EXCEPTION when no_data_found then null;
-- Pas de coordonnées trouvées
END;
END;
/
and i got the following errors:
Rapport d'erreur :
ORA-06550: Line 2, colum 34 :
PLS-00103: Symbole "," rencontré à la place d'un des symboles suivants :
* & = - + ; < / > at in is mod remainder not rem
<exposant (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
ORA-06550: Ligne 2, colonne 52 :
PLS-00103: Symbole ";" rencontré à la place d'un des symboles suivants :
) , * & = - + < / > at in is mod remainder not rem =>
<exposant (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member subm
ORA-06550: Line 38, colum 48 :
PLS-00103: Symbole "CHIFFRE_LISTE" rencontré à la place d'un des symboles suivants :
(
Symbole "(" a été substitué à "CHIFFRE_LISTE" pour continuer.
ORA-06550: Line 39, colum 12 :
PLS-00103: Symbole "W_SUPPORT_X" rencontré à la place d'un des symboles suivants :
) , * & - + / at mod remainder rem <exposant (**)> and or ||
multiset
ORA-06550: Line 40, colum 12 :
PLS-00103: Symbole "W_SUPPORT_Y" rencontré à la place d'un des symboles suivants :
. ( ) , * @ % & = - + < / > at in is mod remainder not rem
<exposant (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || mult
ORA-06550: Line 41, colum 9 :
PLS-00103: Symbole "END" rencontré à la place d'un des symboles suivants :
. ( ) , * @ % & = - + < / > at in is mod remainder not rem
<exposant (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset memb
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I verified the line number, but don't see the error in my code.
Please could you help me?
peace