Re: Problem with UTF8 and array binding....

[John Scoles]

 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: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