Home » SQL & PL/SQL » SQL & PL/SQL » how to run SQL Developer in debug (12.1.0.2.0)
how to run SQL Developer in debug [message #665920] Mon, 02 October 2017 15:06 Go to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
I wish to run my Oracle Procedure in SQL Developer Debug Mode.

I understand this requires running the following scripts on the database as SYS

GRANT DEBUG CONNECT SESSION TO myschema;
GRANT DEBUG ANY PROCEDURE TO myschema;


BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => '142.168.XXX.XXX',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
principal_name => 'myschema',
principal_type => xs_acl.ptype_db)
);
END;


For the HOST do I put my laptop IP4 address ( get with ipconfig ) or that of the remote data base ?


I've added the remote TNS entry to TNSNames.ora

racmbdevi_1=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.36.XXX.XXX)
(PORT=1532)
)
(CONNECT_DATA=
(SERVICE_NAME=MBDEVI)
(INSTANCE_NAME=racmbdev_1)
)
)

if I try the remote DBase address I get..

Connecting to the database auditcollections@RAC-MBDEV.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.36.XXX.XXX', '4000' )
ORA-30683: failure establishing connection to debugger
ORA-12541: TNS:no listener
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database auditcollections@RAC-MBDEV.

if I try my laptop IP4 address It connects I guess and the procedure runs since I get output messages
however it deosn't run to my breakpoint so I could step through it.

What am I missing out on... how can I in fact run in debug mode and step through a procdure ?

Connecting to the database auditcollections@RAC-MBDEV.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '142.168.203.185', '4000' )
Debugger accepted connection from database on port 4000.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
===>>>> p_note_log1 = NEXIUM 40MG TABLET- NO SUB
===>>>> p_note_log1 =
===>>>> p_note_log1 =
Process exited.
Disconnecting from the database auditcollections@RAC-MBDEV.
Debugger disconnected from database.
how to run SQL Developer in debug [message #665921 is a reply to message #665920] Mon, 02 October 2017 15:07 Go to previous messageGo to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
I wish to run my Oracle Procedure in SQL Developer Debug Mode.

I understand this requires running the following scripts on the database as SYS

GRANT DEBUG CONNECT SESSION TO myschema;
GRANT DEBUG ANY PROCEDURE TO myschema;


BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
host => '142.168.XXX.XXX',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
principal_name => 'myschema',
principal_type => xs_acl.ptype_db)
);
END;


For the HOST do I put my laptop IP4 address ( get with ipconfig ) or that of the remote data base ?


I've added the remote TNS entry to TNSNames.ora

racmbdevi_1=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.36.XXX.XXX)
(PORT=1532)
)
(CONNECT_DATA=
(SERVICE_NAME=MBDEVI)
(INSTANCE_NAME=racmbdev_1)
)
)

if I try the remote DBase address I get..

Connecting to the database auditcollections@RAC-MBDEV.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.36.XXX.XXX', '4000' )
ORA-30683: failure establishing connection to debugger
ORA-12541: TNS:no listener
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database auditcollections@RAC-MBDEV.

if I try my laptop IP4 address It connects I guess and the procedure runs since I get output messages
however it deosn't run to my breakpoint so I could step through it.

What am I missing out on... how can I in fact run in debug mode and step through a procdure ?

Connecting to the database auditcollections@RAC-MBDEV.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '142.168.XXX.XXX', '4000' )
Debugger accepted connection from database on port 4000.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
===>>>> p_note_log1 = NEXIUM 40MG TABLET- NO SUB
===>>>> p_note_log1 =
===>>>> p_note_log1 =
Process exited.
Disconnecting from the database auditcollections@RAC-MBDEV.
Debugger disconnected from database.
Re: how to run SQL Developer in debug [message #665922 is a reply to message #665921] Mon, 02 October 2017 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/search?hl=en&q=DBMS_DEBUG_JDWP+examples

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: how to run SQL Developer in debug [message #665923 is a reply to message #665922] Mon, 02 October 2017 15:56 Go to previous message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
Thank you !
Previous Topic: Converting SYS_REFCURSOR to xml
Next Topic: Help in SQL query
Goto Forum:
  


Current Time: Thu Mar 28 18:27:57 CDT 2024