tag:blogger.com,1999:blog-7849367040589270673.post3835440958934411405..comments2024-03-21T22:50:39.997-07:00Comments on Obsessed with Oracle PL/SQL: Recommendations for unit testing PL/SQL programsSteven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger38125tag:blogger.com,1999:blog-7849367040589270673.post-91322965421721675392020-06-25T10:57:01.193-07:002020-06-25T10:57:01.193-07:00Sorry, Bhumika, I have not used that report. If yo...Sorry, Bhumika, I have not used that report. If you have a Twitter account, I suggest asking about this of either/both @thatjeffsmith or @oraclesqldev.<br /><br />Also, if you are on 12.2 or higher, we now offer a utility that generates code coverage data. For more information:<br /><br />https://oracle-base.com/articles/12c/dbms_plsql_code_coverage-12cr2Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-296034128601329412020-06-22T07:55:52.531-07:002020-06-22T07:55:52.531-07:00Nice blog buddy,
Well, Unit Testing is a level of...Nice blog buddy,<br /><br />Well, Unit Testing is a level of software testing where individual units/ components of a software are tested. The purpose is to validate that each unit of the software performs as designed. A unit is the smallest testable part of any software. It usually has one or a few inputs and usually a single output.Jessicanoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-32829949284059406282020-06-22T00:25:54.984-07:002020-06-22T00:25:54.984-07:00Hi Steven, I have just started doing the unit test...Hi Steven, I have just started doing the unit testing of my stored procedures using SQL developer. But while checking the code coverage report, it seems that it is not showing the number of executable/ relevant lines. It just displays the total number of lines and the number of lines covered. It appears to be quite difficult to find out the exact code coverage percentage. Is there any other way of displaying the executable lines or I need to move to utPLSQL for finding out the exact figures?Bhumikahttps://www.blogger.com/profile/06396694672860645710noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-67742649640945724632020-03-02T03:46:53.882-08:002020-03-02T03:46:53.882-08:00This integration is accomplished by using the unit...This integration is accomplished by using the unit testing API. You can find more information about it here: https://docs.oracle.com/en/database/oracle/sql-developer/19.4/rptug/sql-developer-unit-testing.html#GUID-1B5A170C-349C-4990-B695-650E652D7AA8Steven Feuersteinhttp://oracle.com/plsqlnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-44918868233988162252020-03-02T02:59:35.249-08:002020-03-02T02:59:35.249-08:00Hi, "SQL Dev unit tests can also be integrate...Hi, "SQL Dev unit tests can also be integrated into continuous integration scripting" please elaborate on this.Anonymoushttps://www.blogger.com/profile/10317363153942067725noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-60984854724228407152019-10-31T12:29:31.996-07:002019-10-31T12:29:31.996-07:00Preethi, I am hosting an AskTOM Office Hours sessi...Preethi, I am hosting an AskTOM Office Hours session on PL/SQL testing on November 5 9 AM Eastern time. <br /><br />I wonder if you are still using SQL Developer for unit testing and if so, would you be able to join the session and share some of your experience, offer tips for others?StevenFeuersteinhttps://www.blogger.com/profile/13931412532278395973noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-79023539905987810252019-10-31T12:09:12.764-07:002019-10-31T12:09:12.764-07:00Hi Gwindor, I am very interested to know more abou...Hi Gwindor, I am very interested to know more about your experience in developing your tests in JS for PL/SQL! Please let me know the best way to contact you in regards to that. Thanks! Allie Lerchehttps://www.blogger.com/profile/13030254259443443136noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-32689741370602522019-07-17T03:51:47.004-07:002019-07-17T03:51:47.004-07:00I would start with the documentation set available...I would start with the documentation set available at utplsql.orgStevenFeuersteinhttps://www.blogger.com/profile/13931412532278395973noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-40645689202687767342019-07-16T10:55:57.089-07:002019-07-16T10:55:57.089-07:00This comment has been removed by the author.aneesh.parasseriyilhttps://www.blogger.com/profile/05826111459210608102noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-44348153926045557472019-02-18T04:13:51.769-08:002019-02-18T04:13:51.769-08:00Sorry have not done this myself. Check thatjeffsmi...Sorry have not done this myself. Check thatjeffsmith.com or ask @thatjeffsmith on Twitter.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-75462060981530575782019-02-16T10:34:35.952-08:002019-02-16T10:34:35.952-08:00Hi I was using sql developer for uni test case. D...Hi I was using sql developer for uni test case. Does <br /> integration Of this sql developer test suite to Jenkins requires any shell script or pom.xml change.how can I map it. preethahttps://www.blogger.com/profile/04706414578753483300noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-3310268622622632752017-08-22T13:26:53.166-07:002017-08-22T13:26:53.166-07:00Alan, I have not had time to work with v3 so I can...Alan, I have not had time to work with v3 so I cannot report to you on the changes. My impression is that the utility will now do more for you, but you still must write those test packages yourself.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-81865235425747467432017-08-16T19:39:32.074-07:002017-08-16T19:39:32.074-07:00Hi Steve - now that it is nearing the end o 2017 a...Hi Steve - now that it is nearing the end o 2017 and version 3.02 of utPlsql just came out - any further comments about it? I have long wanted to implement database unit testing at my company. I am excited by utPLSQL's new version but, as you say, you have to do a lot of the work by hand.<br /><br />Thoughts?Anonymoushttps://www.blogger.com/profile/11509770317729784271noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-78055749204057114232017-05-05T05:50:25.787-07:002017-05-05T05:50:25.787-07:00Amazing, thanks a lot my friend, I was also siting...Amazing, thanks a lot my friend, I was also siting like a your banner image when I was thrown into Selenium.When I started learning then I understood it has got really cool stuff.<br />I can vouch webdriver has proved the best feature in Selenium framework.<br />Thanks a lot for taking a time to share a wonderful article.<br />Sherlin Joneshttps://www.blogger.com/profile/01635329256994221874noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-35683570969895251792017-03-21T12:52:16.178-07:002017-03-21T12:52:16.178-07:00And well, actually I have worked with Raimonds Sim...And well, actually I have worked with Raimonds Simanovskis, the author of the original idea of testing PL/SQL in Ruby.Gwindorhttps://www.blogger.com/profile/06338525996352695874noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-70142670174671336442017-03-21T12:47:59.884-07:002017-03-21T12:47:59.884-07:00Hi,
We at Intrum Justitia SDC (Riga) have recentl...Hi,<br /><br />We at Intrum Justitia SDC (Riga) have recently achieved a goal to implement automated testing for a big corporate system with PL/SQL back-end and Angular 2.0 front-end, which has been started to be developed recently. After investigating what's there for PL/SQL testing, we took the idea to test it with a dynamic language and developed our own tool.<br />The new user interface is written in JavaScript and runs in a browser. To make it possible I have created an HTTP driver for Oracle, which allows to manipulate data and to call PL/SQL procedures with JSON requests, receiving the data also in JSON format. Virtually all Oracle data types are supported, including the complex ones at any nesting depth. Transactions (including the global multi-user ones) are also supported.<br />So it was very natural for us to create a tool, which will allow to write automates tests for the PL/SQL backend in JavaScript.<br />As a template we took JavaScript community established TDD standard syntax with suite() and test(), augmented it with the expect.js library and wrapped in a Java application (to secure the passwords).<br />As a result we already have hundreds of tests which have been written by all PL/SQL developers AND testing engineers, which haven't had any programming experience before!<br />The tool now slowly gets a WEB-GUI, I plan to integrate it with Jenkins, TestRails and possibly with our future Oracle CI tool :)<br />It now counts as out proprietary product, however, if you are interested in the details and out experience, please leave a comment or contact me.<br /><br />P. S. Steve, I have once participated in your seminar "PL/SQL best practices" in Riga (I guess around 10 years ago or so :)).<br /><br />Cheers.Gwindorhttps://www.blogger.com/profile/06338525996352695874noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-18916727421694763972016-11-04T11:41:55.920-07:002016-11-04T11:41:55.920-07:00Not that I know of, Anil.Not that I know of, Anil.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-82847599334510837042016-11-04T11:40:26.623-07:002016-11-04T11:40:26.623-07:00Right now SDUT does not support REF Cursors and %R...Right now SDUT does not support REF Cursors and %ROWTYPE as parameters. Is there a workaround for this, other than writing some wrapper procedure?Anil Kumar Dhttps://www.blogger.com/profile/11000555773197384473noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-56226810962980055822016-08-16T23:48:48.765-07:002016-08-16T23:48:48.765-07:00Nice post. Your readers might also find real user ...Nice post. Your readers might also find real user reviews for Oracle SQL Developer on IT Central Station to be helpful. As an example, this user writes that one particularly valuable feature is, "Oracle SQL Developer Data Modeler. No PL/SQL IDE has such modelling capabilities, as far as I know. This is a killer feature for me." You can read the rest of his review here: https://goo.gl/MG3nQH.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-7830216647569483752016-05-01T15:18:40.010-07:002016-05-01T15:18:40.010-07:00What you say is sad but true. Learning another la...What you say is sad but true. Learning another language just to test your PL/SQL code can put some people off. But, in my opinion, the benefits far outweigh any costs. Also, you just need the Ruby basics which a developer should be able to pick up very quickly from any number of sites and books.<br /><br />One caveat (for my slow brain) is that the heavily used block functionality in Ruby may take a solely PL/SQL developer a bit of brain retraining but other than that Ruby is a very straight forward language that is designed around the concept of least astonishment. You won't get the hmmm, I recall there are 3 kinds of arrays in PL/SQL, which one should I use now or hmm, now I'm using PL/SQL object types and need to access SQL types where's that no-man's land between SQL types and object types where they havne't built the bridge and I can't get there from here types of questions.<br /><br />Learning a different language will exercise that brain and put it into a higher gear. Like playing those puzzle games you recommend.<br /><br />We PL/SQL developers must realize that we cannot write everything in PL/SQL. Even with Apex, which I really like BTW (Check out Selenium for testing Web UI's using RSpec). Maybe it's a bit easier when developing for Oracle EBS since the tech stack is so large. You already need to know shell scripting, some perl, PL/SQL, of course, Java, and even Pro*C was helpful ten years ago. Now we are in or entering the post Java era and Groovy is making it's way into the stack. So what's another syntax?<br /><br />Maybe someday Oracle will ask Raimonds to present on plsql-spec for the good of PL/SQL developers and not based upon whether it competes with OATS or some other pay for Oracle product. I've submitted to present on plsql-spec multiple times for OOW but never got the green light. So I'm not holding my breath.<br /><br />All I can say is that in my opinion PL/SQL developers need to employ TDD and be open about choosing the best options for doing so. Once you see how much better TDD is for the quality of the software you deliver you won't go back and adding methods to your PL/SQL package procedures and functions the way plsql-spec allows feels quite natural and is really cool in my opinion!<br /><br />Keep up the great work of spreading the word on testing.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-75746649715787338172016-05-01T11:18:52.775-07:002016-05-01T11:18:52.775-07:00Thanks, Jason. Very interesting! I imagine for man...Thanks, Jason. Very interesting! I imagine for many PL/SQL developers the idea of learning ruby is rather off-putting in terms of leveraging this framework. Any suggestions for a fast, easy path to having enough ruby knowledge to use plsql-spec?Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-79054377084772980022016-04-29T11:18:05.079-07:002016-04-29T11:18:05.079-07:00I should have also mentioned how easy it is to aut...I should have also mentioned how easy it is to automate the plsql-spec tests by adding them to a continuous integration server like Hudson.<br /><br />And the added benefit of using a language like Ruby that is a joy to program in.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-28995736658331092662016-04-29T11:14:10.747-07:002016-04-29T11:14:10.747-07:00After trying many testing frameworks, all of those...After trying many testing frameworks, all of those we tried were mentioned here, we picked and love Ruby plsql-spec. That was more than 5 years ago. It's integration with Oracle EBS makesit even better for our shop. plsql-spec allows us to perform small unit tests to larger integration tests like creating POs and invoices and then matching quickly.<br /><br />Keep pushing TDD!<br /><br />Thanks,<br />JasonAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-45024292187839718072016-01-21T07:42:12.804-08:002016-01-21T07:42:12.804-08:00@swapnali, thanks glad it was useful. Answer to yo...@swapnali, thanks glad it was useful. Answer to your question: no, %rowtype and record arguments are not yet supported.Steven Feuersteinhttps://www.blogger.com/profile/18405765731886460622noreply@blogger.comtag:blogger.com,1999:blog-7849367040589270673.post-77546497276636652462016-01-15T08:42:07.657-08:002016-01-15T08:42:07.657-08:00Hi,
Very informative blog i must say.
I just have...Hi,<br />Very informative blog i must say. <br />I just have a quick question ;does sql developer unit testing support %rowtype data types as input or out put from a function/procedure?swapnalihttps://www.blogger.com/profile/02163250911119802099noreply@blogger.com