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.
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"))
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!
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
ALTER TABLE TableB ADD TableAValue VARCHAR2(128) AS ( CAST( MyFunction(CompanyID) AS VARCHAR2(128) ) )