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

oracle plsql procedure
Rey Libutan picture Rey Libutan · Jul 16, 2013 · Viewed 58.8k times · Source

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.

Answer

Florin Ghita picture Florin Ghita · Jul 16, 2013

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.

Related questions

ORA-00900: invalid SQL statement- when run a procedure in oracle 10g

I am using Oracle 10g database and trying to run a procedure using SQL commands. create or replace procedure "exam" is begin DBMS_OUTPUT.PUT_LINE('Test'); end; Then click on Run button. It shows: "procedure created". When I try …

Read More
PL/SQL: how do I prompt user input in a procedure?

This is a question about a small part of a large project I'm doing. I tried the following but I just get the two errors below it: SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE HELLO AS DECLARE variable1 NUMBER(1); variable2 …

Read More
Can I execute a procedure with default null parameters?

I recently created a procedure that is defined like this: create or replace PACKAGE pkg_dml_legal_transactions AS PROCEDURE spm_update_court_cost( p_court_state IN legal_court_cost.state%TYPE, p_tran_code IN legal_court_cost.…

Read More