1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database disconnecting

Discussion in 'Oracle Apps Technical' started by vamsioracle, Apr 24, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi all

    I have created a procedure to send mail, using utl_smtp package. the code goes like this
    Code (Text):

    create or replace procedure send_mail(parma1 In date,param2 in date)
    is
    email varchar2(100);
    subject varchar2(100);
    conn utl_smtp.connection;
    begin

    capture the email id from some table;
    con = utl_smtp.connection (host,25);
    utl_smtp.helo(conn, host)
    utl_smtp.mail(conn,email)

    utl_smtp.open_data(conn)
    .
    .
    .
    .
    end;
     
    I execut this procedure in the following way
    Code (Text):

    SQL:> @c:\scripts\send_mail.sql;
     
    wher send_mail is the .sql file of the above code.

    I get the following error and i am disconnected from the data base.

    Code (Text):

    SQL:> @c:\scripts\send_mail.sql;

    Unknown command at the beginning email varcahr2 .... rest of the line ignored
    Unknown command at the beginning subject varcahr2 .... rest of the line ignored

    ORA-12203 -TNS: unable to conect to destination

    Warning: You are no longer connected to Oracle
    Not Connected
    SQL>
     

    What might be the reason? This is happening for this procedure only. I was able to compile many before this.

    vamsi
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    please use code blocks in your posts... it's high time :)
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    could you paste the full procedure code please then we can make out
     
  4. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    I was able to solve it. It was a simple error.The error is in the first line itself, i forgot procedure keyword.

    Code (SQL):
    CREATE ore REPLACE send_mail()
    ..........
    anyways thanks alot.