Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1359
  • Last Modified:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

When I execute a Oracle procedure I get this ORA-06502 error.
The string size that I am trying to output (on DBMS_OUTPUT.PUTLINE) is less than 2000 character. Is there any way to avoid this error?

I plan to create a script that will create a table with field names that are same as the date value strings of a certain day each week between the dates  "starteDate" and "endDate ".

I get error in the test process... when I run the procedure on SQL prompt:
SQL>  set serveroutput on;
SQL> set serveroutput on buffer 2560000;
SQL>  begin
  2   myProc ;
  3   end;
/

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 3
CREATE OR REPLACE PROCEDURE myProc IS
  starteDate   Date;
  endDate      Date;
col_string   VARCHAR2(2000);
s_sql_insert VARCHAR2(2000);
BEGIN

-- Get the starteDate and endDate values via sql.
s_sql_insert := 'CREATE TABLE MyTab ( f1 VARCHAR2(256), f2 VARCHAR2(256), ';

  loopDate := starteDate;
  WHILE starteDate <= endDate LOOP
    col_string := col_string || to_char(loopDate) || 'VARCHAR2(256),';
    loopDate := loopDate + 7;
  END LOOP;

  s_sql_insert := s_sql_insert || col_string || ')';

  DBMS_OUTPUT.PUT_LINE('s_sql_insert : ' || s_sql_insert ); -- Getting ERROR


--  EXECUTE IMMEDIATE s_sql_insert;
 

END;

Open in new window

0
toooki
Asked:
toooki
1 Solution
 
sdstuberCommented:
your loop never ends

I think you want to check your loopDate, not your StarteDate

loopDate := starteDate;
  WHILE loopDate<= endDate LOOP
    col_string := col_string || to_char(loopDate) || 'VARCHAR2(256),';
    loopDate := loopDate + 7;
  END LOOP;

0
 
toookiAuthor Commented:
Thanks a lot and that worked. I changed the while statement..
Many thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now