CODEWITHSUNDEEP
oracle-databaseplsql
user3738332
user3738332

Reputation: 41

passing table name column name and value of a column in oracle databsase

code in jsp page

 CallableStatement cs ;
cs=conn.prepareCall("{call held1(?,?,?)}"); 
cs.setString(1,"'"+session.getAttribute("roll")+"'"); // sending session to procedure .reciving this in s variable
cs.setString(2, "1");// sending serial nos as string and wanna recieve in sno. variable
cs.setString(3, "Monday"); // sending table name as string and wanna recieve it in tab variable
cs.executeUpdate();

"'"+session.getAttribute("roll")+"'" want to recive this value in s argument "1"

code in oracle database

procedure

create or replace procedure "HELD1"
(s  VARCHAR2,sno  VARCHAR2,tab  VARCHAR2)
is
l_col_name varchar2(30);
begin
execute immediate
'SELECT SUBJECTCODE into l_col_name FROM '||tab||' WHERE SERIALNUMBER ='''||sno||'''';

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

Error Message:

java.sql.SQLException: ORA-00905: missing keyword ORA-06512: at "ROHIT.HELD1", line 8 ORA-06512: at line 1

Upvotes: 0

Views: 1214

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Your SELECT INTO.. portion of dynamic SQL execution goes like below.

execute immediate
'SELECT SUBJECTCODE  FROM '||tab||' WHERE SERIALNUMBER ='''||sno||''''
into l_col_name;

The Query execution is dynamic to SQL Engine.. but still the data is brought back to the PL/SQL engine. So, the fetching part happens after SQL execution only.

You are prone to SQL*Injection. Atleast use bind variables for the where clause.

execute immediate
    'SELECT SUBJECTCODE  FROM '||tab||' WHERE SERIALNUMBER = :sno'
    into l_col_name
    using sno;

execute immediate 
'UPDATE dprofile '||
   ' SET '|| l_col_name || ' = ' || l_col_name || ' + 1 '||
   ' WHERE  Rollno = :s'
using s;

And finally, in your jdbc portion.

cs.setString(1,session.getAttribute("roll"));

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

execute immediate 'SELECT SUBJECTCODE into l_col_name FROM '||tab||' WHERE SERIALNUMBER ='''||sno||'''';

The INTO clause should be after the dynamic sql.

EXECUTE IMMEDIATE <dynamic sql> INTO variable;

Do not get confused with SELECT .. INTO. You are dealing with dynamic sql. First the query is executed and then the returned values are assigned to the respective variables. There is context switching involved between PL/SQL and SQL engines.

Upvotes: 0

Related Questions