utplsql / utplsql-sqldeveloper Goto Github PK
View Code? Open in Web Editor NEWExtension for running unit tests straight from SQL Developer
License: Apache License 2.0
Extension for running unit tests straight from SQL Developer
License: Apache License 2.0
Installation is not possible without internet connection or if access to internet via SQL Developer is blocked. In these cases the following screen appears when the Apache license should be shown:
The Java stack (Details) looks as follows:
java.lang.RuntimeException: java.lang.NullPointerException
at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1488)
at sun.net.www.protocol.http.HttpURLConnection.access$200(HttpURLConnection.java:91)
at sun.net.www.protocol.http.HttpURLConnection$9.run(HttpURLConnection.java:1466)
at sun.net.www.protocol.http.HttpURLConnection$9.run(HttpURLConnection.java:1464)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessController.doPrivilegedWithCombiner(AccessController.java:782)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1463)
at sun.net.www.protocol.http.HttpURLConnection.getHeaderField(HttpURLConnection.java:3018)
at java.net.HttpURLConnection.getResponseCode(HttpURLConnection.java:489)
at oracle.ideimpl.webupdate.wizard.UpdateLicensePage.selectUpdate(UpdateLicensePage.java:250)
at oracle.ideimpl.webupdate.wizard.UpdateLicensePage.pageSelected(UpdateLicensePage.java:132)
at oracle.ideimpl.webupdate.wizard.UpdateWizardPage.onEntry(UpdateWizardPage.java:37)
at oracle.ide.wizard.FSMWizard.gotoPanel(FSMWizard.java:839)
at oracle.ide.wizard.FSMWizard.setSelectedPage(FSMWizard.java:505)
at oracle.bali.ewt.wizard.BaseWizard.selectPage(BaseWizard.java:1999)
at oracle.ide.wizard.FSMWizard.selectPage(FSMWizard.java:480)
at oracle.ide.wizard.FSMWizard.doNext(FSMWizard.java:344)
at oracle.bali.ewt.wizard.BaseWizard$Action$1.run(BaseWizard.java:4072)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:756)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:726)
at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)
at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:109)
at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:184)
at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:229)
at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:227)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:227)
at java.awt.Dialog.show(Dialog.java:1084)
at java.awt.Component.show(Component.java:1671)
at java.awt.Component.setVisible(Component.java:1623)
at java.awt.Window.setVisible(Window.java:1014)
at java.awt.Dialog.setVisible(Dialog.java:1005)
at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:382)
at oracle.bali.ewt.wizard.WizardDialog.runDialog(WizardDialog.java:298)
at oracle.ide.dialogs.WizardLauncher.runDialog(WizardLauncher.java:51)
at oracle.ideimpl.webupdate.wizard.UpdateWizard.runWizard(UpdateWizard.java:261)
at oracle.ideimpl.webupdate.WebUpdateController.checkForUpdates(WebUpdateController.java:24)
at oracle.ideimpl.webupdate.WebUpdateController.handleEvent(WebUpdateController.java:31)
at oracle.ideimpl.controller.MetaClassController.handleEvent(MetaClassController.java:54)
at oracle.ide.controller.IdeAction$ControllerDelegatingController.handleEvent(IdeAction.java:1488)
at oracle.ide.controller.IdeAction.performAction(IdeAction.java:663)
at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:1159)
at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:618)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.AbstractButton.doClick(AbstractButton.java:376)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:833)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:877)
at java.awt.Component.processMouseEvent(Component.java:6533)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
at java.awt.Component.processEvent(Component.java:6298)
at java.awt.Container.processEvent(Container.java:2236)
at java.awt.Component.dispatchEventImpl(Component.java:4889)
at java.awt.Container.dispatchEventImpl(Container.java:2294)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
at java.awt.Container.dispatchEventImpl(Container.java:2280)
at java.awt.Window.dispatchEventImpl(Window.java:2746)
at java.awt.Component.dispatchEvent(Component.java:4711)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:709)
at java.awt.EventQueue$3.run(EventQueue.java:703)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
at java.awt.EventQueue$4.run(EventQueue.java:731)
at java.awt.EventQueue$4.run(EventQueue.java:729)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)
at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Caused by: java.lang.NullPointerException
at sun.net.www.protocol.http.HttpURLConnection.plainConnect0(HttpURLConnection.java:1191)
at sun.net.www.protocol.http.HttpURLConnection$6.run(HttpURLConnection.java:1022)
at sun.net.www.protocol.http.HttpURLConnection$6.run(HttpURLConnection.java:1020)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessController.doPrivilegedWithCombiner(AccessController.java:782)
at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:1019)
at sun.net.www.protocol.http.HttpURLConnection.connect(HttpURLConnection.java:966)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1546)
at sun.net.www.protocol.http.HttpURLConnection.access$200(HttpURLConnection.java:91)
at sun.net.www.protocol.http.HttpURLConnection$9.run(HttpURLConnection.java:1466)
at sun.net.www.protocol.http.HttpURLConnection$9.run(HttpURLConnection.java:1464)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessController.doPrivilegedWithCombiner(AccessController.java:782)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1463)
at java.net.HttpURLConnection.getResponseCode(HttpURLConnection.java:480)
... 83 more
Enabling and disabling the menu option "Run utPLSQL test" might be expensive. Especially when the context menu is called on a node of a schema with a lot of package specifications (the first time, but also the subsequent times). See #15.
The user may choose the enable/disable behaviour of the context menu in the utPLSQL preferences.
By default the "Run utPLSQL test" menu option should be available, even if no utPLSQL is installed in the database instance.
It would be great to have separate Snippet section in SQLDeveloper to aid users.
The snippets could contain a full list of supported annotations and expectations for current version of utPLSQL.
Ideally, utPLSQL framework could include function to provide a list of supported annotations with description and syntax examples.
It's possible to run all utPLSQL tests in a schema/user from the connection node. But it is not possible from a user node, since the menu is not enabled for this node type.
Technically it should not be a problem to support user nodes. Hence there is no reason to not support it.
The log pane shows the following:
SEVERE 603 34748 org.utplsql.sqldev.CodeCoverageReporter Error when running code coverage: Failed to open file:/C:/Users/phs/AppData/Local/Temp/utplsql_476670770145486823html. Error message: No application is associated with the specified file for this operation.
There is a dot .
missing before html
. Hence no extension and no browser is opened.
Based on issue #11.
Evaluate suite paths and present them in the tree view. Allow to choose one ore more paths and generate the ut.run
command. Alternative paths such as "alltests" and "contexts" should be considered. This is the additional value to the pure Package based test execution in the utPLSQL extension.
Implementing the oddgen interface does not have an effect if the oddgen extension is not installed. This means it is not necessary to install oddgen. However, if it is installed, oddgen will automatically detect the implementation and provide the generator in its Generators navigator tree.
In version v0.6 a part of the extension depends on functionality introduced with utPLSQL version 3.0.4.
It is expected that an official API will be introduced in a future utPLSQL version (>3.1.2) to replace the functionality provided by the UT_ANNOTATION_MANAGER
package.
The following version numbers are used in released versions of utPLSQL:
Date | Name | Value of UT.VERSION |
---|---|---|
2017-01-18 | v3.0.0.0-Alpha1 | |
2017-05-15 | v3.0.0-beta | utPLSQL - Version v3.0.0.708-beta |
2017-05-18 | utPLSQL v3.0.0 | utPLSQL - Version v3.0.0 |
2017-06-14 | utPLSQL v3.0.1 | utPLSQL - Version v3.0.1.798 |
2017-07-18 | utPLSQL v3.0.2 | utPLSQL - Version v3.0.2.938 |
2017-08-30 | utPLSQL v3.0.3 | v3.0.3.1266 |
2017-11-03 | utPLSQL v3.0.4 | v3.0.4.1372 |
2018-04-25 | utPLSQL v3.1.0 | v3.1.0.1849-develop |
2018-04-29 | utPLSQL v3.1.1 | v3.1.1.1868 |
2018-07-22 | utPLSQL v3.1.2 | v3.1.2.2134 |
... | ... | ... |
Instead of querying for the existence of UT_ANNOTATION_MANAGER
the return value of the function UT.VERSION
should be used to determine if a certain feature is available or not. Versions of utPLSQL without a UT.VERSION
function are not supported (e.g. v3.0.0.0-Alpha1).
Features are disabled if not available in a version (context menus, oddgen generators) and a error message is written to the error log (visible in the logging pane). If not feasible an error message is shown when executing an enabled but not supported feature.
utPLSQL v3 supports Oracle 11.2 and above. In those oracle versions the output can be set to unlimited size.
Can we set this to unlimited by default or have a preference to set the default output size?
should get this extension listed on the "official" list
see
https://www.oracle.com/technetwork/developer-tools/sql-developer/extensions-083825.html#available
the text at the bottom of teh page says
Please contact [email protected], if you'd like to upload your extension on this page.
At the end of a test the total elapsed time is shown.
It would be nice to see the elapsed time during the test run as well. Such a time needs to be updated independently of runtime reporter events.
When I run all utPLSQL tests (many of them are running really quickly) I often see that the UI is misbehaving.
I'm not sure if i's related to:
Maybe it would be possible to change the refresh rate to be say every 10-20ms to avoid excessive UI flickering while maintaining the "realtime" user-experience?
Attached gifs
showing some of the flickering I've encountered.
In version 1.0.0 the time is shown in seconds, e.g. 0.042
. When the execution time of tests varies a lot, then this is not easy to read. An alternative could be to change the unit based on the value to make it more human readable.
Example:
The unit must be added to the value in the cell.
Sorting should still be possible based on a unified unit behind the scenes.
Make this behavior configurable in the utPLSQL preferences.
In version 0.1 all utPLSQL tests are executed as script in a SQL Developer worksheet and the test output is shown in the script output pane. This was easy to implement and is an acceptable solution for a MVP but it has the following flaws:
To address these flaws all tests should be executed by the utPLSQL extension (without blocking the current connection) and the results should be presented in a dedicated utPLSQL view. This view should look similar to the JUnit view in Eclipse.
I love the icon that shows in the code editor menu.
Can we have a similar icon in the navigator menu?
In version 1.0.0 test results are presented in a table. Here's an example:
The table representation is good and allows to sort the result. For each column (test status, suitepath/description, runtime). This representation should be kept.
However, an additional hierarchical representation would be optimal to vizualize the tree structure. Something like this:
The icon of a test could be replaced by the test status (success, failure, error, disabled) after completion. During execution the icon could be set to "progress". The same way as in the table representation. The runtime could be added at the end of the suitpath/description in square brackets, similar to the documentation reporter (e.g. [0.042 s]
).
Indicators for warning and info will not be included in the tree view. Sorting is also not possible. If this is wanted then switching back to the table representation is the way to go.
The tree should be fully expanded by default.
Synchronization with detail tabs should work the same way as for the table representation.
The default view should be configurable in the utPLSQL preferences. Additionally switching view should be possible anytime within the utPLSQL realtime reporter, e.g. via context menu (kind of radio button logic).
The configuration to show suitepath or description should apply in the tree view as well.
It seems, that the plugin generate procedure calls from the editor if I'm after the first line (where the package name is) for the next found procedure definition.
If this has not a test annotation this leads to annoying errors, see
utPLSQL/utPLSQL#884
I think it should only generate a procedure test, when I'm on a procedure with a test anotation and else the package test.
When I tried running test via SQLDeveloper extension on utPLSQL v3.1.2 I got surprised by lack of test window.
It would be nice if we could get some info/warning to users that would inform them why tests are not running with nice new UI.
Even something like a comment in SQLDeveloper worksheet would be great to say:
-- Running with SQL worksheet
-- detected utPLSQL v3.1.2.2130
-- you need utPLSQL v3.1.7 or above to use the test-runner UI
EXECUTE dbms_session.reset_package;
SET SERVEROUTPUT ON SIZE UNLIMITED
CLEAR SCREEN
EXECUTE ut.run('UT3$USER#');
Running the realtime reporter from the SQL Developer extension produces sometimes no output.
In version < 1.0.0 the worksheet reporter was started, even if no utPLSQL was installed in the database. In that case the user got the following error in the script output:
ORA-06550: line 1, column 57:
PLS-00201: identifier 'UT.RUN' must be declared
ORA-06550: line 1, column 57:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
This was good.
Starting with version 1.0.0 the realtime reporter is the default. When utPLSQL is not installed, nothing happens. Based on the statement log, it's clear that the ut.version
statement fails.
I see two options for a user feedback.
a) an alert message informing the user, that utPLSQL is not installed
b) run the worksheet reporter as in previous versions and let the user figure out what the message means.
in Oddgen (Run test) window, suite paths are shown as duplicated if they are typed with upper/lower case in unit test package spec.
So from those two suitepaths:
--%suitepath(org.utplsql.core)
...
--%SUITEPATH(ORG.UTPLSQL.CORE)
We get separate trees/branches in navigator.
Those should be converted into common (lower) case and displayed as one tree.
The node tree is loaded eagerly, this means all nodes are loaded in one go. Oddgen is not aware of the load strategy and therefore asks the generator for every selected parent node for its child nodes recursively. This makes the generator sluggish.
A simple solution is to cache the nodes on first read. The cache will be automatically refreshed when refresh is pressed in oddgen's Generator window (old generators are discarded and new ones are created).
utPLSQL for SQL Developer releases on https://github.com/utPLSQL/utPLSQL-SQLDeveloper/releases should contain a MD5 checksum file.
For Some company policies require that.
The ability to sort results by status in the grid is great.
It wold be even better if the plugin would allow to narrow down the list of visible test to:
This could be implemented as toggles:
The functionality would be very useful when using "tree" view to display test results.
The following tree shows that the packages Core
and Reporters
are missing. But these packages are shown under All Suitepaths
(see screenshot in #54).
PL/SQL packages annotated with suite
should be included even if they have no tests (procedures annotated with test
).
When then connection name contains at sign ("@"), the "Run utPLSQL test" menu option is always disabled.
The associated connection node is not found and therefore no enable checks occur and the node stays disabled.
As a workaround do not use the at sign ("@") in the connection name.
The extension supports currently only single selection. When selecting multiple items in the Connections navigator tree the Run utPLSQL test
menu item is disabled explicitly. However, it would be useful to support multiple selection independent of issue #6.
If in the preferences "Open an unshared worksheet?" is checked, then a unshared worksheet is opened for every "Run utPLSQL test" call. So far so good.
When this unshared worksheet is closed, the connection stays open in the background. Even "Disconnect" is issued from Connections navigator tree on various levels.
How about showing the options menu as well as install process as an animated gif in the readme?
In Windows platforms the cursor position is not calculated correctly and therefore the wrong test procedure might be called, when invoking utPLSQL from the PL/SQL editor.
Example code:
create or replace package test_expect_not_to_be_null
is
--%suite(expectations - not_to_be_null)
--%suitepath(utplsql.core.expectations.unary)
--%aftereach
procedure cleanup_expectations;
--%beforeall
procedure create_types;
--%afterall
procedure drop_types;
--%test(Gives success for not null blob)
procedure blob_not_null;
--%test(Gives success for blob with length 0)
procedure blob_0_length;
-- ...
end test_expect_not_to_be_null;
/
If you position the cursor on line 13, column 26 (end of line procedure drop_types;
) and call Run utPLSQL test
then the utPLSQL path test_expect_not_to_be_null.drop_types
is executed instead of test_expect_not_to_be_null.blob_not_null
.
The reason for this wrong behaviour is, that an end-of-line is calculated as a single position in the underlying Swing component of SQL Developer, regardless of the OS platform. But the utPLSQL extensions calculates two positions for an end-of-line on Windows (CR/LF). This is a bug.
When trying to execute test: test_expect_not_to_be_null.blob_not_null
from utPLSQL test suites, the extension generates the following call:
SET SERVEROUTPUT ON SIZE 1000000
EXECUTE ut.run(''||gc_varray_name||'.drop_types');
The body of package can be seen here
It has constants at the top of the package body.
create or replace package body test_expect_not_to_be_null
is
gc_object_name constant varchar2(30) := 't_not_to_be_null_test';
gc_nested_table_name constant varchar2(30) := 'tt_not_to_be_null_test';
gc_varray_name constant varchar2(30) := 'tv_not_to_be_null_test';
procedure cleanup_expectations
is
begin
ut3.ut_expectation_processor.clear_expectations();
end;
procedure create_types
is
pragma autonomous_transaction;
begin
execute immediate 'create type '||gc_object_name||' is object (dummy number)';
execute immediate 'create type '||gc_nested_table_name||' is table of number';
execute immediate 'create type '||gc_varray_name||' is varray(1) of number';
end;
procedure drop_types
is
pragma autonomous_transaction;
begin
execute immediate 'drop type '||gc_object_name;
execute immediate 'drop type '||gc_nested_table_name;
execute immediate 'drop type '||gc_varray_name;
end;
procedure blob_not_null
is
begin
--Act
execute immediate expectations_helpers.unary_expectation_block('not_to_be_null', 'blob', 'to_blob(''abc'')');
--Assert
ut.expect(anydata.convertCollection(ut3.ut_expectation_processor.get_failed_expectations())).to_be_empty();
end;
--and so on...
end;
The following tree shows Core
and Reporters
with a package icon. This is not really wrong, since in fact a package suite
annotation exists. However these packages do not have tests (procedures annotated with test
). In such cases just folder instead of a package icon should be used.
This change should not affect the behavior of the generator.
When then connection name contains spaces the "Run utPLSQL test" menu option is always disabled.
The associated connection node is not found and therefore no enable checks occur and the node stays disabled.
As a workaround do not use spaces in the connection name.
When I open a unit test package that belongs to different user in package editor, SQLDeveloper is aware of the package owner (and is compiling the package into the owners schema).
The plugin however fails to extract the owner information and so:
ut.run
is invoked for package without owner and so it fails when trying to find unit tests.In some installations the context menu containing the Run utPLSQL test
menu item is shown with a delay of some seconds.
The main contributor to the delay is the query on all_synonyms
, which is executed more than once.
Short term solution is to use dba_synonyms
when possible. Long term solution is provided by utPLSQL/utPLSQL#571, which makes the lookup of the utPLSQL schema unnecessary.
If the preference Check availability of "Run utPLSQL test" menu option?
is enabled, then the menu option is disabled for suites without tests.
The menu option Run utPLSQL test
should be enabled for CORE
to make it consistent with issue #55.
Implement the oddgen interface to support sophisticated test generation. It is similar to #10 but the oddgen integration should support file based generation (a file for each package specification and package body) and multiselection. Naming conventions may be handled through parameter settings. Finally all suite paths in a should be evaluated and presented in a tree to just run the tests based on the suite path and not only based on schema/package/procedure paths.
Implementing the oddgen interface does not have an effect if the oddgen extension is not installed. This means it is not necessary to install oddgen. However, if it is installed, oddgen will automatically detect the implementation and provide the generator in its Generators navigator tree.
utPLSQL version: v3.0.4.1372
utPLSQL-SQLDeveloper plugin Version: 0.2.1
Database Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Problem:
“Run utPLSQL test” option went disabled
Troubleshooting:
But the problem still persists. Can you help us with necessary steps to fix the issue?
Integrate support for code coverage provided by utPLSQL in the extension.
In editor, we can use Alt+Shift+T
to run the tests.
Is it possible to have the same shortcut for navigator action?
The oddgen doesnt show unit tests if utplsql is installed into diffeent schema than ut3
Oracle SQL Developer's code template are a good way to support test-first approach.
Managing code template is a responsibility of SQL Developer. However, it would be nice if the utPLSQL extension could install a default set of templates, e.g. via a button in its preference dialog.
The following code templates should be installed:
--%test
PROCEDURE [procedure_name];
--
-- test ... case ...: ...
--
PROCEDURE [procedure_name] IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
-- populate actual
-- ...
-- populate expected
-- ...
-- assert
ut.expect(l_actual).to_equal(l_expected);
END [procedure_name];
CREATE OR REPLACE PACKAGE test_[package_name] IS
--%suite(...)
--%suitepath(alltests)
--%context([procedure_name])
--%test
PROCEDURE [procedure_name_1];
--%test
PROCEDURE [procedure_name_2];
--%endcontext
END test_[package_name];
/
CREATE OR REPLACE PACKAGE BODY test_[package_name] IS
--
-- test ... case 1: ...
--
PROCEDURE [procedure_name_1] IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
-- populate actual
-- ...
-- populate expected
-- ...
-- assert
ut.expect(l_actual).to_equal(l_expected);
END [procedure_name_1] ;
--
-- test ... case 2: ...
--
PROCEDURE [procedure_name_2] IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
-- populate actual
-- ...
-- populate expected
-- ...
-- assert
ut.expect(l_actual).to_equal(l_expected);
END [procedure_name_2];
END test_[package_name];
/
Based on an existing PL/SQL unit (package, function, procedure, type) an utPLSQL test package (spec and body) may be created based on a template. Add a dedicated context menu item in the Connections navigator tree.
This is independent of #11. It works without oddgen for a single unit. The output is generated in a new worksheet (spec and body). More generation options are provided with #11 via oddgen.
Sometimes after the installation or reinstallation of the utPLSQL extensions the context menu is missing in Connections window. It does look similar to this:
The SQL Developer About dialog shows that the extension is not fully loaded.
Opening the context menu in the Connections window (database navigator) does not trigger the load of the extension. This is a bug. The extensions should be fully loaded as soon as a context menu with a "Run utPLSQL test" option is opened (or the assigned keyboard shortcut is pressed). However, triggering works in the PL/SQL editor or in the Worksheet but not in the Connections window. This is a bug.
As a workaround the context menu in the PL/SQL editor or the Worksheet can be opened. After a restart of SQL Developer the extension is fully loaded and fully functional.
In the preferences you can configure if you want to check for the availability of the menu option "Run utPLSQL test". When set, the extension enables the menu option only when at least one test has been found. This may be slow for two reasons:
because the annotation cache needs to be updated
this happens only, if you do not use a DDL trigger to track changes, in this case changes are detected delayed, when running a test or when checking for availability of tests
large schemas
there is more data in the annotation cache and in the data dictionary, affecting the runtime performance of the queries, especially when no DDL trigger is in place, due to delayed annotation detection
Since version 3.1.8 the following functions are available in ut_runner
:
has_suites
is_suite
is_test
These functions are optimized for performance and are faster than the generic table function get_suites_info
. Hence these functions should be used whenever possible by the SQL Developer extension.
In fact these functions are available since utPLSQL version 3.1.3, but since version 3.1.8 they are considered reliable (see also utPLSQL/utPLSQL#976). Hence the use of this functions should be limited to utPLSQL version 3.1.8 and higher.
Here's an example
CREATE OR REPLACE PACKAGE BODY t2_package_name IS
--
-- test
--
PROCEDURE procedure_name IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
-- populate actual
-- ...
-- populate expected
-- ...
-- assert
ut.expect(l_actual).to_equal(l_expected);
END procedure_name;
END t2_package_name;
/
should be
CREATE OR REPLACE PACKAGE BODY t2_package_name IS
--
-- test
--
PROCEDURE procedure_name IS
l_actual INTEGER := 0;
l_expected INTEGER := 1;
BEGIN
-- populate actual
-- ...
-- populate expected
-- ...
-- assert
ut.expect(l_actual).to_equal(l_expected);
END procedure_name;
END t2_package_name;
/
In version 0.1 every package is considered a test suite and every package procedure is considered a test. If this is not true a error message similar to the following is shown in the script output panel:
Error starting at line : 2 in command -
BEGIN ut.run('PLSCOPE.LINEAGE_UTIL'); END;
Error report -
ORA-20204: Suite package plscope.lineage_util not found
ORA-06512: at "UT3.UT_RUNNER", line 101
ORA-06512: at "UT3.UT_SUITE_MANAGER", line 245
ORA-06512: at "UT3.UT_SUITE_MANAGER", line 305
ORA-06512: at "UT3.UT_RUNNER", line 84
ORA-06512: at "UT3.UT", line 105
ORA-06512: at "UT3.UT", line 272
ORA-06512: at "UT3.UT", line 324
ORA-06512: at line 1
Starting with utPLSQL v3.0.4 it is possible to get a list of annotations (Thanks to @jgebal for the example queries)
Annotated objects:
SELECT o.object_owner, o.object_type, o.object_name, o.annotations
FROM TABLE(ut3.ut_annotation_manager.get_annotated_objects(USER, 'PACKAGE')) o;
All annotations:
SELECT o.object_owner, o.object_type, o.object_name, a.name, a.text, a.subobject_name
FROM TABLE(ut3.ut_annotation_manager.get_annotated_objects(USER, 'PACKAGE')) o
CROSS JOIN TABLE(o.annotations) a;
These queries should be used for the following:
Run utPLSQL test
menu entry, if no test exists for the selected scopeRun utPLSQL test
menu entry, if one or more tests exist for the selected scopeIf a utPLSQL version less than 3.0.4 is used than the extension should still work, but will not validate utPLSQL paths (as in v0.1).
On a large schema (with more than 40'000 PL/SQL packages) the query
SELECT o.object_owner, o.object_type, o.object_name, a.name, a.text, a.subobject_name
FROM TABLE(UT3.ut_annotation_manager.get_annotated_objects(upper(?), 'PACKAGE')) o
CROSS JOIN TABLE(o.annotations) a
WHERE o.object_name = upper(?)
took 2 minutes to complete. The UI is blocked during this time. This is very bad.
When the cache is initialized the query takes about 700 milliseconds. Since this query is executed twice the context menu is delayed by 1.4 seconds, which is too much.
Since this statement might be expensive, it should be executed once.
Furthermore a mechanism should be defined to limit the wait time for the query response and to define a default behaviour.
There are following issues:
When opening a Code coverage report window by navigating: Connection->Other Users->Some User->Packages->UT_package->Right click (Code coverage), the field Include objects
is not getting populated.
Expected behavior
Include objects
gets populated the same way as it is when navigating: Connection->Packages->UT_package->Right click (Code coverage)
When tests are located in different schema then the tested code, the populated Include objects
list does not give expected results - no coverage is showing.
The objects need to be prefixed with schema name.
Below animation illustrates both issues as well as the correct outcomes
Here's an example of a generated package specification without comments:
CREATE OR REPLACE PACKAGE test_type_util IS
--%suite(test_type_util)
--%suitepath(alltests)
--%context(dedup)
--%test
PROCEDURE dedup1;
--%test
PROCEDURE dedup2;
--%endcontext
END test_type_util;
/
The initial indentation within the package is missing. It should look as follows (using 3 indent spaces):
CREATE OR REPLACE PACKAGE test_type_util IS
--%suite(test_type_util)
--%suitepath(alltests)
--%context(dedup)
--%test
PROCEDURE dedup1;
--%test
PROCEDURE dedup2;
--%endcontext
END test_type_util;
/
Same issue with the package body.
Other indentations are not affected.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.