| Problem debugging in SQL Developer [message #301752] |
Thu, 21 February 2008 09:36  |
Philip Simons
Messages: 3
Registered: February 2008
|
Junior Member |
|
|
I have a problem with debugging using SQL Developer
When I reach the lines marked in red in the following
I cannot step to the folowing lines (marked in blue)
Even when extra_user<>'' is TRUE
or extra_user2<>'' is TRUE
create or replace
procedure "SEND_MAIL_TO_GROUPS_PROC"
(html_str IN VARCHAR2,
groupnames IN VARCHAR2,
extra_user IN VARCHAR2,
subject_str IN VARCHAR2,
extra_user2 IN VARCHAR2)
AUTHID DEFINER IS
--this needs to be created as sys
mail_conn utl_smtp.connection;
auser varchar(100);
user_list varchar(16000);
first boolean;
type users_curs is ref cursor;
mail_users users_curs;
has_mail_user boolean;
sender varchar(100);
begin
mail_conn := utl_smtp.open_connection ('SWIDB01', 25);--changed from localhost 13/oct/06
utl_smtp.helo(mail_conn,'SWIDB01');
sender:=sys_context('USERENV','SESSION_USER');
sender:=translate(translate( sender,' ','.'),'_','.');-- convert _ and space to .
utl_smtp.mail( mail_conn, sender);
has_mail_user:=false;
DBMS_OUTPUT.enable();
-- DBMS_OUTPUT.PUT_LINE(sql_str);
OPEN mail_users FOR
'select distinct GRANTEE from sys.dba_role_privs where grantee<>''SYSTEM'' and granted_role in (' || groupnames || ')';
loop
begin
fetch mail_users into auser;
exit when mail_users%NOTFOUND;
if auser<>'SYSTEM' then
auser:=translate(translate( auser,' ','.'),'_','.');-- convert _ and space to .
DBMS_OUTPUT.PUT_LINE(auser);
utl_smtp.rcpt( mail_conn,auser);
if has_mail_user then
user_list:=user_list||',';
end if;
has_mail_user:=true;
user_list:=user_list||auser;
end if;
EXCEPTION
WHEN OTHERS THEN NULL;
end;
end loop;
if extra_user<>'' then
auser:=translate(translate( extra_user,' ','.'),'_','.');
DBMS_OUTPUT.PUT_LINE('extra_user');
DBMS_OUTPUT.PUT_LINE(auser);
utl_smtp.rcpt( mail_conn,auser);
if has_mail_user then
user_list:=user_list||',';
end if;
has_mail_user:=true;
user_list:=user_list||auser;
end if;
if extra_user2<>'' then
auser:=translate(translate( extra_user2,' ','.'),'_','.');
DBMS_OUTPUT.PUT_LINE('extra_user2');
DBMS_OUTPUT.PUT_LINE(auser);
utl_smtp.rcpt( mail_conn,auser);
if has_mail_user then
user_list:=user_list||',';
end if;
has_mail_user:=true;
user_list:=user_list||auser;
end if;
if has_mail_user then
utl_smtp.data (
mail_conn,
'Subject :'||subject_str || CHR (13) || CHR (10) ||
'Content-Transfer-Encoding: 8bit'||
CHR (13) || CHR (10) ||
'Content-Type: text/html; charset=iso-8859-1'||
CHR (13) || CHR (10) ||
'To : ' || user_list || CHR (13) || CHR (10) ||
html_str
);
end if;
utl_smtp.quit (mail_conn);
end;
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
