Zend_Db - define not done before fetch or execute and fetch

Question

I have a send_mail() procedure in Oracle that handles all the emails.

I can call that procedure from a Zend query with no errors $db->query("blah blah"); but when I use a $stmt = $db->prepare("blah blah"); I get the error (though the email still sends):

24374 ORA-24374: define not done before fetch or execute and fetch *begin send_mail(:mailto,:mailhost,:mailport,:mailfrom,:subject,:message); end;","file":"C:\\wamp\\www\\sam\\library\\Zend\\Db\\Statement\\Oracle.php

All my variable are defined? So not sure what could be causing this error?

My code:

$db = Zend_Registry::get('db');

            $emailTo = trim($_POST['emailto']);
            $subject = trim($_POST['subject']);
            $message = trim($_POST['msg']);

        $eSettings = $db->query("Select MAIL_HOST, MAIL_PORT, MAIL_FROM from     PI_SETTINGS_TABLE")->FetchAll();

            $stmt = $db->prepare("begin send_mail(:mailto,:mailhost,:mailport,:mailfrom,:subject,:message); end;");
            $stmt->bindParam('mailto', $emailTo);
            $stmt->bindParam('mailhost', $eSettings[0]['MAIL_HOST']);
            $stmt->bindParam('mailport', $eSettings[0]['MAIL_PORT']);
            $stmt->bindParam('mailfrom', $eSettings[0]['MAIL_FROM']);
            $stmt->bindParam('subject', $subject);
            $stmt->bindParam('message', $message);
            $stmt->execute();
            $checkQuery = $stmt->FetchAll();

Maybe some one can shed some light on why I can pass this as a query but not as a bind statement?

My SEND_MAIL procedure:

create or replace
PROCEDURE "SEND_MAIL" (
msg_to varchar2,
mail_host varchar2,
mail_port varchar2,
msg_from varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;

BEGIN
c := utl_smtp.open_connection(mail_host, mail_port); -- SMTP on port 25
utl_smtp.helo(c, mail_host);
utl_smtp.mail(c, msg_from);
utl_smtp.rcpt(c, msg_to);

utl_smtp.data(c,'From: ' || msg_from || utl_tcp.crlf ||
'To: ' || msg_to || utl_tcp.crlf ||
'Subject: ' || msg_subject ||
utl_tcp.crlf || utl_tcp.crlf || msg_text);
utl_smtp.quit(c);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt
using UTL_SMTP.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
END;

NB: When I run the query from SQL Developer using bind vars I get this error and the email does not send:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PI_USER_ADMIN.SEND_MAIL", line 15

But it will send with zend?

Kindest Regards

Nathan

You should not get an error with SQL Developer. At least if you have an error, expect it to be reproduced with zend :) The character to number conversion error may come from the port number (needs to be numeric). I'm not sure your original error (ORA-24374) comes from this procedure though, since the proc looks fine ! — Vincent Malgrat, Feb 20 '13 at 11:03
@VincentMalgrat Ah you were right about the port number should not be a string (noob moment) lol. So it works fine in SQL Developer with bind vars but Zend throws that define error but still sends it? — Kal, Feb 20 '13 at 11:14
@VincentMalgrat OK, got it working it partly due to me handling exceptions in the procedure and not letting PHP catch them and the fact that I had `$checkQuery = $stmt->FetchAll();` at the end of that PHP block when that query wasn't ever going to return anything. Thanks for your help Vincent. If you post that comment as an answer I'll mark that accepted :) — Kal, Feb 20 '13 at 11:25
I think **you** should post an answer because I have no idea why it failed in PHP and what correction you have made to make it work ! =) — Vincent Malgrat, Feb 20 '13 at 11:55

Zend_Db - define not done before fetch or execute and fetch

0 Answers0