Tired the NVARCHAR2, it had no effect. -----Original Message----- From: John Scoles [mailto:[email protected]] Sent: Friday, February 18, 2011 7:03 AM To: Bobak, Mark Cc: [email protected] Subject: Re: Problem with UTF8 and array binding....
On 17/02/2011 4:38 PM, Bobak, Mark wrote: Sorry Mark I saw that PI_CIBD_CODE_TAB was a clob so that is where I though you where having your problem. There is such a thing as a nvarchar never used it myself perhaps that will help. > 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:[email protected]] > Sent: Thursday, February 17, 2011 4:10 PM > To: [email protected] > 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 >> >> >> > >
