2

Hope you're doing well I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL. What I've written so far is :

declare

   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_new_stname varchar2(50);

begin

  sql_stmt := 'update student 
               set student_name = ''pantea'' where student_id :1 ' ||
              'returning student_name into :2';
  execute immediate sql_stmt
  using v_student_id
  returning into v_new_stname;

     dbms_output.put_line('New student name is :' || v_new_stname);

end;

when I want to run the code I see this error :

ORA-06502:Numeric or value error:Character string buffer too small.

I'm 80 percent sure that the problem might be in this part of the code

set student_name = ''pantea''

but I don't know how to solve it and I could not also find it on oracle books I know how to handle this in T/SQL but have no idea how to handle this in PL/SQL.

  • 2
    I see no reason for dynamic SQL. I advise you don't make things too complicated for yourself. Also, the q'[]' notation helps when you need to woute single quotes. – Michael Kutz Jun 10 at 9:20
5

You forgot the comparison operator for the WHERE clause

If you really want to use dynamic SQL (which is not needed for a simple UPDATE statement like that), then you should also pass all values as parameters, not just the student ID.

declare
   sql_stmt     varchar2(100);
   v_student_id number := 1;
   v_name varchar2(20) := 'pantea';
   v_new_stname varchar2(50);
begin
  sql_stmt := 'update student set student_name = :1 '|| 
              'where student_id = :2 '|| 
              'returning student_name into :3';

  execute immediate sql_stmt
    using v_name, v_student_id
    returning into v_new_stname;

  dbms_output.put_line('New student name is: ' || v_new_stname);
end; 
/ 

The "string buffer too small" seems to happen when you are continuing a string literal (constant) over multiple lines. So it's better to open and close the string constants on each line and concatenate them using || rather than embedding a new line in the string as you did.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.