CODEWITHSUNDEEP
oracle-databaseplsqloracle11gdynamic-sql
KTB
KTB

Reputation: 1529

Dynamic PL/SQL function

I'm trying to use the following PL/SQL function - oracle.

FUNCTION GET_RECORDS_FOR_REFRESH( KEY_ARRAY IN VARCHAR2 ) RETURN SYS_REFCURSOR

    IS  RET_DATA SYS_REFCURSOR;
        SQL_QUEURY VARCHAR2(32767);

    BEGIN

        SQL_QUEURY:= 'SELECT * FROM PASSESNGERS WHERE ' || KEY_ARRAY ;

        OPEN RET_DATA FOR SQL_QUEURY;

        RETURN RET_DATA;

    END; 
END;

KEY_ARRAY contains a comma separated values with column name attached. For example KEY_ARRAY = 'PAS_PROF_ID IN (15236,85694,45863,59456,42387)'

Although this function has no compilation errors, it results following error at runtime.

java.sql.SQLException: ORA-06550: line 1, column 62:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   . ( * % & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

Can someone please tell me what's the mistake here and what I should do?

Thanks in advance.

Upvotes: 0

Views: 381

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

    END; 
END;

This might be silly, but I see you have two END statements in the PL/SQL code you have shared. Probably this is the reason you are getting PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following.

Although this function has no compilation errors, it results following error at runtime.

PLS error is a compile time error. Remove one of the ENDs, and recompile.

Also, it is a good practice to use DBMS_OUTPUT.PUT_LINE and first check the query formation before actually executing it in the code.

Upvotes: 2

Related Questions