Solved

how to debugging a package in PL/SQL developer

Posted on 2006-06-08
21
13,609 Views
Last Modified: 2008-01-09
how to debugging a package in PL/SQL developer;how to set break points;how to stop debugging.
0
Comment
Question by:chaitu chaitu
  • 12
  • 7
  • 2
21 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16869504
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16869597
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
Process exited.

3. If not already done, a DBA user needs to unlock the schema to provide access. This can be done with the following commands:

ALTER USER hr UNLOCK ACCOUNT;
ALTER USER hr IDENTIFIED BY hr;


 
4. For the PL/SQL debugging portion of this tutorial, the HR user needs a few additional privileges. Grant the privileges using the following commands:

GRANT debug any procedure, debug connect session TO hr;
 


what is hr here???
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16869613
hr is a test user in oracle database if you install example schemas.

you need to replace hr by your current username.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16869637
i think i dont have enough privelages to run that procedure in jdeveloper;so i try to debug same procedure in PL/SQL IDE.how to debug procedure in that IDE
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16870038
in which window i mean (program window/test window/sql window) i can debug in allroundautomations pl/sql developer;i want to debug package.where should i put breakpoints;
0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16896582
1.right click on the package you want to debug, hit edit spec&body
2.dbl click on the function/procedure you want to debug - this gets you to the start of the method
3.on the immediate left side of the method text, near the beginning of the method, click once to put a breakpoint - a small red circle with an X over it should appear
4.right click the method and hit test it - another dialog appears
5.click the left most icon in this dialog and you're ready to begin
6.hit the run icon to run the method and it should stop on the breakpoint
7. now ctrl+o for step over, ctrl+n for step in, ctrl+r to run free :)
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16899731
i put the some breakpoints at procedure starting point.
when i hit run icon method i am getting one alert box saying that

"Error setting at line no:191"

Illegal line

but it stopped there where i put break points.

No Debug Information is coming when i hover on certain variables.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16899797
while debbuging in the bottom window

variable and values are not coming...
0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16900233
for that you need to first hit F8 (compile) immediately after the step 1 - this will offer debug information. don't forget to also compile the eventual objects invalidated after compiling this particular package (menu Tools->Compile invalid objects->F8) - objects that depend on another always get invalidated when their dependant changes at least one bit.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16918006
as u said i hit F8 immediately after the step 1;

"dont forget to also compile the eventual objects invalidated after compiling this particular package "

menu Tools->Compile invalid objects->F8

what will it do???


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16918012
menu Tools->Compile invalid objects->F8

when i run this its taking so much .(i think because there r so many packages in my DB ;it is invalidating objects in all packages.)
0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16919142
well, you cannot avoid that... if the package you're trying to debug is a commonly used one and lots of other objects use it, this is it... they get invalidated and sometimes takes a lot to recompile
but, you could try this: copy the contents of this package to a new one so no other already existing object depends on what you're trying to debug. then it would be easier.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16919220
"copy the contents of this package to a new one so no other already existing object"

I didnt get this;

can u tell me the steps??
0
 
LVL 1

Accepted Solution

by:
primusmagestri earned 500 total points
ID: 16919308
ok. so you opened the package in edit mode (step 1.)
a)press CTRL+S to save it to some location (see that you use .sql extension or you'll have to change it afterwards).
b)open the saved file and, say that yor package name is PACK_NAME, change all the appearances of this exact name with something else, PACK_NAME2 for example.
c)then get back to PL/SQL Developer and say NO when you are asked to reload the contents of the current package; close the edit package window
d)open a command window (File->New->Command window) and enter in the prompt:
@<path_where_you_saved_the_package_contents>\PACK_NAME.SQL
and hit enter to execute it
e)your package copy will be created, just refresh the packages tree and you'll see it; do the same things, open for edit and F8 to compile it and get the debug info. this time, nothing else will be invalidated
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16919572
I did what u said above;but when i  put  breakpoints in package body its not stopping there;


0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16919659
are you sure you put breakpoints and not bookmarks?
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16919725
i think when u follow this approach (package copy) no need to do this thing; menu Tools->Compile invalid objects->F8
0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16920047
>>i think when u follow this approach (package copy) no need to do this thing; menu Tools->Compile invalid objects->F8
and what did I say?

>> this time, nothing else will be invalidated

the F8 i mentioned on e) was for debug info in your new (copied) package
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16920083
for new (copied) package should i do execute this menu Tools->Compile invalid objects??

I think F8 will execute the package or compile the package.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 16925419
THANKS for ur help;u didnt answer my above Q
0
 
LVL 1

Expert Comment

by:primusmagestri
ID: 16926086
well, let's get something straight:
1.when you open a package for edit pressing F8 will compile the package
2.when you open Tools->Compile invalid objects and press F8 those invalid objects (if any) will be compiled.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now