CODEWITHSUNDEEP
sqloracle-databasestored-procedures
rockerchain
rockerchain

Reputation: 29

ORA-00904: invalid identifier (SP)

I tried to write a stored procedure:

Procedure get_cont(
                          docNum in number default null,
                          state in number default null,
                          department in varchar2,
                          dateF in varchar2 default null,
                          IOCURSOR OUT v_cursor)
IS
vSelectCursor varchar2(10000);

BEGIN

    vSelectCursor :=    'SELECT DISTINCT(cb.DOC_NUM),
                                 to_char(cb.DATE_INIT,''DD/MM/YYYY'') date_init,                     
                                 cb.DEPT,
                                 dt.STATE,
                                 dt.NAME
                         FROM department cb,
                              db_book dt
                         WHERE cb.code = dt.code
                               AND cb.DEST = ''MIT2'' 
                                 ';

    IF docNum IS NOT NULL THEN               
         vSelectCursor := vSelectCursor || ' AND det.DOC_NUM = ' || docNum ;
    END IF;
    IF state <> -1 THEN               
         vSelectCursor := vSelectCursor || ' AND cb.STATE = ' || state ;
    END IF;
    IF department <> '-' THEN               
         vSelectCursor := vSelectCursor || ' AND cb.DEPT = ' || department;  --this statement is incorrect
    END IF;

   OPEN IOCURSOR FOR vSelectCursor;
END;

The third if statement (vSelectCursor: = vSelectCursor || 'AND cb.DEPT =' || department;) does not work and shows me the message: 

java.lang.Exception: ORA-00904: "MIT": invalid identifier

Upvotes: 0

Views: 390

Answers (1)

DoctorMick
DoctorMick

Reputation: 6793

You need to wrap department is single quotes in the query you're generating as it is a string, where as the others are numeric:

IF department <> '-' THEN               
    vSelectCursor := vSelectCursor || ' AND cb.DEPT = ''' || department || '''';
END IF;

Upvotes: 2

Related Questions