Error "end-of-file" in the declaration of a cursor

Question

I am doing a PL/SQL where I initially declare a cursor and then work with these values. The problem is that, along the lines of A.FH_DESACTIVEDATE DESC; I get the following error:

SQL Error [6550] [65000]: ORA-06550: línea 27, columna 29:
PLS-00103: Se ha encontrado el símbolo "end-of-file" cuando se esperaba uno de los siguientes:

   ;

The mistake doesn't make sense to me. I've searched other pages, looked at other examples, and still can't find the reason why this error appears.

The PL/SQL is:

DECLARE
    CURSOR cursor_centre IS
        SELECT
            A.ID_CENTRE,
            A.TX_NAME,
            CASE WHEN A.FH_DESACTIVEDATE IS NOT NULL THEN 1 ELSE 0 END desactiveDate,
            A.NU_FKGROUP
        FROM
            CENTRE A
        INNER JOIN (
            SELECT
                FU_WITHOUTACCENT(REPLACE(TX_NAME, '.', '')) AS normalized_name,
                NU_FKORGANIZATION
            FROM
                CENTRE
            GROUP BY
                FU_WITHOUTACCENT(REPLACE(TX_NAME, '.', '')),
                NU_FKORGANIZATION
            HAVING
                COUNT(*) > 1
        ) B ON
            FU_WITHOUTACCENT(REPLACE(A.TX_NAME, '.', '')) = B.normalized_name
            AND A.NU_FKORGANIZATION = B.NU_FKORGANIZATION
        ORDER BY
            A.TX_NAME DESC,
            A.NU_FKGROUP ASC,
            A.FH_DESACTIVEDATE DESC;
        
    c_id_centre NUMBER;
    c_name VARCHAR2(255);
    c_desactive_date NUMBER;
    c_group NUMBER;

...

I have executed the subqueries individually and the set and it works correctly, but not the cursor declaration. What is happening?

I have reviewed examples of other colleagues and the statement of their cursors is the same. I've searched for information from other queries on stack or other pages but I can't find this error exactly.

Answer 1

There's nothing wrong with that piece of code.

Sample table and function (as you didn't post them), just to make anonymous block compile:

SQL> CREATE TABLE centre
  2  (
  3     id_centre           NUMBER,
  4     tx_name             VARCHAR2 (10),
  5     fh_desactivedate    DATE,
  6     nu_fkgroup          NUMBER,
  7     nu_fkorganization   VARCHAR2 (10)
  8  );

Table created.

SQL> CREATE OR REPLACE FUNCTION fu_withoutaccent (par_tx_name IN centre.tx_name%TYPE)
  2     RETURN centre.tx_name%TYPE
  3  IS
  4  BEGIN
  5     RETURN NULL;
  6  END;
  7  /

Function created.

This is code you're complaining about:

SQL> DECLARE
  2     CURSOR cursor_centre IS
  3          SELECT a.id_centre,
  4                 a.tx_name,
  5                 CASE WHEN a.fh_desactivedate IS NOT NULL THEN 1 ELSE 0 END desactivedate,
  6                 a.nu_fkgroup
  7            FROM centre a
  8                 INNER JOIN
  9                 (  SELECT fu_withoutaccent (REPLACE (tx_name, '.', '')) AS normalized_name,
 10                           nu_fkorganization
 11                      FROM centre
 12                  GROUP BY fu_withoutaccent (REPLACE (tx_name, '.', '')), nu_fkorganization
 13                    HAVING COUNT (*) > 1) b
 14                    ON     fu_withoutaccent (REPLACE (a.tx_name, '.', '')) =
 15                           b.normalized_name
 16                       AND a.nu_fkorganization = b.nu_fkorganization
 17        ORDER BY a.tx_name DESC, a.nu_fkgroup ASC, a.fh_desactivedate DESC;
 18
 19     c_id_centre       NUMBER;
 20     c_name            VARCHAR2 (255);
 21     c_desactive_date  NUMBER;
 22     c_group           NUMBER;
 23  BEGIN
 24     NULL;
 25  END;
 26  /

PL/SQL procedure successfully completed.

SQL>

So ... nope, nothing's wrong with it.

---

On the other hand, you said

... along the lines of A.FH_DESACTIVEDATE ESCR;

There's no such line in code you posted. Are you sure that this code is to blame?