CODEWITHSUNDEEP
oracle-database
jkrempien
jkrempien

Reputation: 13

Oracle Error PLS-00103: Encountered the symbol "("

I wrote the following Package. When I compile it I got the following error:

PLS-00103: Encountered the symbol "("
Line 42 Column 66

Which is "PROCEDURE p_fail "

I am helpless. I searched the Internet but found nothing what helps me. Does anyone has an idea?

Many thanks in advance.

CREATE OR REPLACE PACKAGE BODY boxi_rep.pck_jk_test AS
PROCEDURE p_main
IS
    err_num         NUMBER;
    err_msg         VARCHAR2 (200);
BEGIN
    boxi_rep.pck_jk_test.p_start;
    boxi_rep.pck_jk_test.p_truncate;
EXCEPTION
    WHEN OTHERS
    THEN
        err_num     := SQLCODE;
        err_msg     := SUBSTR (SQLERRM, 1, 200);
        boxi_rep.pck_jk_test.p_fail (err_num, err_msg);
END;

PROCEDURE p_start
IS
BEGIN
    /*Make start entry into Log_Jobs*/
    DELETE FROM log_jobs
     WHERE job_id = '1501'
        AND TRUNC (datum) = TRUNC (SYSDATE)
        AND end_timestamp IS NULL;


    INSERT INTO log_jobs (job_id,
                                 job_name,
                                 datum,
                                 start_timestamp)
    VALUES ('1501',
              'V$Re_Schedule TEST',
              TRUNC (SYSDATE),
              SYSDATE);

    COMMIT;
END;



PROCEDURE p_fail (in_err_code IN NUMBER, in_err_msg IN VARCHAR2 (200))
IS
BEGIN
    UPDATE log_jobs
        SET end_timestamp = SYSDATE,
             status       = 'FAILED  - ' || in_err_code || ' - ' || in_err_msg,
             duration     = TO_CHAR (TO_DATE ('00:00:00', 'hh24:mi:ss') + (SYSDATE - start_timestamp), 'hh24:mi:ss')
     WHERE job_id = '1501'
        AND end_timestamp IS NULL;

    COMMIT;
END;

PROCEDURE p_truncate
IS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE boxi_rep.jk_test';
END;
END pck_jk_test;

Upvotes: 1

Views: 888

Answers (2)

Avi
Avi

Reputation: 1145

Size of varchar2 is not allowed with in and out partameter

 PROCEDURE p_fail (in_err_code IN NUMBER, in_err_msg IN VARCHAR2)
IS
BEGIN
    UPDATE log_jobs
        SET end_timestamp = SYSDATE,
             status       = 'FAILED  - ' || in_err_code || ' - ' || in_err_msg,
             duration     = TO_CHAR (TO_DATE ('00:00:00', 'hh24:mi:ss') + (SYSDATE -      start_timestamp), 'hh24:mi:ss')
     WHERE job_id = '1501'
        AND end_timestamp IS NULL;

    COMMIT;
 END;

Upvotes: 2

Andreas Fester
Andreas Fester

Reputation: 36649

See http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_packages.htm#i1006401

Numerically constrained types such as NUMBER(2) or VARCHAR2(20) are not allowed in a parameter list.

Change

PROCEDURE p_fail (in_err_code IN NUMBER, in_err_msg IN VARCHAR2 (200))

to

PROCEDURE p_fail (in_err_code IN NUMBER, in_err_msg IN VARCHAR2)

Upvotes: 3

Related Questions