Method to pass arguments to a procedure in oracle database

Question

code in jsp page

cs=conn.prepareCall("{call held('"+session.getAttribute("roll")+"')}");
 cs.executeUpdate();

in oracle database procedure is as

create or replace procedure "Held"
(s in Varchar2)
 l_col_name varchar2(30);
begin
 SELECT SUBJECTCODE 
 into l_col_name
 FROM table02 
 WHERE SERIALNUMBER = '1';
 execute immediate
 'UPDATE TABLE01 SET '|| l_col_name || ' = '
 || l_col_name || ' + 1 WHERE Rollno = s'
 ;
end;

the following is the error

java.sql.SQLException: ORA-00904: "S": invalid identifier ORA-06512: at "ROHIT.HELD", line 12 ORA-06512: at line 1

please rectify it

i am trying to take the value of session ie from code in jsp and trying to use it in oracle database .... i have used s as variable to store that value and used it in where clause

suggest a solution

Answer 1

I don't know if the jsp side is correct, but you clearly have a bug in your procedure.

When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid.

Here, your statement WHERE Rollno = s is understand as 'where column rollno is equal to column s', but the column s doesn't exist. I suppose you just have to move s out of the string.

execute immediate
 'UPDATE TABLE01 SET '|| l_col_name || ' = '
 || l_col_name || ' + 1 WHERE Rollno = ''' || s || ''''
;

• user3738332: yes u are right s is not a column.......................but i need to retrive the value of session through which i have logged in ..the session is actually a value of column roll nos .....so now how should i proceed
• NiziL: @user3738332 See my edit, you wasn't using the s argument but a raw character ;)