select value from NLS_DATABASE_PARAMETERS where parameter in ('NLS_RDBMS_VERSION', >'NLS_CHARACTERSET',  'NLS_LENGTH_SEMANTICS' )<

ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
/
create type gpu_type_numbers AS TABLE OF NUMBER(12)
/

create type gpu_type_varchar2_tab as table of VARCHAR2(4000) 
/
create table gpu_ident2(ITEM_SHIP_ID number(12,0) not null, layout_short CLOB)
/
insert into gpu_ident2(ITEM_SHIP_ID,layout_short)
values(44413615,
'Yaskawa VSD Spare Parts kit: Power Cell Control Board Isolation Board Relay Board Power Supplies 5/15Vdc and 24Vdc Fan Detect Board Cooling Fan Air Filter (Small) as required Air Filter (Large) Digital Operator Three main fuses.
POWER CELL MV2 330A  3FUSE,  Qty in VFD 6EA, Price book PN UUX001104,  USD$45391.05,  Qty in KIT 1EA,  USD$45391.05.
FAN 200-277VAC 2.3A S3G400-KC22-51, Qty in VFD 6EA, Price book PN UUX001109,  USD$2835, Qty in KIT 1EA,  USD$2,835.00.
FILTER CASSETTE 398 x 898 FLGS31050-B, Qty in VFD 10EA, Price book PN UUX001111,  USD$1513.35, Qty in KIT 2EA,  USD$1513.35.
CONTROL BOARD JEBC-61304-INV 30400- S0100, Qty in VFD 1EA, Price book PN  100-064-008,  USD$3801.60, Qty included in KIT  1EA,  USD$3801.60. RELAY BOARD 120V 7910161-91321, Qty in VFD 1EA, Price book PN 100-088-860,  USD$1800.90, Qty in KIT  1EA, USD$1800.90. FAN DTECT BRD JEBC-61703-0 7910161-70300, Qty in VFD 1EA,  Price book PN 100-064-013,   USD$540, Qty included in KIT  1EA,  USD$540.00. ISOLATION BOARD JEBC-61701, Qty in VFD 1EA, Price book PN 100-064-429,  USD$2070.90, Qty in KIT 1EA, USD$2070.90. POWER SUPPLY 200V +5/+-15VDC PMC30E-2-C, Qty in VFD  1EA, Price book PN 300-034-594,  USD$399.60, Qty in KIT  1EA,  USD$399.60. POWER SUPPLY 200V 24V PBA50F-24-C, Qty in VFD 1EA, Price book PN 300-034-595, USD$365.85, Qty in KIT 1EA,  USD$365.85. OPERATOR, JVOP-180 MV2, LCD, CE PRG 0102, Qty in VFD 1EA, Price book PN 100-092-072,  USD$260.55, Qty in KIT 1EA,  USD$260.55. Unit prices above reflect individual purchases.')
/
commit
/
select dbms_lob.getlength(LAYOUT_SHORT) from gpu_ident2;

create package GPU_PCK2 as
TYPE gpu_ref_cursor  IS REF CURSOR;
function ident_desc2(p_ident  IN NUMBER,p_nls_id IN NUMBER, p_proj_id IN VARCHAR2 default null) RETURN VARCHAR2;
function hmm_1(p_col_list IN gpu_type_varchar2_tab, p_item_ship_ids IN gpu_type_numbers, p_nls_id IN number) return gpu_ref_cursor;
end GPU_PCK2;
/
create or replace package body GPU_PCK2 as
function ident_desc2(p_ident  IN NUMBER,p_nls_id IN NUMBER, p_proj_id IN VARCHAR2 default null) RETURN VARCHAR2
is
  return_desc VARCHAR2(4000);
  v_tmp       VARCHAR2(3000);
  v_layout_short    gpu_ident2.layout_short%TYPE;
  CURSOR ccl IS SELECT layout_short FROM gpu_ident2 ;
begin
    OPEN ccl;
    FETCH ccl INTO v_layout_short;
    CLOSE ccl;
    v_tmp := dbms_lob.substr(v_layout_short, 3000,1);
    return_desc  := v_tmp;
    return return_desc;
end ident_desc2;
function hmm_1(p_col_list IN gpu_type_varchar2_tab, p_item_ship_ids IN gpu_type_numbers, p_nls_id IN number) return gpu_ref_cursor
is
    v_cur gpu_ref_cursor;
begin
    open v_cur for  'select ish.item_ship_id, IDENT_SHORT_DESC from GPU_ISH_EXPED_5 ish,' ||    
    'TABLE(CAST(:1 AS gpu_type_numbers)) ish_ids' || 
    ' where ish.item_ship_id = ish_ids.COLUMN_VALUE'
    USING p_item_ship_ids;    
    return v_cur;
end hmm_1;
end GPU_PCK2;
/

create or replace view GPU_ISH_EXPED_5("ITEM_SHIP_ID", "IDENT_SHORT_DESC") AS 
  SELECT item_ship_id,
           GPU_PCK2.ident_desc2(1, 1) IDENT_SHORT_DESC
           from gpu_ident2/

SELECT C.name, c.nls_length_semantics
FROM DBA_PLSQL_OBJECT_SETTINGS C
WHERE  c.name in ('GPU_PCK2', upper('gpu_type_varchar2_tab'))
ORDER BY C.name

set serveroutput on;
declare
v_item_ship_id      gpu_ident2.item_ship_id%type;
v_IDENT_SHORT_DESC  GPU_ISH_EXPED_5.IDENT_SHORT_DESC%type;
l_cursor            GPU_PCK2.gpu_ref_cursor;
begin
  l_cursor := GPU_PCK2.hmm_1(gpu_type_varchar2_tab('ITEM_SHIP_ID', 'IDENT_SHORT_DESC'), gpu_type_numbers(44413615), 1);
  fetch l_cursor into v_item_ship_id, v_IDENT_SHORT_DESC;
  dbms_output.put_line(v_IDENT_SHORT_DESC);
  close l_cursor;
exception
when others then
  close l_cursor;
  raise;
end;
/
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

select dbms_lob.getlength(LAYOUT_SHORT) from gpu_ident2;

select length(v), lengthb(v) from (
select GPU_PCK2.ident_desc2(1,1) v from dual);

function hmm_2(p_col_list IN gpu_type_varchar2_tab, p_item_ship_ids IN gpu_type_numbers, p_nls_id IN number) return gpu_ref_cursor
is
    v_cur gpu_ref_cursor;
begin
    open v_cur for  'select ish.item_ship_id, IDENT_SHORT_DESC from GPU_ISH_EXPED_7 ish,' ||    
    'TABLE(CAST(:1 AS gpu_type_numbers)) ish_ids' || 
    ' where ish.item_ship_id = ish_ids.COLUMN_VALUE'
    USING p_item_ship_ids;    
    return v_cur;
end hmm_2;

create or replace view GPU_ISH_EXPED_7("ITEM_SHIP_ID", "IDENT_SHORT_DESC") AS 
  SELECT item_ship_id,
           GPU_PCK2.ident_desc2(1, 1, null) IDENT_SHORT_DESC
           from gpu_ident2;

SQL> declare
  2  v_item_ship_id      gpu_ident2.item_ship_id%type;
  3  v_IDENT_SHORT_DESC  GPU_ISH_EXPED_7.IDENT_SHORT_DESC%type;
  4  l_cursor            GPU_PCK2.gpu_ref_cursor;
  5  begin
  6    l_cursor := GPU_PCK2.hmm_2(gpu_type_varchar2_tab('ITEM_SHIP_ID', 'IDENT_SHORT_DESC'), gpu_type_numbers(44413615), 1);
  7    fetch l_cursor into v_item_ship_id, v_IDENT_SHORT_DESC;
  8    dbms_output.put_line(v_IDENT_SHORT_DESC);
  9    close l_cursor;
 10  exception
 11  when others then
 12    close l_cursor;
 13    raise;
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
ORA-06512: at line 1
ORA-06512: at line 7

>ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
>desc  GPU_ISH_EXPED_5;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM_SHIP_ID                              NOT NULL NUMBER(12)
 IDENT_SHORT_DESC                                   VARCHAR2(4000)

SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

SQL> desc  GPU_ISH_EXPED_5;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM_SHIP_ID                              NOT NULL NUMBER(12)
 IDENT_SHORT_DESC                                   VARCHAR2(4000 CHAR)

>alter package GPU_PCK2 compile NLS_LENGTH_SEMANTICS=CHAR;
SELECT C.name, C.type, c.NLS_LENGTH_SEMANTICS
FROM DBA_PLSQL_OBJECT_SETTINGS C
WHERE name in ('GPU_PCK2')
ORDER BY C.name;
GPU_PCK2	PACKAGE	CHAR
GPU_PCK2	PACKAGE BODY	CHAR

 return_desc VARCHAR2(4000 VARCHAR2);
  v_tmp       VARCHAR2(3000 VARCHAR2);

QL> select column_name, data_length, char_used from user_tab_columns where table_name = 'GPU_ISH_EXPED_5';

COLUMN_NAME
                 DATA_LENGTH C
-------------------------------------------------------------------------------------------------------------------------------- ----------- -
ITEM_SHIP_ID
                          22
IDENT_SHORT_DESC
                        4000 C

 open v_cur for  'select ish.item_ship_id, GPU_PCK2.ident_desc2(1,1) IDENT_SHORT_DESC from GPU_ISH_EXPED_5 ish,' ||    
    'dual --TABLE(gpu_type_numbers(44413615)) ish_ids';

select value from NLS_DATABASE_PARAMETERS where parameter in ('NLS_RDBMS_VERSION', 'NLS_CHARACTERSET',  'NLS_LENGTH_SEMANTICS' )

select  version from  v$instance
12.1.0.2.0
>select value from NLS_DATABASE_PARAMETERS where parameter in ('NLS_RDBMS_VERSION', 'NLS_CHARACTERSET',  'NLS_LENGTH_SEMANTICS' )
12.1.0.2.0
CHAR
AL32UTF8
>ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
/

> select  ish.item_ship_id,
IDENT_SHORT_DESC
from  GPU_ISH_EXPED_5 ish,
TABLE(SYS.ODCIDATELIST(SYSDATE))
where ish.item_ship_id = 44413615
/

Error starting at line : 22 in command -
 select  ish.item_ship_id,
IDENT_SHORT_DESC
from  GPU_ISH_EXPED_5 ish,
TABLE(SYS.ODCIDATELIST(SYSDATE))
where ish.item_ship_id = 44413615
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small