[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problems When Executing Procedure

Posted on 2014-08-15
8
Medium Priority
?
241 Views
Last Modified: 2014-10-21
Hi

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


begin

    begin  
   
    EXECUTE IMMEDIATE 'alter table LEASEOPS01.pay_advice nologging';
         
    execute immediate 'truncate table LEASEOPS01.pay_advice';
   
    EXCEPTION
       WHEN OTHERS THEN
        -- -942 = table or view does not exist
        IF (SQLCODE <> -942) THEN
          RAISE_APPLICATION_ERROR(-20000, SQLERRM);
        END IF;

    END;

begin
    execute immediate '
insert into

can someone please assist?
0
Comment
Question by:ernie_shah
7 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40263790
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.
0
 

Author Comment

by:ernie_shah
ID: 40263818
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.....
0
 

Author Comment

by:ernie_shah
ID: 40263839
When I click on 'OK' to the message then I get the message Procedure Completed.  However, I does not insert anything into the table....
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 35

Expert Comment

by:johnsone
ID: 40263977
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.
0
 
LVL 12

Accepted Solution

by:
HugoHiasl earned 2000 total points
ID: 40264001
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40264048
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40264195
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  /
declare
*
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

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month18 days, 3 hours left to enroll

830 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