Alternative debugging methods for Oracle package

Since many debugging methods are not very good shortly after oracle is involved, the most common debugging currently is the debugging provided by PLSQL developer. Although it is not as easy as Microsoft's vs.net series, for example, when viewing the variable value, if the variable is a collection, the specific content in the collection cannot be displayed in the observed value, but it is enough for beginners.

Now I have encountered a tricky problem, that is, during debugging, the function input parameters in some of my packages are my custom nest table, but during PLSQL debugging, in the input variable type, there is no variable type defined by myself. How is this good? Google hasn't found any similar reference articles for a long time.

 

After repeated suffering and tossing, I finally got a solution of my own. Although stupid, it worked well.

This method is to use emails ~!

Yes, that's right. It's just sending an email. The ult_smtp Package function in sys can be used to send emails. In this way, in the function that I think is faulty, "set breakpoints" is, of course, personal virtual, then, a code function is attached to the breakpoint to send emails. In this way, when the program reaches this breakpoint, it will send an email to the specified mailbox, then you will know whether the program is running here. You can write some information in the mail content, so that the segment is in that function, in that code block, what are the running conditions, and so on. Now I still don't know how to write the variable value into the email. I don't know how to get the variable value and assign it to the string. If so, this method is more perfect.

 

Add the procedure of the Self-written email.

-- ===================================================== ==================================

Procedure send_html_mail (

-- ===================================================== ==================================

P_sender_in in varchar2,

P_to_rcpt_in varchar2,

P_cc_rcpt_in in varchar2 default null,

P_bcc_rcpt_in varchar2 default null,

P_subject_in in varchar2,

P_htmlmsg_in in varchar2,

P_priority_in in pls_integer default null) is

 

Rochelle smtp_conn utl_smtp.connection;

L_sender varchar2 (32767): = p_sender_in;

Rochelle to_rcpt varchar2 (32767): = p_to_rcpt_in;

Rochelle cc_rcpt varchar2 (32767): = p_cc_rcpt_in;

L_bcc_rcpt varchar2 (32767): = p_bcc_rcpt_in;

 

Begin

Rochelle smtp_conn: = open_session;

Utl_smtp.mail (l_smtp_conn, fetch_address (l_sender ));

 

While (l_to_rcpt is not null) loop

Utl_smtp.rcpt (l_smtp_conn, fetch_address (l_to_rcpt ));

End loop;

 

While (l_cc_rcpt is not null) loop

Utl_smtp.rcpt (l_smtp_conn, fetch_address (l_cc_rcpt ));

End loop;

 

While (l_bcc_rcpt is not null) loop

Utl_smtp.rcpt (l_smtp_conn, fetch_address (l_bcc_rcpt ));

End loop;

 

-- Start body of email

Utl_smtp.open_data (l_smtp_conn );

 

-- Set "from" MIME Header

Write_mime_header (l_smtp_conn, 'from', p_sender_in );

 

-- Set "to" MIME Header

Write_mime_header (l_smtp_conn, 'to', p_to_rcpt_in );

 

-- Set "cc" MIME Header

Write_mime_header (l_smtp_conn, 'cc', p_cc_rcpt_in );

 

-- Set "BCC" MIME Header

Write_mime_header (l_smtp_conn, 'bcc ', p_bcc_rcpt_in );

 

-- Set "subject" MIME Header

Write_mime_header (l_smtp_conn, 'subobject', p_subject_in );

 

-- Set "reply-to" MIME Header

Write_mime_header (l_smtp_conn, 'reply-to', c_reply_to );

 

-- Set "Content-Type" MIME Header

Write_mime_header (l_smtp_conn, 'content-type', c_multipart_mime_type );

 

-- Set "X-mailer" MIME Header

Write_mime_header (l_smtp_conn, 'x-mailer ', c_mailer_id );

 

-- Set priority:

-- High normal low

-- 1 2 3 4 5

If (p_priority_in is not null) then

Write_mime_header (l_smtp_conn, 'x-priority ', p_priority_in );

End if;

 

-- Send an empty line to denote end of MIME headers and

-- Beginning of message body.

Utl_smtp.write_data (l_smtp_conn, utl_tcp.crlf );

 

-- Set upper HTML Boundary

Write_boundary (l_smtp_conn, false );

Write_text (l_smtp_conn, 'content-type: text/html; '| utl_tcp.crlf );

 

-- Send HTML content

Write_text (l_smtp_conn, p_htmlmsg_in );

 

-- Set lower HTML Boundary

Write_boundary (l_smtp_conn, true );

 

-- End mail msg

End_mail (l_smtp_conn );

 

-- End mail connection

End_session (l_smtp_conn );

 

Exception

When utl_smtp.transient_error or utl_smtp.permanent_error then

End_session (l_smtp_conn );

Err_raise_pkg.record_and_continue (

P_err_in => sqlcode,

P_msg_in => 'failed' to send mail due to error: '| sqlerrm );

 

When utl_smtp.invalid_operation then

End_session (l_smtp_conn );

Err_raise_pkg.record_and_continue (

P_err_in => sqlcode,

P_msg_in => 'invalid operation due to error: '| sqlerrm );

 

When others then

End_session (l_smtp_conn );

Err_raise_pkg.record_and_continue (

P_err_in => sqlcode );

End send_html_mail;