DBD::Oracle Character Encoding Problem?

Upgrading from Interwoven 5.5.2 to 6.5, I'm testing my old workflows and hit an error that I'm having a hard time figuring out. Perhaps someone here has some gems of wisdom that might be the inspiration I need.

We store owner and approver information for each branch in an Oracle database. When an editor wants to submit content to be posted to the site, our workflow looks up the approvers in the Oracle database and creates the appropriate tasks in those user's queues. It also sends each user an e-mail, and the database has each user's e-mail address in it.

The problem I have is that when I look up the user's e-mail address, the query dies with an Oracle error:
PLS-00561: character set mismatch on value for parameter 'P_TCSID'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 52 in 'BEGIN :cursor := WAM_IWV_PC_PM.FN_GET_USER_BY_TCSID(<*>:tcsid); END;')'

Of course, the user ID is coming from the Oracle database originally, so I don't understand why there is a character set issue.

I added the following code immediately in front of the query code to attempt to diagnose the problem:
if ($id eq "remp022") {
printf($id." matches!\n");
} else {
printf($id." does not match remp022\n");
}
#$id = "remp022";

When I uncomment the $id override statement, the database look ups work correctly, though since I've overriding it with "remp022", it just looks up the same user ID every time. Here's what I get:
remp022 matches!
TCS ID is remp022
remp022 matches!
TCS ID is remp022
remp012 does not match remp022
TCS ID is remp022
remp016 does not match remp022
TCS ID is remp022
remp021 does not match remp022
TCS ID is remp022

Comment out the $id override, and the code fails:
remp022 matches!
TCS ID is remp022
PLS-00561: character set mismatch on value for parameter 'P_TCSID'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 52 in 'BEGIN :cursor := WAM_IWV_PC_PM.FN_GET_USER_BY_TCSID(<*>:tcsid); END;')'

As near as I can tell, PERL can correctly compare the database Stored Procedure result with it's own PERL string, and the Oracle stored procedure can accept the PERL string, but the Oracle stored procedure will not accept the results of another Oracle Stored Procedure that are passed to it through PERL.

Anybody have any thoughts?

Find more posts tagged with

Comments

Adam Stoller

What does the code look like *after* the snippet you posted?

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

mike_jaixen

sub getUserByTcsId{
my ($self, $id) = (shift, shift);
my $user;
my $cn = $self->getConn();
if ($id eq "remp022") {
printf($id." matches!\n");
} else {
printf($id." does not match remp022\n");
}
#$id = "remp022";
my $stmt = $cn->prepare($self->getFunction("getUserByTcsId"));
$stmt->bind_param(":tcsid", $id);
$stmt->bind_param_inout(":cursor", \$user, 0, $self->getRS());
printf("TCS ID is ".$id."\n");
$stmt->execute;
my @row = $user->fetchrow_array;
$self->closeCursor(\$user);
return FallingOnline: Smiley Very Happy

irBuilder: Smiley Very Happy

b::User->new(id=>$row[0],
tcsid=>$row[1],
name=>&fixName($row[2]),
email=>$row[3]);
}

Adam Stoller

Your prepare's, your execute's and perhaps even the bind's should be within eval statements to verify that they worked, and if not to error out immediately with diagnostic information.That being said, what does $self->getFunction("getUserByTcsid") return? (i.e. store it in a separate variable and print it out - or add the debugging print to the actual subroutine getFunction() so that you can be sure that it's returning the information you believe it should be.

Test both with, and without, the id override so that you can compare results.

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

mike_jaixen

Adding the eval steps gave me a little more information, but it's looking more and more like the character set Interwoven is using is not necessarily the character set being used by Oracle.

The workflow begins by querying the database to find the approvers for a workflow, and storing it in a workflow variable. That works fine.

However, it fails when I extract the workflow variable contents using my $approversA = $WF->GetVariable("Approvers_A");

email.ipl: Getting name for remp022
Now looking up remp022
DBD: Smiley Surprised

racle::st execute failed: ORA-06550: line 1, column 53:
PLS-00561: character set mismatch on value for parameter 'P_TCSID'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator at char 52 in 'BEGIN :cursor := WAM_IWV_PC_PM.FN_GET_USER_BY_TCSID(<*>:tcsid); END;') [for Statement "BEGIN :cursor := WAM_IWV_PC_PM.FN_GET_USER_BY_TCSID(:tcsid); END;" with ParamValues: :tcsid="remp022", :cursor=DBI::st=HASH(0x9b94e8)] at /software/interwoven/teamsite/iw-perl/site/lib/FallingOnline/DirBuilder/Db/Util.pm line 179.

Now, I copied the code that queries the database to insert it into the workflow into my script, and used that information to query the database fine.
email.ipl: Getting name for remp022
Now looking up remp022
email.ipl: Name is remp022Mike Jaixen

So, it makes me wonder if there is something weird in the character sets, or some goofy invisible characters that are getting passed along in my string when it comes out of the workflow. Note the PL/SQL error string; it shows the value for tcsid being correctly passed.