CODEWITHSUNDEEP
plsql
iqueqiorio
iqueqiorio

Reputation: 1187

PL/SQL if statement error

Here is what I have in my stored procedure

CREATE OR REPLACE PROCEDURE GET_USER (IN IN_USER_NAME VARCHAR(256))
    DYNAMIC RESULT SETS 1
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN for

        IF (IN_USER_NAME IS NULL) THEN
            SELECT BLUEMSP.USERS.USER_ID FROM BLUEMSP.USERS;
        END IF;

    -- Cursor left open for client application
    OPEN cursor1;
END P1

I am getting an error on the If statement line that says 

Multiple markers at this line
"." was expected instead of "(".
- "JOIN" was expected instead of "THEN".
- "IF (IN_USER_NAME IS NULL) THEN
        SELECT BLUEMSP.USERS.USER_ID FROM BLUEMSP." appears to be misplaced.
- "(" was expected after "IS".

Why is this not working?

Thanks

Upvotes: 0

Views: 418

Answers (2)

Michał B
Michał B

Reputation: 62

CREATE OR REPLACE PROCEDURE GET_USER (IN_USER_NAME IN  VARCHAR2)
IS
xxx BLUEMSP.USERS.USER_ID%TYPE; 
CURSOR cursor1 IS SELECT BLUEMSP.USERS.USER_ID FROM BLUEMSP.USERS;
BEGIN
IF (IN_USER_NAME IS NULL) THEN               
   OPEN cursor1;
   FETCH cursor1 INTO xxx;
   ...
END IF
CLOSE cursor1;
END

Upvotes: 0

MJH
MJH

Reputation: 2307

You can't use an IF statement in a cursor declaration, as IF is PL/SQL, and a cursor declaration must be pure SQL.

Upvotes: 2

Related Questions