Problems When Executing Procedure


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
ernie_shahAuthor Commented:
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.....
ernie_shahAuthor Commented:
When I click on 'OK' to the message then I get the message Procedure Completed.  However, I does not insert anything into the table....
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
slightwv (䄆 Netminder) Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.