CODEWITHSUNDEEP
oracle-databaseif-statementstored-proceduresplsqloracle11g
Ahmad Gunawan
Ahmad Gunawan

Reputation: 45

Oracle : PLS-00103 occur on procedure with condition

When I tried to compile this procedure:

PROCEDURE GET_NUMBER_PROPOSED_TRADES(p_client_id NUMBER, p_curr_id NUMBER, p_cursor  OUT sys_refcursor)
IS
BEGIN  
  OPEN p_cursor FOR
      IF (p_client_id = 0 AND p_curr_id = 0)
      THEN
        SELECT COUNT (DISTINCT NVL(id,0)) AS "Proposed_Trade_Number"  FROM proposed_trade;
      ELSE
        SELECT COUNT (DISTINCT NVL(id,0)) AS "Proposed_Trade_Number"  FROM proposed_trade WHERE client_id = p_client_id AND ccy_id = p_curr_id;
      END IF;
END GET_NUMBER_PROPOSED_TRADES;

this error occurred:

Error(9,11): PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( - + case mod new not null select with continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Error(18,1): PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map The symbol "static" was substituted for "PROCEDURE" to continue.

Error(25,1): PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map The symbol "static" was substituted for "PROCEDURE" to continue.

But the thing is, if I just remove IF condition, I can compile it easily, so this kind of a new for me

Upvotes: 2

Views: 698

Answers (1)

Mureinik
Mureinik

Reputation: 311393

You can't nest an if condition inside a cursor declaration, it's just not valid syntax. One way around this would be to have a single query, and use the where clause conditions to get the same logic you were trying to get from the if:

OPEN p_cursor FOR
    SELECT COUNT (DISTINCT NVL(id,0)) AS "Proposed_Trade_Number"  
    FROM   proposed_trade 
    WHERE  (p_client_id = 0 AND p_curr_id = 0) OR 
           (client_id = p_client_id AND ccy_id = p_curr_id);

Upvotes: 2

Related Questions