Why type of a view column does not match corresponding function result type?

user_2774914 0
Viewed 222

Suppose having a function my_pkg.get_prop_of, that returns value of type CHAR(5). Also let's declare a view my_view with one column prop, gaining its value from this function call.

create or replace package my_pkg AS

  subtype short_string is CHAR(5);

  function get_prop_of(pi_name char)
    return short_string;    
end;    

create or replace package body my_pkg AS

  function get_prop_of(pi_name char)
    return short_string is
  begin
    return substr(pi_name || 'abcde', 1, 5);
  end;    
end;
---

create or replace view my_view as
select 
       my_pkg.get_prop_of('x') as prop
  from dual;

Could you explain why data dictionary shows that column type as VARCHAR2(4000), what is the purpose of such behaviour?

select table_name, data_Type, data_length
  from all_tab_cols
 where table_name = 'MY_VIEW';

----------------------------------------------------
 TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_LENGTH
----------------------------------------------------
   MY_VIEW  |  PROP       |  VARCHAR2 |    4000

Looks like the Data dictionary contains the required data to let compiler apply it at the view compilation time. But somewhy it does not go that way.

select object_name, package_name, data_type, data_length, in_out
  from all_arguments
 where object_name = 'GET_PROP_OF';

-----------------------------------------------------------------
  OBJECT_NAME  | PACKAGE_NAME | DATA_TYPE | DATA_LENGTH | IN_OUT
-----------------------------------------------------------------
  GET_PROP_OF  |    MY_PKG    |   CHAR    |      5      |  OUT     <- return type
  GET_PROP_OF  |    MY_PKG    |   CHAR    |             |  IN

I would like to get in a clear way a matching of column type and function result type.

It is known that hard casting like CAST(val AS CHAR(5)) could temporarily solve the problem, but is there a more elegant way, which also handles case of changing the function return type without need of changing CAST expression everywhere in the columns, calling that function.

column-types plsql view type-conversion oracle
2 Answers

Because when VIEW is created, Oracle does not know the max value of the function result that is returned from the function but it knows the data type.

Also, The fact is --> any returned value from function can not contain the data precision.

You can still achieve what you want by controlling the data type and precision in VIEW query using CAST as following:

SQL>
SQL> CREATE OR REPLACE VIEW MY_VIEW AS
  2      SELECT
  3          CAST(MY_PKG.GET_PROP_OF('x') AS VARCHAR2(5)) AS PROP
  4      FROM
  5          DUAL;

View created.

SQL> DESC MY_VIEW;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROP                                               VARCHAR2(5)

SQL>

Cheers!!

user_115656290
user_108310940
Related