PL/SQL: numeric or value error: character string buffer too small %ROWTYPE

Question

I don't know if I am missing something but what I am doing is:

I have a function that returns a ROWTYPE

  FUNCTION myFunc(pChar CHAR) RETURN myTable%ROWTYPE AS
    myTable_rec myTable%ROWTYPE;
  BEGIN
    SELECT col1, col2, col3
    INTO myTable_rec.col1 
      , myTable_rec.col2
      , myTable_rec.col3
    FROM myTable
    WHERE col4 = pChar;

    RETURN(myTable_rec);
  END B001_03;

then in my procedure (which calls the function above), I declared:

myTable_rec myTable%ROWTYPE;

but when I call in the procedure:

...
myTable_rec := myFunc(someChar);
...

I get

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

Aren't the fields supposed to be FROM the same table and OF THE SAME datatype (as of my little understanding)?

EDIT: I tried to SELECT * and every works. I am definitely missing something here. I just don't know that it is.

why don't you use Varchar2 instead of Char?

Florin Ghita
– Florin Ghita

2013-07-16 05:38:32 +00:00
Commented Jul 16, 2013 at 5:38 — Florin Ghita
– Florin Ghita, Commented Jul 16, 2013 at 5:38

Florin Ghita · Accepted Answer · 2013-07-16 06:00:56Z

6

I bet the problem originates from using Char which is a fixed length string. Not sure where, but somewhere in your code you try to put a Char or varchar2 string of length N into a char of lengh M where M > N.

answered Jul 16, 2013 at 6:00

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Biki · Accepted Answer · 2016-02-19 07:21:26Z

3

I was getting this error while accessing Oracle SP through my .Net code. If someone is facing this error then for him/her specifying the size of the input / output parameter would help solving the issue.

Sample code goes like this :

OracleParameter oparamProjectId = mycom.Parameters.Add("p_open_flag", OracleDbType.Varchar2); oparamProjectId.Direction = ParameterDirection.Output; oparamProjectId.Size = 100;

answered Feb 19, 2016 at 7:21

Biki

2,6088 gold badges41 silver badges53 bronze badges

1 Comment

Vicente Hernández Over a year ago

Perfect! After a long time trying to find the error finally I know why. Thank you very much!

Collectives™ on Stack Overflow

PL/SQL: numeric or value error: character string buffer too small %ROWTYPE

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related