Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Major |
| Complexity | Unknown |
| Component | Editor - Debugger for Oracle |
| Version | 17.0 |
Just upgraded to ADS 17, and am trying to debug a procedure defined in a package. From what I can see, ADS does not seem to recognize procedures with no parameters as things that can be run. The procedure compiles and runs when executed using PLSQL (granted with an error, hence the debugging).
I've attached screenshots of what I am seeing, as well as the most current logs from .datastudio
I am running on Mac OX 10.11.3, oracle java 1.8.0_40.
Let me know what else you need.
|
|
434 KB
|
17 KB
|
|
200 KB
|
|
106 KB
|
|
113 KB
|
|
59 KB
|
|
138 KB
|
|
5 KB
I am confused about the screen shots. First screen shot shows the create or replace package DDL, which will form an invalid package object due to the fact that v_current_proc variable is not set. If this DDL is run the package will be invalid and your screen shot would show an invalid object in the tree, instead of the valid object shown in your screen shot.
Next you will want to make sure you create the package body before you debug in the debugger. Without the package body there will be nothing to debug.
Now I find no issues debugging a package in debugger with its body defined and which has procedures with no parameters. I am attaching a very basic package and package body script with a screen shot....
Hi Ivan, the screenshot was of the package definition, not the full package. It is valid in Oracle to declare a variable in the package definition without a value. I know this because it works - the package compiles and runs. Given that the screenshots also included the object tree, which did no show the package as invalid, I would have thought that evident. I did not include the package body in either a screenshot or the ticket as I did not think it relevant, and you had not requested it.
That being said, I was incorrect in my original diagnosis. The issue has nothing to do with the presence or absence of procedure parameters. I checked an older version of the package, and all three declared procedures are runnable and debuggable. Since the package itself is over 2000 lines long, it will probably take me a while to track down. Though honestly, given your response, I'm not sure I'll bother spending my time to help make your product better.
Hi Ivan, the screenshot was of the package definition, not the full package. It is valid in Oracle to declare a variable in the package definition without a value. I know this because it works - the package compiles and runs. Given that the screenshots also included the object tree, which did no show the package as invalid, I would have thought that evident. I did not include the package body in either a screenshot or the ticket as I did not think it relevant, and you had not requested it.
That being said, I was incorrect in my original diagnosis. The issue has nothing to do with the presence or absence of procedure parameters. I checked an older version of the package, and all three declared procedures are runnable and debuggable. Since the package itself is over 2000 lines long, it will probably take me a while to track down. Though honestly, given your response, I'm not sure I'll bother spending my time to help make your product better.
Hi Jon,
You are correct. I am not sure why I received that error on the variable declaration when I ran your script, and if you have the package body declared then you should be able to run the debugger on the package without any issues executing a procedure without parameters. There was nothing in the log files that showed any exceptions related to the debugger and because I saw no body SQL I only assumed perhaps the package body was missing. I would need more information to try and debug and fix this issue, if it is a syntax error in the body code. Could you attach the create package body for the 3 procedures?
Hi Jon,
You are correct. I am not sure why I received that error on the variable declaration when I ran your script, and if you have the package body declared then you should be able to run the debugger on the package without any issues executing a procedure without parameters. There was nothing in the log files that showed any exceptions related to the debugger and because I saw no body SQL I only assumed perhaps the package body was missing. I would need more information to try and debug and fix this issue, if it is a syntax error in the body code. Could you attach the create package body for the 3 procedures?
From what I can see, the script itself has nothing to do with the issue. I have another database with the same package defined, and it does not manifest the same error. That leads me to suspect something around the database configuration that might be causing the difference. Can you explain how you identify what procedures can be executed in a package? I am guessing you use the oracle metadata views, so which ones are important? I can then try to step through that process on both databases and see if there are discrepancies that would explain what is happening.
From what I can see, the script itself has nothing to do with the issue. I have another database with the same package defined, and it does not manifest the same error. That leads me to suspect something around the database configuration that might be causing the difference. Can you explain how you identify what procedures can be executed in a package? I am guessing you use the oracle metadata views, so which ones are important? I can then try to step through that process on both databases and see if there are discrepancies that would explain what is happening.
ADS has the feature Help->[SQL Log] which allows you to see the queries executed on the database server to get meta data. The following 2 queries are the ones used to extract the package body information, I modified them to work for your package in screen the shot.
SELECT a.owner, a.object_name, a.package_name, 'FUNC' rtype, a.overload, o.created, o.LAST_DDL_TIME, o.status
ADS has the feature Help->[SQL Log] which allows you to see the queries executed on the database server to get meta data. The following 2 queries are the ones used to extract the package body information, I modified them to work for your package in screen the shot.
SELECT a.owner, a.object_name, a.package_name, 'FUNC' rtype, a.overload, o.created, o.LAST_DDL_TIME, o.status
That's definitely the issue. The query returns:
OWNER OBJECT_NAME PACKAGE_NAME RTYPE OVERLOAD CREATED LAST_DDL_TIME STATUS
-------- -------------------------- --------------- -------- ----------- --------------------- -------------------- ---------
ECSCO REPORT_SUMMARIZE_EVENT_DAY REPORT_UTILS PROC (null) 10/20/2015 6:57:57 PM 3/4/2016 11:32:09 PM VALID
And the same query in another database returns three rows, as it should. Given that this user has access to the dba views, I'm also currently guessing it is not permissions related. I'll look into other possible configuration things, and how those views are populated. Please share if you have any insight as to why these views are behaving differently.
That's definitely the issue. The query returns:
OWNER OBJECT_NAME PACKAGE_NAME RTYPE OVERLOAD CREATED LAST_DDL_TIME STATUS
-------- -------------------------- --------------- -------- ----------- --------------------- -------------------- ---------
ECSCO REPORT_SUMMARIZE_EVENT_DAY REPORT_UTILS PROC (null) 10/20/2015 6:57:57 PM 3/4/2016 11:32:09 PM VALID
And the same query in another database returns three rows, as it should. Given that this user has access to the dba views, I'm also currently guessing it is not permissions related. I'll look into other possible configuration things, and how those views are populated. Please share if you have any insight as to why these views are behaving differently.
Hi Ivan,
I think I have it figured out. The database I am seeing the issue on is running oracle 12.1.0.2.0. The version without the issue is 12.1.0.1.0. It seems as though Oracle fixed this bug in 12.1.0.2.0 - https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=10j51ved41_4&_afrLoop=226408584362148 , and I think you were relying on the behavior of that bug to identify parameter-less procedures. Basically, you can't use the arguments view for those procedures any longer.
Thoughts?
Hi Ivan,
I think I have it figured out. The database I am seeing the issue on is running oracle 12.1.0.2.0. The version without the issue is 12.1.0.1.0. It seems as though Oracle fixed this bug in 12.1.0.2.0 - https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=10j51ved41_4&_afrLoop=226408584362148 , and I think you were relying on the behavior of that bug to identify parameter-less procedures. Basically, you can't use the arguments view for those procedures any longer.
Thoughts?
It seems like the DBA_PROCEDURES view might be a better choice, where the OBJECT_NAME is the package name.
It seems like the DBA_PROCEDURES view might be a better choice, where the OBJECT_NAME is the package name.
Hi Jon,
We have oracle 12.1.0.2.0 instance running in our dev and qa lab now and we can reproduce your issue. I'll work on getting a fix for your specific issue quick but we will need to do a full regression test on version 17 to insure datastudio is working correctly with this version oracle. Will keep you updated and hopefully have a patch for you by early next week.
Hi Jon,
We have oracle 12.1.0.2.0 instance running in our dev and qa lab now and we can reproduce your issue. I'll work on getting a fix for your specific issue quick but we will need to do a full regression test on version 17 to insure datastudio is working correctly with this version oracle. Will keep you updated and hopefully have a patch for you by early next week.
Hi Ivan,
No worries. I've moved past the issue I needed this for anyway, so take your time.
Hi Ivan,
No worries. I've moved past the issue I needed this for anyway, so take your time.
Package procedure extraction has been modified so that it will still extract procedures in a package that do not contain parameters in oracle 12c.
Committed revision: 48934
Package procedure extraction has been modified so that it will still extract procedures in a package that do not contain parameters in oracle 12c.
Committed revision: 48934
Hi Jon,
I know you are a long time user and I am sorry that you had to experience this issue. It is not normal for database vendors to modify how there catalog tables are working in patches. We have enhanced the package procedure extraction so this will no longer be an issue. We should be able to get you a patch by tomorrow or Monday, once the QA team certifies the changes.
After reviewing all the patch notes for 12.1.0.2.0, there doesn't seem to be anything else that will affect data studio.
Hi Jon,
I know you are a long time user and I am sorry that you had to experience this issue. It is not normal for database vendors to modify how there catalog tables are working in patches. We have enhanced the package procedure extraction so this will no longer be an issue. We should be able to get you a patch by tomorrow or Monday, once the QA team certifies the changes.
After reviewing all the patch notes for 12.1.0.2.0, there doesn't seem to be anything else that will affect data studio.
@QA
The modifications can be tested by using the tree node, packages->procedures. Also if you turn detail view on, this will hit the deeper level of extraction. You can test the final extraction changes by debugging the package in the Package Debugger. You should get all functions and procedures available in the debugger.
@QA
The modifications can be tested by using the tree node, packages->procedures. Also if you turn detail view on, this will hit the deeper level of extraction. You can test the final extraction changes by debugging the package in the Package Debugger. You should get all functions and procedures available in the debugger.
Hi Jon,
The issue should now be resolved. You can download the patch with the link below.
patch: ads-17.0.3-11-patch.zip
Let us know if you have any issues with the new patch
Hi Jon,
The issue should now be resolved. You can download the patch with the link below.
patch: ads-17.0.3-11-patch.zip
Let us know if you have any issues with the new patch
Thanks, I'll give it a try later today.
Thanks, I'll give it a try later today.
Issue #14316 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build v17.0.3-8 |
| No time estimate |
I am confused about the screen shots. First screen shot shows the create or replace package DDL, which will form an invalid package object due to the fact that v_current_proc variable is not set. If this DDL is run the package will be invalid and your screen shot would show an invalid object in the tree, instead of the valid object shown in your screen shot.
Next you will want to make sure you create the package body before you debug in the debugger. Without the package body there will be nothing to debug.
Now I find no issues debugging a package in debugger with its body defined and which has procedures with no parameters. I am attaching a very basic package and package body script with a screen shot....