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" <[email protected]>
To: John Scoles <[email protected]>
Cc: "[email protected]" <[email protected]>
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:[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
>>
>>
>>
>
>
