snowflake-cloud-data-platformprocedureeof

SNowflake - Syntax error: unexpected '<EOF>'. Query size limit?


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


Solution

  • 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
    $$;
    
    

    Doc reference.