Problems When Executing Procedure

Posted on 2014-08-15
Last Modified: 2014-10-21

I am getting the following error when executing a procedure in toad:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "LEASEOPS01.PROC_ADVICE", line 23
ORA-06512: at line 15

This is what the beginning of the code looks like:

CREATE OR REPLACE procedure LEASEOPS01.proc_advice
 (blockno_var in varchar2, startdate_var in varchar2, enddate_var in varchar2, ENDTIME_VAR in varchar2, BLOCK_VAR in varchar2) is


    EXECUTE IMMEDIATE 'alter table LEASEOPS01.pay_advice nologging';
    execute immediate 'truncate table LEASEOPS01.pay_advice';
        -- -942 = table or view does not exist
        IF (SQLCODE <> -942) THEN
        END IF;


    execute immediate '
insert into

can someone please assist?
Question by:ernie_shah
    LVL 34

    Expert Comment

    That doesn't look like the entire procedure.  My guess is that the error is right where you cut it off, but without the entire procedure, that is hard to tell.

    Author Comment

    The entire procedure is extremely long.  When I create it in Toad, I am able to create it without errors, however, when I try to execute it I am getting the above error message.....

    Author Comment

    When I click on 'OK' to the message then I get the message Procedure Completed.  However, I does not insert anything into the table....
    LVL 34

    Expert Comment

    Within the code you have posted, I don't see any place where you could have exceeded a character buffer as the error message mentions.

    Line 23 is referenced which seems to be about where the insert statement is.  Since you cut off the procedure at that point, it is impossible to see what could have caused the error.

    The error you are receiving is a run time error, so I'm not surprised that the procedure created successfully.  When putting data into variables, there is no way for the compiler to predict the exact size of what is coming in.  I don't see how it could have caught this at compile time.
    LVL 12

    Accepted Solution

    Did you probably declare a variable as varchar instead of varchar(200) (or whatever size you need)? This would declare it as varchar(1) which is too small for most values.

    The error message states that a value is longer than your defined size in the target table or the variable into which it is selected.
    LVL 73

    Expert Comment

    we don't need the entire procedure (but it would help)

    we do need at least the entire execute immediate command since that's what failed
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    The error tells you what the problem is:
    You are trying to put a value into a variable that is too big to fit.

    Does the execute immediate use an INTO clause to take a value and assign it to a variable?

    Below the insert, look at variable assignments.

    For example:
    SQL> declare
      2          myvar varchar2(5);
      3  begin
      4          --assigning 6 characters to a variable that can only hold 5
      5          myvar := 'abcdef';
      6  end;
      7  /
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 5

    Open in new window


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now