Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Red face Unanswered: TOAD exec procedure help

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Show us the command your executing in TOAD and then we might be able to help.

    Alan

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    here is the command to run the procedure:

    variable resp VARCHAR2(2000);
    exec Send_Email_Alert('test','test@test.com','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;
    /
    =================================================

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •