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:[email protected]]
Sent: Thursday, February 17, 2011 1: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
>
>
>
