Wrong Datatype for a Column During Create View/Table from PLSQL Function
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version 184.108.40.206 and later
Information in this document applies to any platform.
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;
CREATE OR REPLACE PACKAGE BODY bam_test_pkg IS
FUNCTION getstring(p_str IN char) RETURN char IS
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;
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