user5900513 February 2016

Oracle function compilation error PLS-00103 (Encountered the symbol "SELECT" ...)

I receive the following error when I compile this function:

Compilation errors for PROCEDURE INV.USP_MSC_MODIFICA_ESTADO

Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
 continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternat    Line: 14    Text: IF SELECT TRUNC((SYSDATE) -TO_DATE(@FCH_GRABACION, 'DD/MM/YYYY HH24:MI:SS')) From DUAL=1 THEN
CREATE OR REPLACE PROCEDURE "USP_MSC_MODIFICA_ESTADO" AS
  BEGIN
    DECLARE
      CURSOR reservar IS
      SELECT
        id_reserva,
        fch_grabacion 
       FROM tb_msc_reserva
      WHERE to_date(to_char(fch_grabacion, 'dd/mm/yyyy')) = to_date(to_char(SYSDATE, 'dd/mm/yyyy')) - 1;
      id_reserva    VARCHAR2(50);
      fch_grabacion DATE;
    BEGIN
      OPEN reservar;
      FETCH reservar INTO id_reserva, fch_grabacion;

      IF SELECT TRUNC((SYSDATE) - TO_DATE(@fch_grabacion, 'DD/MM/YYYY HH24:MI:SS')) FROM dual=1 THEN

      UPDATE inv.tb_msc_reserva t
      SET t.flg_estado = 'C'
      WHERE id_reserva = @id_reserva;
      COMMIT;
    END if;
    WHILE (@@fetch_status = 0)

    CLOSE RESERVAR;

  END;

Answers


Debabrata February 2016

Franky speaking too many errors in the code. Cant Write them all. Few mistakes:

  1. Declare on line-4 not needed.
  2. Too many Begin statements.
  3. While(@@FETCH_STATUS=0) isnt used for loop termination. Use EXIT WHEN.
  4. Missed the Loop beginning & End Loop[Assuming that You are using Loop as you are Cursor in stead of Select Into]

    CREATE OR REPLACE PROCEDURE "USP_MSC_MODIFICA_ESTADO"
    AS
    CURSOR reservar
    IS
    SELECT id_reserva, fch_grabacion
    FROM tb_msc_reserva
    WHERE TO_DATE (TO_CHAR (fch_grabacion, 'dd/mm/yyyy')) =
    TO_DATE (TO_CHAR (SYSDATE, 'dd/mm/yyyy'))
    - 1;
    
    id_reserva      VARCHAR2 (50);
    fch_grabacion   DATE;
    BEGIN
    OPEN reservar;
    
    FETCH reservar
    INTO id_reserva, fch_grabacion;
    
    LOOP
    IF (SELECT TRUNC (  (SYSDATE) - TO_DATE (@fch_grabacion, 'DD/MM/YYYY HH24:MI:SS'))
    FROM DUAL) = 1
    THEN
    UPDATE inv.tb_msc_reserva t
    SET t.flg_estado = 'C'
    WHERE id_reserva = @id_reserva;
    
    COMMIT;
    END IF;
    
    EXIT WHEN (@@fetch_status = 0);
    END LOOP;
    
    CLOSE reservar;
    END;
    


user5900513 February 2016

i´m so sorry, but i´m really new in Oracle. I have change the code, but i keep reciving the same error.

CREATE OR REPLACE PROCEDURE "USP_MSC_MODIFICA_ESTADO"
AS
CURSOR reservar
IS 
SELECT id_reserva, fch_grabacion
FROM tb_msc_reserva
WHERE to_date(FCH_GRABACION,'dd/mm/yyyy') =  to_date(SYSDATE,'dd/mm/yyyy')-  1;

reserva      VARCHAR2 (50);
fch_grabacion   DATE;
BEGIN
OPEN reservar;
FETCH reservar
INTO reserva, fch_grabacion;

LOOP
IF (SELECT TRUNC((SYSDATE) -TO_DATE(fch_grabacion, 'DD/MM/YYYY HH24:MI:SS'))From DUAL=1)

THEN
UPDATE inv.tb_msc_reserva t
SET t.flg_estado = 'C'
WHERE id_reserva = reserva;

COMMIT;
END IF;

EXIT WHEN reservar%NOTFOUND ;
END LOOP;

CLOSE reservar;
END;

What i want to do, is to compare two dates (dd/mm/yyyy HH:mm:ss). (Yesterday and today) If my registry have more than 24 hours, then ID_ESTADO='C'

I have the error here:

 IF (SELECT TRUNC((SYSDATE) -TO_DATE(fch_grabacion, 'DD/MM/YYYY  HH24:MI:SS'))   From DUAL=1)

the message:

 Compilation errors for PROCEDURE INV.USP_MSC_MODIFICA_ESTADO
Error: PLS-00103: Encountered the symbol "SELECT" when expecting one of  the following:

  ( - + case mod new not null <an identifier>
  <a double-quoted delimited-identifier> <a bind variable>
  continue avg count current exists max min prior sql stddev
  sum variance execute forall merge time timestamp interval
  date <a string literal with character set specification>
  <a number> <a single-quoted SQL string> pipe
  <an alternatively-quoted string literal with character set  specification>
   <an alternat
 Line: 18
 Text: IF (SELECT TRUNC((SYSDATE) -TO_DATE(fch_grabacion, 'DD/MM/YYYY    HH24:MI:SS')) From DUAL=1)

 Error: PLS-00103: Encountered the symbol "=" when expecting one of the   f       oollowing:

  . , @ ; for <an identifier>
  <a double-quoted delimited-identifier> group having intersect
  mi 


Debabrata February 2016

The issue with ur code is that u r using comparison with 1 inside the Select clause. Ur Code:

IF (SELECT TRUNC((SYSDATE) -TO_DATE(fch_grabacion, 'DD/MM/YYYY HH24:MI:SS'))From DUAL=1)

Corrected Code:

IF (SELECT TRUNC (  (SYSDATE) - TO_DATE (fch_grabacion, 'DD/MM/YYYY HH24:MI:SS')) FROM DUAL) = 1

However, there are better ways to compare 24hrs time difference[i m confused here as in whether u mean 1day difference or exactly 24hrs difference]: 1.If you want to compare 24hrs:

IF TO_DATE (fch_grabacion, 'DD/MM/YYYY HH24:MI:SS') < (SYSDATE - 1)

2. If you want to compare 1d:

IF TO_DATE (fch_grabacion, 'DD/MM/YYYY') < TRUNC ((SYSDATE - 1))

Hope this helps. If it does. Dont forget to mark this answer accepted and upvote.[I can do with some extra rep :p]

Post Status

Asked in February 2016
Viewed 2,457 times
Voted 12
Answered 3 times

Search




Leave an answer