Hi all,
I'm running into a 'PLS-00418: array bind type must match PL/SQL table row
type' error, but only when passing UTF8 data.
The details are as follows. I have a PL/SQL packaged function that looks like
this:
FUNCTION MSTInsUpdCIT(
PI_CGP_ID IN VARCHAR2
,PI_CBL_ID IN VARCHAR2
,PI_VCL_ID IN VARCHAR2
,PI_CIL_LOCATOR IN VARCHAR2
,PI_VCL_ID_TAB IN vcl_id_tab_type
,PI_CIL_LOCATOR_TAB IN cil_locator_tab_type
,PI_CIT_HITLISTLINE IN VARCHAR2
,PI_CIT_ALPHA_DATE IN VARCHAR2
,PI_CIT_START_DATE IN VARCHAR2
,PI_CIT_END_DATE IN VARCHAR2
,PI_CIT_SORT_DATE IN VARCHAR2
,PI_CIT_NUMERIC_DATE IN VARCHAR2
,PI_VIBC_ID_TAB IN VARCHAR2
,PI_CIBD_CODE_TAB IN CLOB
,PI_CIT_ABS_WORD_CNT IN NUMBER
,PI_CIT_TXT_WORD_CNT IN NUMBER
,PI_CIT_TYPE IN VARCHAR2
,PI_CIT_UNDATED_FLAG IN VARCHAR2
,PI_CIT_DELETE_FLAG IN VARCHAR2
,PI_ROUTER_PRIORITY IN NUMBER
,PI_CIT_COVER IN VARCHAR2
,PO_VCS_ID OUT NUMBER
,PO_DELETE_FLAG OUT varchar2
,PO_HOLD_STATUS OUT varchar2
,PO_CIT_EXISTS OUT NUMBER
,po_cit_id OUT VARCHAR2
,PO_MESSAGE_GROUP_ID OUT VARCHAR2) RETURN NUMBER;
The 'cil_locator_tab_type' is defined as:
TYPE cil_locator_TAB_TYPE is table of cnt_item_locators.cil_locator%type index
by binary_integer;
And cnt_item_locators.cil_locator is VARCHAR2(255 CHAR).
Database characterset is AL32UTF8.
In Perl, I'm defining an array reference as:
my $cil_locator_tab = ['2004322656 Vädskulturmuseet i Göorg',
'9789187484124 (Vädskulturmuseet i Göorg)',
'2004322656 Vädskulturmuseet i Göorg'];
which has UTF8 characters in it.
Next, I'm setting the multibyte flag via:
Encode::_utf8_on(@{$cil_locator_tab}[0]);
Encode::_utf8_on(@{$cil_locator_tab}[1]);
Encode::_utf8_on(@{$cil_locator_tab}[2]);
Finally, I define a param_hash as:
my $paramHash = {cbl_id => '23481',
vcl_id => '15',
cil_locator => '2004322656 Vädskulturmuseet i Göorg',
vcl_id_tab => $vcl_id_tab,
cil_locator_tab => $cil_locator_tab,
hitlistline => 'Viñ cielo, luna y arena. El espacio
Calchaquín el folclore argentino moderno~~~2003~Book Chapter',
alphadate => '2003',
startdate => '20030101',
sortdate => '20030101',
numdate => '20030101',
vibc_id_tab => '1',
cibd_code_tab => 'MLA',
cit_abs_word_cnt => '0',
cit_txt_word_cnt => '0',
cit_undated_flag => 'N',
cit_delete_flag => 'N',
router_priority => '5',
cover_flag => 'N',
};
And the binding of the datatype is here:
$sth->bind_param(':PI_CIL_LOCATOR_TAB', $paramHash->{cil_locator_tab},
{ora_type => ORA_VARCHAR2_TABLE, ora_maxarray_numentries => $maxrows});
Now, I call my PL/SQL, passing the paramHash, and I'm getting the PLS-00418
error described above.
So, I guess I need to understand how to map my bind variable to match my PL/SQL
table type.
This seems to work for non-UTF8 data, so, I'm not sure if this is a coding
error in my code, or some DBD/DBI bug?
Can anyone offer any insights?
Thanks,
-Mark
