Re: Problem with UTF8 and array binding....
Hello,
It's been a looong time since I've been able to post tot his list. I miss Perl
more then my ex-wife. Anyway. There was a time when I had built this great Perl
app using DBI. I remember looping through the dB and inside a while loop
assigning the values from the cells to keys (scalars) which I could then use
anywhere I wanted in the HTML etc...
Haven't been able to find much example wise except this which is kinda close
from what I remember. It's for a flat-file though.
Here's the example I found:
while (($id,$name,$age)=$sth->fetchrow_array()){
print “id=$id name=$name age=$age\n”;
}
but, I don't need to print them out. I need to assign the values and hold them
in state until I call them elsewhere in the HTML, etc...
like:
while (($id,$name,$age)=$sth->fetchrow_array()){
id=$id;
name=$name;
age=$age;
}
Could someone get me going in the right direction. I have to re-build this app.
From: "Bobak, Mark"
To: John Scoles
Cc: "dbi-users@perl.org"
Sent: Fri, February 18, 2011 10:56:27 AM
Subject: RE: Problem with UTF8 and array binding
Tired the NVARCHAR2, it had no effect.
-Original Message-
From: John Scoles [mailto:sco...@pythian.com]
Sent: Friday, February 18, 2011 7:03 AM
To: Bobak, Mark
Cc: dbi-users@perl.org
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: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_DATEIN VARCHAR2
>> ,PI_CIT_NUMERIC_DATE IN VARCHAR2
>> ,PI_VIBC_ID_TAB IN VARCHAR2
>> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
>> ,PO_VCS_IDOUT 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:
>>
RE: Problem with UTF8 and array binding....
Then, I guess, the text was not utf8, you set the flag, but did you actually
convert the text to a UTF-8 format, perhaps an explicit encode('utf8',
$text) call?
Just a thought.
Also some more food for thought about utf8 and UTF-8
http://search.cpan.org/~dankogai/Encode-2.42/Encode.pm#UTF-8_vs._utf8_vs._UT
F8
Thanks,
Carl Furst
o/~ What a difference a byte makes... o/~
-Original Message-
From: Bobak, Mark [mailto:mark.bo...@proquest.com]
Sent: Friday, February 18, 2011 11:56 AM
To: Nelson, Erick [HDS]; dbi-users@perl.org
Subject: RE: Problem with UTF8 and array binding
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_DATEIN VARCHAR2
>,PI_CIT_NUMERIC_DATE IN VARCHAR2
> ,PI_VIBC_ID_TAB IN VARCHAR2
> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
> ,PO_VCS_IDOUT 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',
>
RE: Problem with UTF8 and array binding....
Tired the NVARCHAR2, it had no effect.
-Original Message-
From: John Scoles [mailto:sco...@pythian.com]
Sent: Friday, February 18, 2011 7:03 AM
To: Bobak, Mark
Cc: dbi-users@perl.org
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: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_DATEIN VARCHAR2
>> ,PI_CIT_NUMERIC_DATE IN VARCHAR2
>> ,PI_VIBC_ID_TAB IN VARCHAR2
>> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
>> ,PO_VCS_IDOUT 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'
RE: Problem with UTF8 and array binding....
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_DATEIN VARCHAR2
>,PI_CIT_NUMERIC_DATE IN VARCHAR2
> ,PI_VIBC_ID_TAB IN VARCHAR2
> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
> ,PO_VCS_IDOUT 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',
>
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_DATEIN VARCHAR2
>,PI_CIT_NUMERIC_DATE IN VARCHAR2
> ,PI_VIBC_ID_TAB IN VARCHAR2
> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
> ,PO_VCS_IDOUT 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
>
>
>
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: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_DATEIN VARCHAR2
,PI_CIT_NUMERIC_DATE IN VARCHAR2
,PI_VIBC_ID_TAB IN VARCHAR2
,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
,PO_VCS_IDOUT 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
RE: Problem with UTF8 and array binding....
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_DATEIN VARCHAR2
>,PI_CIT_NUMERIC_DATE IN VARCHAR2
> ,PI_VIBC_ID_TAB IN VARCHAR2
> ,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
> ,PO_VCS_IDOUT 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
>
>
>
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_DATEIN VARCHAR2
,PI_CIT_NUMERIC_DATE IN VARCHAR2
,PI_VIBC_ID_TAB IN VARCHAR2
,PI_CIBD_CODE_TABIN 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_COVERIN VARCHAR2
,PO_VCS_IDOUT 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
