PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: :=(@%;

Question

i'm trying to execute a stored procedure dynamically since i got alof of them based on a simple number, so i created another procedure to do this, but i keep getting the erro on the title of my question, here is my procedure:

PROCEDURE P_EXEC_REG(p_Register IN VARCHAR2, p_LineBuff IN VARCHAR2, p_User IN VARCHAR2) IS  

  l_Procedure   VARCHAR2(50);
  l_Sentence    VARCHAR2(500);

BEGIN    
  l_Procedure := 'P_REG_' || TRIM(p_Register);
  l_Sentence := 'BEGIN CTS.PK_INTEGRATE_MANIFESTO.' || l_Procedure || '(:A, :B); END;';
  EXECUTE IMMEDIATE l_Sentence
  USING IN p_LineBuff, p_User;    
END;

Since we have hundreds of procedures, it is a pain to do it with if statements, hope somebody can help me out on this, also, i will show you how i am calling this:

PROCEDURE P_LOAD_FILE(p_Linebuff IN VARCHAR2, p_User IN VARCHAR2) IS

l_Register        VARCHAR2(3);

BEGIN
    BEGIN
       SELECT SUBSTR(p_Linebuff,1,3)
       INTO   l_Register
       FROM   DUAL;                 
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
            --p_result := false;
            NULL;
    END;
    CTS.PK_INTEGRATE_MANIFESTO.P_EXEC_REG(l_Register, p_Linebuff, p_User);

    EXCEPTION
      WHEN OTHERS THEN
      NULL;         
END;

Add `dbms_output.put_line(l_Sentence);` right before the `execute immediate`, and show us the last line called. — Jon Heller, Apr 29 '15 at 02:56
Ok, so this is what i get from that l_Sentence "Conectando a la base de datos Manifiestos_Lazaro. BEGIN CTS.PK_INTEGRATE_MANIFESTO.P_REG_900(:p_LineBuff, :p_User); END; El proceso ha terminado. Desconectando de la base de datos Manifiestos_Lazaro." — Isael Macias, Apr 29 '15 at 16:30
I'll translate that part, it says this: "Connecting to data base Manifiestos_Lazaro. BEGIN CTS.PK_INTEGRATE_MANIFESTO.P_REG_900(:p_LineBuff, :p_User); END; Process has finished. Disconnecting from data base Manifiestos_Lazaro" — Isael Macias, Apr 29 '15 at 16:32

score 0 · Accepted Answer · answered Apr 29 '15 at 17:34

Thank you guys, i found the answer to this issue thanks to @John Heller's comment, with that command i could notice that it was in fact executing, but when i was sending the dynamic parameter, which isl_Register, i wasnt checking for null values, so it went straight to EXECUTE IMMEDIATE with a procedure with a null parameter at the end. Now it looks like this:

PROCEDURE P_LOAD_FILE(p_Linebuff IN VARCHAR2, p_User IN VARCHAR2, p_Error OUT BOOLEAN, p_Message OUT VARCHAR2) IS

  l_MessageLevel    NUMBER(1);
  l_Register        VARCHAR2(3);
  l_Command         VARCHAR2(50);
  l_Procedure       VARCHAR2(4000);
  l_Error           NUMBER(1);

  BEGIN              

  IF p_Linebuff IS NOT NULL OR p_Linebuff <> '' THEN
        BEGIN
           SELECT SUBSTR(p_Linebuff,1,3)
           INTO   l_Register
           FROM   DUAL;

           IF LENGTH(TRIM(l_Register)) = 3 THEN
              P_EXEC_REG(l_Register, p_Linebuff, p_User, l_Error, p_Message);
           END IF;               
        EXCEPTION
             WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END IF;

  EXCEPTION
  WHEN OTHERS THEN
      p_Error := TRUE;
      p_Message := 'Sucedio un error al procesar el archivo.';      
  END;

Thanks for the help, and sorry if it was a dumb question :)

PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: :=(@%;

1 Answers1