On Sat, 31 Aug 2002 11:28:23 +0200 Hallvard B Furuseth <[EMAIL PROTECTED]>
wrote:
> I'm still trying to update through a cursor, and I still can't get it to
> work. The program fails at statement->execute(value) with the message
>
> DBD::Oracle::st execute failed:
> ORA-00936: missing expression (DBD ERROR: OCIStmtExecute)
> at ./test.pl line 27, <STDIN> chunk 1.
>
> What am I doing wrong now?
>
> I wondered if the UPDATE statement should have ":csr" instead of "csr",
> but then the program complained that I could not mix ':' and '?'
> variables.
Even if you can use a placeholder for the cursor name, the DBI statement
handle won't work.
> Here is the program:
>
> #!/local/bin/perl5 -w
>
> my($database, $user, $password) = ("lttest", "hbf");
>
> use strict;
> use DBI;
> use DBD::Oracle qw(:ora_types);
>
> &Login;
> my $dbh = DBI->connect("dbi:Oracle:$database", $user, $password,
> { 'AutoCommit' => 0, 'RaiseError' => 1 });
>
> my $sel = $dbh->prepare("
> BEGIN
> OPEN :csr FOR
> SELECT fakultetnavn FROM lt.fakultet WHERE fakultetnavn IS NOT NULL
> FOR UPDATE;
> END;");
> my $csr;
> $sel->bind_param_inout(":csr", \$csr, 0, { ora_type => ORA_RSET } );
> $sel->execute();
> my $upd = $dbh->prepare("
> UPDATE lt.fakultet SET fakultetnavn=? WHERE CURRENT OF csr");
You have to get the cursor name using the CursorName statement handle
attribute ($csr->{CursorName}). The DBI statement handle is not the
statement name. You will have to test to see if DBD::Oracle supports that
attribute, I don't think it does yet. You might be able to get the cursor
name using Oracle::OCI, but I have not used it so I don't know how.
You might also be able to force the name of the cursor using the alternate
syntax mentioned in the fine manual (perldoc DBD::Oracle):
$sth1 = $dbh->prepare(q{
CREATE OR REPLACE FUNCTION sp_ListEmp RETURN types.cursorType
AS l_cursor types.cursorType;
BEGIN
OPEN l_cursor FOR select ename, empno from emp order by ename;
RETURN l_cursor;
END;
});
$sth2 = $dbh->prepare(q{BEGIN :cursor := sp_ListEmp; END;});
In this case, you _might_ be able to use 'l_cursor' as your cursor name. I
don't currently have Oracle installed, so I can't test this myself.
If the SELECT and the INSERT were part of a PL/SQL package (possibly
temporary), you could open the cursor in a package level variable, return
it from a package function for your fetches, and use it in a package
procedure for the INSERT. For a start see
http://search.cpan.org/src/TIMB/DBD-Oracle-1.12/Oracle.ex/curref.pl .
I've given you a lot of speculation. Please let us know what works if
anything does.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
