2uzhan.com
Advertisement
Now Place:2uzhan.com » TOAD exec procedure help

TOAD exec procedure help

Oracle @ April 12, 2006   Views:0

Hello,

when I try to run a procedure using TOAD it gives me an error

ORA-01008: not all variables bound

but when i try the same exec statement in sqlplus it runs without any error

Can someone please help me with this, I did a lot of googling but could not find any solution.

Thanks
Rohit

--------------Solutions-------------

Show us the command your executing in TOAD and then we might be able to help.

Alan

here is the command to run the procedure:

variable resp VARCHAR2(2000);
exec Send_Email_Alert('test','[email protected]','mesg','su b','text/plain',:resp);
print resp;

and the procedure script:
================================================== ====
CREATE OR REPLACE PROCEDURE Send_Email_Alert (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_message IN VARCHAR2,
p_subject IN VARCHAR2 DEFAULT 'Web Mailer',
p_content_type IN VARCHAR2 DEFAULT 'text/plain',
p_response OUT VARCHAR2
)
/************************************************** *********************************
This Procedure is used for sending emails ************************************************** **********************************/
IS
crlf VARCHAR2 (2) := UTL_TCP.crlf;
l_mail_conn UTL_SMTP.connection;
l_mail_host VARCHAR2 (30) := <mail server>;
l_header VARCHAR2 (1000);
BEGIN

l_mail_conn := UTL_SMTP.open_connection (l_mail_host, 25);
l_header :=
'To: ' || p_recipient || '@' || l_mail_host || crlf ||
'Content-Type: ' || p_content_type || crlf ||
'X-Mailer: ' || 'Mailer by Oracle'|| crlf ||
'Subject: '|| p_subject ;

UTL_SMTP.helo (l_mail_conn, l_mail_host);
UTL_SMTP.mail (l_mail_conn, p_sender);
UTL_SMTP.rcpt (l_mail_conn, p_recipient);
UTL_SMTP.open_data (l_mail_conn);
UTL_SMTP.write_data (l_mail_conn, l_header);
UTL_SMTP.write_data (l_mail_conn, crlf || p_message);
UTL_SMTP.close_data (l_mail_conn);
UTL_SMTP.quit (l_mail_conn);
p_response := 'Mail sent successfully';

EXCEPTION
WHEN OTHERS
THEN
p_response := 'Error: ' || SUBSTR (SQLERRM, 1, 100);
END Send_Email_Alert;
/
=================================================

variable, exec and print are sqlplus specific keywords. In TOAD you'll have to declare an anonymous plsql block (begin... end) to call your procedure and probably use dbms_output if you want to print the value of the variable.

Alan

Tags:
© 2018 2uzhan.com Contact