Shell Programming and Scripting

Hi. I'm attempting to assign a variable via a field that is using an autoincrement sequence. I'm trying to use the Oracle bind_param_inout() procedure to pass the value by reference. The code goes a bit like this:

*************

my $sql=q(INSERT INTO MESSAGES (message_id, message_text, message_category, start_date, stop_date, admin_comments, time_submitted) VALUES (?,?,?,?,?,?,?) RETURNING message_id INTO ?);
my $sth=$dbh->prepare($sql);
die "Could not prepare query. Check SQL syntax."
unless defined $sql;
my $newMessageID;
$sth->bind_param_inout(8,\$newMessageID, 38);
$sth->execute('messages_id_pkseq.nextval', $messageText, $messageCategory, $startDate, $stopDate, $adminComments, $timeStamp);

****************

By example, bind_param_inout seems to by far the best way to retrieve the value, but the examples I have seen so far all assume no arguments to sth->execute(). In this case I have arguments in my execute statement, and that seems to be where my issues lie. Apache returns:

***
DBD::Oracle::st execute failed: called with 7 bind variables when 8 are needed [for Statement "INSERT INTO MESSAGES (message_id, message_text, message_category, start_date, stop_date, admin_comments, time_submitted) VALUES (?,?,?,?,?,?,?) RETURNING message_id INTO ?" with ParamValues: 5=undef, 6=undef, 3=undef, 7=undef, 1=undef, 8=undef, 4=undef, 2=undef]
***

Wondering if anyone could shed some light as to why the DB is expecting the eight value (the ? after the RETURNING clause); it should be assigned via the bind_param_inout() function by my understanding. Could very well be something I am missng here though. There may be a way to pass it in the execute() statement as well. but just passing $newMessageID as the eight parameter returns an error. Thanks in advance for assistance.

Hello... I think you might have to pass some variable name for the in/out in your execute statement, but I'm not sure. Will follow along with you though as I'm going to need something very similar and I don't know how to do this... Thanks for the good question.

Thanks for the response quine. If you are still following, I slogged through this one and figured it out on my own. Apparently one can't mix and match binding parameters between the bind_param_inout statement and the execute() statement. If you use $sth->bind_param_inout(), you'll want to make sure that you bind all parameters that way, and have no arguments to $sth->execute(). Working great for me that way as of now.

Although, according to the 3k+ "expert" on the Oracle forum, "no one is using PERL for Oracle DB programming." Guess we are wasting our time.

Hello again...

So let me get this straight... You have to do something like....

$sth -> bind_param_in(1,\$messages_id_pkseq.nextval, 12);
$sth -> bind_param_in(2,\$message_text, 256);
$sth -> bind_param_in(3, ..... );

and like that for each of the parameters, then
$sth -> execute(); without parms?

Interesting..... Thanks...

As for perl for Oracle DB work via the DBI (which is I think what you are speaking of here), I have to admit that except for low-volume inserts/updates like summary records, or updating some status-of-a-job record and things like that, I don't use it much either. Something like what you are doing I would only do if the transaction rate is relatively low. Otherwise, you might consider input via sql+ and get the return value by assigning the result of a qx|| call to an array....

So you have a simple sql+ script that takes the VALUES in as parms and in perl you do something like....

(@IO) = qx|sqlplus -s login\/passwd\@DB scripttoexecute parm1 parm2... |;

The in/out parm should end up as $IO[0], etc.

The -s parm SILENCES sql+ so the normal stuff dumped to stdout doesn't end up in @IO, only what you want...

My syntax might be a little off... I'm writing this off-the-cuff as it were, and I'm a little rusty lately...

Indeed you are correct...a series of sth-> bind_param_inout() statements to bind all variables, and then no args to sth-> execute () and you should be good to go.

Thanks for the SQL+ tip and duly noted. The transaction rate will be very low in this case; this project is a .cgi workaround that assumes our Tomcat server is down and that our normal mechanisms aren't available. I doubt there will be more than a few transactions a week.