Sorry, but I'm not sure I understand the suggestion. NCLOB is (as far as I can see), not in the picture...
Datatype of table is VARCHAR2(255 CHAR).... The PL/SQL table type is table of table_name.column_name%type indexed by binary_integer IN TYPE is named type defined as the table type mentioned above... I don't see how/where NCLOB fits into the picture? -Mark -----Original Message----- From: John Scoles [mailto:sco...@pythian.com] Sent: Thursday, February 17, 2011 4:10 PM To: dbi-users@perl.org Subject: Re: Problem with UTF8 and array binding.... On 17/02/2011 3:05 PM, Bobak, Mark wrote: Perhaps you have to declare you in type as a NCLOB?? Cheers John > 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 > > >
