CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Ciaran Gallagher
Ciaran Gallagher

Reputation: 4020

(Oracle SQL Stored Proc) Insert a single character into a table -- Value too large for columns

As far as I've read, in declaring a stored procedure it is not possible to specify the length of a CHAR, and so it appears to default to 200 characters in length. This is a problem because my table only accepts a CHAR with 1 character.

So even though I'm only passing in a single character to the stored procedure, I still get this error: 

ORA-12899: value too large for columns ... (actual: 200, maximum: 1)

Here's what I have: 

CREATE OR REPLACE
PACKAGE BODY TESTPACKAGE AS

      PROCEDURE Testing(myChar IN CHAR)
      IS
      BEGIN
        UPDATE my_table SET my_column = myChar WHERE [some condition];
      END Testing;

END TESTPACKAGE;

My package declaration:

PROCEDURE Testing(myChar IN CHAR);

The real stored procedure obviously has more parameters and logic, but I've stripped it down to the problem area.

What's the best way to deal with this problem, without resorting to altering my table to accept CHARS with 200 characters (which would be totally unnecessary)?

Thanks

Upvotes: 2

Views: 1118

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

I can only generate this error is the variable being passed in is defined as char(200):

declare
  val char(200);
begin
  val := 'b';
  testpackage.testing(val);
end;
/

ORA-12899: value too large for column ... (actual: 200, maximum: 1)

If val is declared as char(1) or a string literal is passed then it doesn't error:

declare
  val char(1);
begin
  val := 'b';
  testpackage.testing(val);
end;
/

anonymous block completed

begin
  testpackage.testing('b');
end;
/

anonymous block completed

So it doesn't appear to default at all. If you can't change how the passed variable is declared, you can trim it:

      PROCEDURE Testing(myChar IN CHAR)
      IS
      BEGIN
        UPDATE my_table SET my_column = trim(myChar)
        WHERE [some condition];
      END Testing;

Then this works:

declare
  val char(200);
begin
  val := 'b';
  testpackage.testing(val);
end;
/

anonymous block completed

This will still fail if the caller tries to pass more than one character, but that's probably what you want.

Upvotes: 1

Related Questions