CODEWITHSUNDEEP
plsqloracle11gcursor
Phalani Kumar
Phalani Kumar

Reputation: 205

cursor declaration with order by clause throwing error

What is wrong in below cursor declaration, am getting error

Error(4,6): PL/SQL: SQL Statement ignored
Error(10,3): PL/SQL: ORA-00907: missing right parenthesis.

When I remove order by clause and try its not throwing error. But with order by clause its not getting compiles. Order by wont work for cursors or am missing anything?

CREATE OR REPLACE PROCEDURE TEST
    IS
     CURSOR c_missing_eve 
      IS (
      SELECT 
      P.PID,P.DIRECTION,E.EVE_NAME 
      FROM PAY P,EVE E, PAY_EVE_LINK EL 
      WHERE E.EVE_ID = EL.EVE_ID
      AND EL.PAY_ID = P.PAY_ID 
      AND P.VALUE_DATE=TO_DATE(SYSDATE-1) 
      AND METHOD IN ('FT', 'FT-RT') 
      ORDER BY P.PID);
    BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO');
    --business loginc
    END;

Upvotes: 1

Views: 1105

Answers (2)

XING
XING

Reputation: 9886

No need to put ( after cursor. Try this:

CREATE OR REPLACE PROCEDURE TEST
IS
  CURSOR c_missing_eve
  IS    
      SELECT
        P.PID,
        P.DIRECTION,
        E.EVE_NAME
      FROM
        PAY P,
        EVE E,
        PAY_EVE_LINK EL
      WHERE
        E.EVE_ID      = EL.EVE_ID
      AND EL.PAY_ID   = P.PAY_ID
      AND P.VALUE_DATE= TO_DATE(SYSDATE-1)
      AND METHOD   IN ('FT', 'FT-RT')
      ORDER BY         P.PID ;
BEGIN
  DBMS_OUTPUT.PUT_LINE('HELLO');
  --business loginc
END;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Your specific problem is that the table pay uses the alias p, so pay is not understood.

You need:

order by p.pid

Along the way, you should learn to use proper, explicit, standard JOIN syntax:

SELECT P.PID, P.DIRECTION, E.EVE_NAME
FROM PAY P JOIN
     PAY_EVE_LINK EL 
     ON EL.PAY_ID = P.PAY_ID JOIN
     EVE E
     ON E.EVE_ID = EL.EVE_ID
WHERE P.VALUE_DATE = TRUNC(SYSDATE-1) AND METHOD IN ('FT', 'FT-RT') 
ORDER BY P.PID;

Additional notes:

  • TO_DATE() is to convert a string to a date. sysdate is already a date, so the appropriate function is trunc().
  • METHOD should be qualified with a table name.
  • If your code does not require a cursor, then you should use just query to do the processing.

Upvotes: 0

Related Questions