Oracle PL SQL Function return type size VARCHAR2(32767) incorrectly?

struggling with a PL/SQL Function here (new to me) – and what makes this weird is it appears to work on Oracle XE (local) and Oracle 12c – but when applied to AWS RDS Oracle instances it does not work.

I have a function which is returning a query column value from another table, and I am trying to add a function derived column to a new table.

The database is currently empty as it is being designed.

TableA

CompanyID      Integer
VALUE          VARCHAR2(128)

Function – This works executing in SQL Developer

create or replace FUNCTION MyFunction
    (
        CompanyID IN INT 
    ) RETURN VARCHAR2
    DETERMINISTIC
    IS result VARCHAR2(128);
    BEGIN
        SELECT "VALUE" INTO result
        FROM "TableA"
        WHERE "CompanyID" = CompanyID;
        RETURN result;
    END MyFunction;

Next – Alter TableB to add column referencing MyFunction

ALTER TABLE "TableB" ADD "TableAValue" VARCHAR2(128) AS (MyFunction("CompanyID"))

Observe error

ORA-12899: value too large for column "TableAValue" (actual: 32767, maximum: 128)
12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"

It’s working OK in Oracle XE as mentioned – but when trying to create my database on AWS RDS Oracle, it’s like it cannot determine that the size of column TableA.VALUE is only 128 in size, and treating it as maximum (for extended string size settings).

Am I missing something in the function that can force the DB Engine to see that it is appropriately sized? I have tried casting the SELECT "VALUE" to VARCHAR2(128) as well but that made no difference.

A bit confused, appreciate any insight!

>Solution :

Don’t use a function and don’t declare non-deterministic functions as DETERMINISTIC when they are not.

Instead, create a view:

CREATE VIEW tableb_view AS
SELECT b.*,
       a.value AS tableavalue
FROM   tableb b
       LEFT OUTER JOIN tablea a
       ON b.id = a.id

If you really want to use your function (DON’T) then you can specify the size using CAST:

ALTER TABLE TableB ADD TableAValue VARCHAR2(128) AS (
  CAST( MyFunction(CompanyID) AS VARCHAR2(128) )
)

Leave a Reply