I have a procedure and I want to modify this. When I use this request:
CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (:p_loading_mode ='incremental') THEN
COPY INTO SFR_AC_REENG_FIXE_HANDICALL_BORDEAUX (
V_NUM_COMMANDE_MIG,
V_SITE_CMD,
V_ACTIVITE_EZY,
V_TYPE_AC,
D_JOUR_CMD,
D_JOUR_PANIER,
V_SEMAINE_CMD,
V_MOIS_CMD,
V_LOGIN_CC_CMD,
V_NUM_CONTRAT,
V_TYPE_ACTE,
B_MIG,
V_COURANT_PTA_MIG,
V_LIBL_COURANT_PTA_MIG,
V_FUTUR_PTA_MIG,
V_LIBL_FUTUR_PTA_MIG,
V_LIBL_FUTUR_PTA_MIG_COURT,
B_REMISE,
V_LIBL_REMISE,
N_MT_REMISE_M_TTC,
B_USAGE,
V_LIBL_USAGE,
V_NOM,
V_PRENOM,
N_FACIAL_AVANT,
N_FACIAL_APRES,
N_REMISE_AVANT,
N_REMISE_APRES,
N_MOUVEMENT_VALEUR,
V_CODE_CAMPAGNE,
B_FLAG_HBD,
V_CODE_CAMPAGNE,
V_CODE_LOT,
V_RAC,
V_TECH_NOM_FICHIER,
V_TECH_STATUT_TRAITEMENT,
V_TECH_NOM_TRAITEMENT,
TS_TECH_DEBUT_TRAITEMENT
) FROM (
SELECT
$12,
$1,
$2,
$3,
TO_DATE($4, 'YYYY-MM-DD'),
TO_DATE($5, 'YYYY-MM-DD'),
$6,
$7,
$8,
$9,
$10,
CAST($11 AS BOOLEAN),
$13,
$14,
$15,
$16,
$17,
CAST($18 AS BOOLEAN),
$19,
CAST($20 AS NUMBER(10, 0)),
CAST($21 AS BOOLEAN),
$22,
$23,
$24,
CAST(REPLACE($25, ',', '.') AS NUMBER(6, 3)),
CAST(REPLACE($26, ',', '.') AS NUMBER(6, 3)),
CAST(REPLACE($27, ',', '.') AS NUMBER(4, 2)),
CAST(REPLACE($28, ',', '.') AS NUMBER(4, 2)),
CAST(REPLACE($29, ',', '.') AS NUMBER(6, 3)),
$30,
TO_BOOLEAN($31),
$32,
$33,
$34,
METADATA$FILENAME,
'I',
'INGEST MODE : '|| :p_loading_mode,
current_timestamp(0)
FROM @ES_SFR )
FILE_FORMAT = ( FORMAT_NAME = FF_INGEST_CSV_SFR)
PATTERN = '.*incremental.*ac_reeng_fixe_handicall_bordeaux.*\.csv';
END IF;
It work. But if I add just a column, I have the message Syntax error: unexpected '<EOF>'. no matter the column.
Example:
CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
IF (:p_loading_mode ='incremental') THEN
COPY INTO SFR_AC_REENG_FIXE_HANDICALL_BORDEAUX (
V_NUM_COMMANDE_MIG,
V_SITE_CMD,
V_ACTIVITE_EZY,
V_TYPE_AC,
D_JOUR_CMD,
D_JOUR_PANIER,
V_SEMAINE_CMD,
V_MOIS_CMD,
V_LOGIN_CC_CMD,
V_NUM_CONTRAT,
V_TYPE_ACTE,
B_MIG,
V_COURANT_PTA_MIG,
V_LIBL_COURANT_PTA_MIG,
V_FUTUR_PTA_MIG,
V_LIBL_FUTUR_PTA_MIG,
V_LIBL_FUTUR_PTA_MIG_COURT,
B_REMISE,
V_LIBL_REMISE,
N_MT_REMISE_M_TTC,
B_USAGE,
V_LIBL_USAGE,
V_NOM,
V_PRENOM,
N_FACIAL_AVANT,
N_FACIAL_APRES,
N_REMISE_AVANT,
N_REMISE_APRES,
N_MOUVEMENT_VALEUR,
V_CODE_CAMPAGNE,
B_FLAG_HBD,
V_CODE_CAMPAGNE,
V_CODE_LOT,
V_RAC,
V_PROMO_TR,
V_TECH_NOM_FICHIER,
V_TECH_STATUT_TRAITEMENT,
V_TECH_NOM_TRAITEMENT,
TS_TECH_DEBUT_TRAITEMENT
) FROM (
SELECT
$12,
$1,
$2,
$3,
TO_DATE($4, 'YYYY-MM-DD'),
TO_DATE($5, 'YYYY-MM-DD'),
$6,
$7,
$8,
$9,
$10,
CAST($11 AS BOOLEAN),
$13,
$14,
$15,
$16,
$17,
CAST($18 AS BOOLEAN),
$19,
CAST($20 AS NUMBER(10, 0)),
CAST($21 AS BOOLEAN),
$22,
$23,
$24,
CAST(REPLACE($25, ',', '.') AS NUMBER(6, 3)),
CAST(REPLACE($26, ',', '.') AS NUMBER(6, 3)),
CAST(REPLACE($27, ',', '.') AS NUMBER(4, 2)),
CAST(REPLACE($28, ',', '.') AS NUMBER(4, 2)),
CAST(REPLACE($29, ',', '.') AS NUMBER(6, 3)),
$30,
TO_BOOLEAN($31),
$32,
$33,
$34,
$35,
METADATA$FILENAME,
'I',
'INGEST MODE : '|| :p_loading_mode,
current_timestamp(0)
FROM @ES_SFR )
FILE_FORMAT = ( FORMAT_NAME = FF_INGEST_CSV_SFR)
PATTERN = '.*incremental.*ac_reeng_fixe_handicall_bordeaux.*\.csv';
END IF;
I search since this morning, I was able to add more column by deleting the comments. I therefore really wonder if this is not a limitation of the number of characters in the procedure. Any idea ?
Thanks
To fix the issue you need to add the $$ sings to quote the code block.
Example:
CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
--your code the scripting block
END
$$;