Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Question Unanswered: Problem with Oracle Procedure

    Hey there!

    I'm really in desperate need of some help! I tried to get a procedure up and running, which has to create a statistic and then email it to an administrator. So what I did was, I created a trigger, which fires, when a change is made to a table and keeps record of those changes in another table called table_benutzer. Now I wrote the procedure and tried to fill the variables dummy and message with text from the cursor.

    In order to check the contents of those two variables I tried to make an output with dmbs_output.put_line but it didn't work. Also every other thing I tried didn't work out. I could really use some help here!

    Here are the two procedures:

    --************************************************** ********
    --MAIL PROCEDURE
    --************************************************** ********
    create or replace PROCEDURE send_mail (message IN VARCHAR2)
    IS
    mailhost VARCHAR2(30) := 'mail.host.com';
    sender VARCHAR2(30) := 'bla@sender.com';
    recipient VARCHAR2(30) := 'bla@recipient.com';
    mail_conn utl_smtp.connection;
    header varchar2(255);

    BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    header:= 'Date: '||sysdate||utl_tcp.crlf||
    'From: '||sender||utl_tcp.crlf||
    'To: '||recipient||utl_tcp.crlf||
    'Subject: '||'Statistik Schueler-DB';

    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);

    utl_smtp.data(mail_conn, header||utl_tcp.crlf||''||utl_tcp.crlf||message);

    utl_smtp.quit(mail_conn);
    END;
    /

    --************************************************** ********
    --REPORT PROCEDURE
    --************************************************** ********
    create or replace procedure report( p_user benutzer.B_Kennung%type,
    p_date_from date,
    p_date_until date,
    p_actiontype table_benutzer.LOG_B_command%type)
    is
    cursor c1(cp_user benutzer.b_kennung%type
    ,cp_date_from date
    ,cp_date_until date
    ,cp_actiontype table_benutzer.LOG_B_command%type)
    is
    select * from table_benutzer
    where nvl(LOG_B_Datetime_upd,LOG_B_Datetime_neu) between cp_date_from and cp_date_until
    and nvl(cp_user,log_b_kennung) = log_b_kennung
    and nvl(cp_actiontype,log_b_command) = log_b_command;

    vr_c1 c1%rowtype;
    message varchar2(2000);
    dummy varchar2(200);
    begin
    open c1(p_user,p_date_from,p_date_until,p_actiontype);
    loop
    fetch c1 into vr_c1;
    exit when c1%notfound;

    dummy := vr_c1.log_b_name; -- hier kannst du noch mehr Daten aus der Tabelle einbinden und quasi die einzelnen Zeilen der Mail aufbereiten
    message := message || dummy || chr(10) ||chr(13);
    --das hier ist nur fürs debuggen, in sqlplus vorher set serveroutput on
    dbms_output.put_line(dummy);

    if length(message) >= 1800 then -- wichtig, um keinen Überlauf in der Variable zu bekommen!!
    message := message || chr(13) ||chr(10) || 'Mail hier abgeschnitten!';
    exit;
    end if;

    end loop;
    close c1;
    begin
    Send_Mail(message);
    exception
    when others then
    dbms_output.put_line('Mail konnte nicht verschickt werden!');
    dbms_output.put_line(dummy);
    end;
    end;
    /


    If anyone could help me out here, I'd really appreciate it!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Problem with Oracle Procedure

    Originally posted by grOOvekill@
    Hey there!

    I'm really in desperate need of some help! I tried to get a procedure up and running, which has to create a statistic and then email it to an administrator. So what I did was, I created a trigger, which fires, when a change is made to a table and keeps record of those changes in another table called table_benutzer. Now I wrote the procedure and tried to fill the variables dummy and message with text from the cursor.

    In order to check the contents of those two variables I tried to make an output with dmbs_output.put_line but it didn't work. Also every other thing I tried didn't work out. I could really use some help here!

    Here are the two procedures:

    --************************************************** ********
    --MAIL PROCEDURE
    --************************************************** ********
    create or replace PROCEDURE send_mail (message IN VARCHAR2)
    IS
    mailhost VARCHAR2(30) := 'mail.host.com';
    sender VARCHAR2(30) := 'bla@sender.com';
    recipient VARCHAR2(30) := 'bla@recipient.com';
    mail_conn utl_smtp.connection;
    header varchar2(255);

    BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    header:= 'Date: '||sysdate||utl_tcp.crlf||
    'From: '||sender||utl_tcp.crlf||
    'To: '||recipient||utl_tcp.crlf||
    'Subject: '||'Statistik Schueler-DB';

    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);

    utl_smtp.data(mail_conn, header||utl_tcp.crlf||''||utl_tcp.crlf||message);

    utl_smtp.quit(mail_conn);
    END;
    /

    --************************************************** ********
    --REPORT PROCEDURE
    --************************************************** ********
    create or replace procedure report( p_user benutzer.B_Kennung%type,
    p_date_from date,
    p_date_until date,
    p_actiontype table_benutzer.LOG_B_command%type)
    is
    cursor c1(cp_user benutzer.b_kennung%type
    ,cp_date_from date
    ,cp_date_until date
    ,cp_actiontype table_benutzer.LOG_B_command%type)
    is
    select * from table_benutzer
    where nvl(LOG_B_Datetime_upd,LOG_B_Datetime_neu) between cp_date_from and cp_date_until
    and nvl(cp_user,log_b_kennung) = log_b_kennung
    and nvl(cp_actiontype,log_b_command) = log_b_command;

    vr_c1 c1%rowtype;
    message varchar2(2000);
    dummy varchar2(200);
    begin
    open c1(p_user,p_date_from,p_date_until,p_actiontype);
    loop
    fetch c1 into vr_c1;
    exit when c1%notfound;

    dummy := vr_c1.log_b_name; -- hier kannst du noch mehr Daten aus der Tabelle einbinden und quasi die einzelnen Zeilen der Mail aufbereiten
    message := message || dummy || chr(10) ||chr(13);
    --das hier ist nur fürs debuggen, in sqlplus vorher set serveroutput on
    dbms_output.put_line(dummy);

    if length(message) >= 1800 then -- wichtig, um keinen Überlauf in der Variable zu bekommen!!
    message := message || chr(13) ||chr(10) || 'Mail hier abgeschnitten!';
    exit;
    end if;

    end loop;
    close c1;
    begin
    Send_Mail(message);
    exception
    when others then
    dbms_output.put_line('Mail konnte nicht verschickt werden!');
    dbms_output.put_line(dummy);
    end;
    end;
    /


    If anyone could help me out here, I'd really appreciate it!
    My crystal ball is in the shop for repair.
    No Oracle version specified.
    No OS or version specified.
    No ACTUAL & EXACT error messages posted.
    "Didnot work" is a bit vague.
    A gazillion thing could be wrong and make it "not work".
    What makes you think anyone can accurately guess
    what really may be wrong on your system?
    You're On Your Own (YOYO)!

  3. #3
    Join Date
    Nov 2003
    Posts
    2
    Yes, thank you very much. That was exactly the kind of smart ass answer I expected. It really helped me indeed. The next time I have to read that gay phrase about crystal balls, I'll puke myself to death, I swear to god. Why don't you just tell me to provide more info? No, you have to behave like a fu**in' geek, who spends 30 hours a day in front of his pc checking forums to annoy poster's who really need help.

    You know what? Just forget it. I'm outta here.

Posting Permissions

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