Thanks for the suggestion, Erick. In my case, I already had NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 (which is my db character set).
I didn't have anything set for ORA_NLS, but I tried setting it to $ORACLE_HOME/nls/data (which is my guess as to the 11.2 equivalent of the directory you had specified for 9.2). It didn't make any difference. On another note, I've discovered that if we set the UTF8 flag on the Perl string, it gives me the PLS-418 error. If we do not set the UTF8 flag, it works fine, data does not get munged, and we are good. But, my developers are telling me that's wrong, since the strings *are*, in fact, UTF8, they *should* have the UTF8 flag set. So, I have a workaround, albeit one that makes my developers uncomfortable. -Mark -----Original Message----- From: Nelson, Erick [HDS] [mailto:erick.nel...@hdsupply.com] Sent: Thursday, February 17, 2011 5:19 PM To: dbi-users@perl.org Subject: RE: Problem with UTF8 and array binding.... I had this problem quite awhile ago with an old Oracle 9.2 database. I don't remember why but the solution involved setting a couple of environment vars. Looking back at those ancient, no longer used, problems I see.... $ENV{NLS_LANG} = "american_america.we8iso8859p1"; $ENV{ORA_NLS} = "$ENV{ORACLE_HOME}/ocommon/nls/admin/data"; -----Original Message----- From: John Scoles [mailto:sco...@pythian.com] Sent: Thursday, February 17, 2011 1: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 > > >
