Procedure returning PLS-00103: Encountered the symbol "EOF" when expecting

Question

I'm new to PL/SQL, doing a homework question where I need to write a procedure that returns an error message based on day of week/time but I am stuck on a basic thing before I get to that point:

I have written the following:

CREATE OR REPLACE PROCEDURE
SECURE_IT
(p_weekday NUMBER,
 p_currtime TIME)
IS
BEGIN
select to_char(current_timestamp,'D')
  , to_char(current_timestamp,'HH24:MI') 
into p_weekday, p_currtime 
from dual;
DBMS_OUTPUT.PUT_LINE(p_weekday,p_currtime);
END;

I think all of my ;'s are all in place, can't see any difference between this, code in the book, and code I've found online yet still it returns this error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the 
following: ; <an identifier> <a double-quoted delimited-identifier> 
current delete exists prior <a single-quoted SQL string> The symbol ";" 
was substituted for "end-of-file" to continue.

I tried changing END; to END SECURE_IT; in hopes that it would fix something (all I could think of) but can't see what's wrong. Could someone help me with this?

Thank you in advance :-)

Unfortunately the error message in this case doesn't help you. There are two problems: (1) `TIME` is not a valid datatype unless you have defined it yourself; (2) `DBMS_OUTPUT.put_line` only accepts one parameter. — Jeffrey Kemp, Nov 27 '15 at 05:46
Another issue is that your parameters use the default `IN` mode, but your procedure is trying to assign new values to them - this won't be allowed. — Jeffrey Kemp, Nov 27 '15 at 05:47

Hawk · Answer 1 · 2015-11-27T05:57:03.070

There is a couple of mistakes in your procedure

TIME datatype (In PL/SQL there is no such datatype see Documentation, Alternatively, you can pass datetime data types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) You can extract time part using many options. For example:
```
SELECT TO_CHAR(p_currDateTime, 'HH24:MI:SS') into p_currtime FROM DUAL;
```
You are trying to update p_weekday & p_currtime which are IN parameters. These parameters are used to pass value to the procedure, and not to return ones.
DBMS_OUTPUT.PUT_LINE takes only one parameter, so instead of , you can concatenate the two values like this: DBMS_OUTPUT.PUT_LINE(p_weekday||'-'||p_currtime);

It would help a great deal if it is not clear what are you trying to achieve by this procedure

score 0 · Answer 2 · answered Nov 27 '15 at 06:15

Basically what i can see here is you need to print some value out od procedure as a part of your homework. But using dbms_output will not do any good. You need to make these as OUT param to use these values any where.

    CREATE OR REPLACE PROCEDURE SECURE_IT(
        p_weekday OUT VARCHAR2,
        p_currtime OUT VARCHAR2)
    IS
    BEGIN
      p_weekday := TO_CHAR(CURRENT_TIMESTAMP,'D');
      p_currtime:= TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI');
      DBMS_OUTPUT.PUT_LINE(p_weekday||' '||p_currtime);
    END;

-----------------------------EXECUTE--------------------------------------------

var week VARChaR2(100);
var curtime VARChaR2(100);

EXEC SECURE_IT(:week,:curtime);

print week;
print curtime;

-------------------------------OUTPUT--------------------------------------------

WEEK
-
6

CURTIME
-----
06:12

-------------------------------OUTPUT----------------------------------------------

Procedure returning PLS-00103: Encountered the symbol "EOF" when expecting

2 Answers2