Home » Other » Client Tools » Problem debugging in SQL Developer
Problem debugging in SQL Developer [message #301752] Thu, 21 February 2008 09:36 Go to next message
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;
Re: Problem debugging in SQL Developer [message #301753 is a reply to message #301752] Thu, 21 February 2008 09:39 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Please format your code and place it between [code] ... [/code] tags. Otherwise it is unreadable.
PS in Oracle '' is equivalent (pretty much) to NULL which you have yto evaluate using IS NULL or IS NOT NULL

[Updated on: Thu, 21 February 2008 09:41]

Report message to a moderator

Re: Problem debugging in SQL Developer [message #301754 is a reply to message #301752] Thu, 21 February 2008 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I wonder what you'd see if you commented out the following 2 lines:
>EXCEPTION
>WHEN OTHERS THEN NULL;
Re: Problem debugging in SQL Developer [message #301757 is a reply to message #301752] Thu, 21 February 2008 09:48 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
As empty string is treated as NULL, normal comparition operators do not work correctly. Use IS NOT NULL instead.
Re: Problem debugging in SQL Developer [message #301759 is a reply to message #301757] Thu, 21 February 2008 09:51 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Is that not what I said? Wink

[Updated on: Thu, 21 February 2008 09:51]

Report message to a moderator

Previous Topic: Toad - Database connectivity problem
Next Topic: Cannot Create Spool File
Goto Forum:
  


Current Time: Thu Mar 28 12:29:55 CDT 2024