Home » SQL & PL/SQL » SQL & PL/SQL » procedure(to increase buffer size)
procedure(to increase buffer size) [message #218651] Fri, 09 February 2007 03:10 Go to next message
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure pro_emp1(
  2  i_client_no in number,
  3  o_deptno out number,
  4  o_job out varchar2,
  5  o_name out varchar2,
  6  o_sal out number) is
  7  begin
  8  select deptno, job, name, sal into o_deptno, o_job, o_name, o_sal from emp1
  9   where client_no=i_client_no;
 10  exception
 11   when no_data_found then
 12   o_deptno:=null;
 13* end;
SQL> /

Procedure created.

SQL> exec :v_client_no:=2

PL/SQL procedure successfully completed.

SQL> exec pro_emp1(:v_deptno, :v_job, :v_name, :v_sal, :v_client_no)
BEGIN pro_emp1(:v_deptno, :v_job, :v_name, :v_sal, :v_client_no); END;

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.PRO_EMP1", line 8
ORA-06512: at line 1

can u please tell me how could i increase my buffer size.
if i m providing buffer size on datatype, it shows error.

please provide me ur valuable opinion



[Updated on: Fri, 09 February 2007 03:12]

Report message to a moderator

Re: procedure(to increase buffer size) [message #218653 is a reply to message #218651] Fri, 09 February 2007 03:21 Go to previous message
Messages: 402
Registered: July 2005
Senior Member
Are these out variable (:v_deptno, :v_job, :v_name, :v_sal) that are defined have same size as that in table.

i.e if job column in your table emp1 is of size 10 then your :v_job variable should be equal or more than this size.

Hope this helps.

Previous Topic: update statement
Next Topic: DB refresh Steps or Documents
Goto Forum:

Current Time: Thu Aug 17 08:51:43 CDT 2017

Total time taken to generate the page: 0.06464 seconds