Wrong Datatype for a Column During Create View/Table from PLSQL Function (Doc ID 1582137.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

During create view/table as select..., if one of the column is a character datatype returned from PLSQL function or user defined functions that are implemented via external procedures(extproc), the column always gets declared as varchar2(4000) irrespective of its length.

 

Is this expected?

 

Sample code :

 

CREATE OR REPLACE PACKAGE bam_test_pkg IS
  FUNCTION getstring(p_str IN char) RETURN char;
END bam_test_pkg;
/

CREATE OR REPLACE PACKAGE BODY bam_test_pkg IS
  FUNCTION getstring(p_str IN char) RETURN char IS
  BEGIN
   return p_str;
  END getstring;
END bam_test_pkg;
/

CREATE OR REPLACE VIEW BAM_TEST_V AS
SELECT '1' field1, '2' field2, '3' field3, bam_test_pkg.getstring('test') field4 FROM dual;

SQL> desc bam_test_v;

 FIELD1                                             CHAR(1)
 FIELD2                                             CHAR(1)
 FIELD3                                             CHAR(1)
 FIELD4                                             VARCHAR2(4000)


 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms