codemonth blog codemonth blog - One project every month - making stuff better. Thu, 18 Jan 2018 04:54:28 +0700 Thu, 18 Jan 2018 04:54:28 +0700 30 Random null value in plsql generated data 55DB7F809BFA12E3E050007F0100789A Thu, 03 Aug 2017 22:51:44 +0700 I have added another small feature to the testdata library. The ability to create random data is good, but sometimes you also need to have "gaps" in that data. So I made it possible to randomly create null instead of a value for any generated field. Simply surround the generator function in parentheses and add how big a percentage the chance are of a null value after the parentheses. Topological sorting in PLSQL 559E1D040547AF77E050007F01002209 Mon, 31 Jul 2017 21:36:54 +0700 For my <a href="">testdata_ninja</a> library, I recently created the <a href="">field auto reference feature</a>, where a value from one field can be used as an input to a generator for another field. Part of this feature involves parsing all columns and building a dependency list (<a href="">Directed Acyclic Graph</a>) sorting all columns correctly. Since everything in the library is build purely in plsql, I don't store anything in tables. So if my data is not in a table, I cannot use sql as one would normally do it. I couldn't find any code out there that did what I wanted, so like much of the other stuff I do, I had to build it myself. Chinese support added to parts of the plsql random data generator 54D58D26DB2A1FC6E050007F01003861 Fri, 21 Jul 2017 22:19:12 +0700 For quite a while I have been wanting to add Chinese support to some of the dbms_random functions, and this last weekend I finally got some time to work a little bit on it. So now the library support Chinese output in functions in the core_random package, text_random package and location_random package. PLSQL Test data generators with auto back reference fields 52B627AADF8417C6E050007F010042DA Sun, 25 Jun 2017 22:02:14 +0700 So in another entry I described how to <a href=""> manually reference back to other fields</a> to use as input values in other columns for the row generator. What could be a bit more user friendly than that? Maybe using auto reference fields. So if you are using a generator in one of your columns that outputs something that can be used as input to another generator, it is now done automatically. Recursive test data using plsql and the testdata_ninja package 52B210E69A3F516BE050007F010037EE Sat, 24 Jun 2017 17:09:41 +0700 Just the other day I was running a test of a system at work. I have already run several tests against this system, so I already have a test data generator to generate clients in the system. This time though I had to generate some orders within the system. So a really quick way to do that is to reuse the existing generator for the client id reference (a foreign key in the real system) from the orders data rows. This way I don't have to create an actual client table. I can just reference the plsql test data generator directly in my definition of my order generator. PLSQL test data from cursor to table or from cursor to csv 52527FEBC9838E7DE050007F01007DC5 Mon, 19 Jun 2017 23:08:47 +0700 When you are creating test data it is usually either to create a quick test table for a piece of code, or because you need to simulate data extraction to another system. That is why any generator created with testdata_ninja includes methods to do that quickly. Every generator created includes a to_table method and a to_csv table function. Even more test data generated using the testdata_ninja plsql package 519A2E2EE2359580E050007F010074F7 Sat, 10 Jun 2017 19:14:23 +0700 Last entry to the testdata_ninja blog roll, was about how to create iterators that were unique when you create test data rows. In this entry I will show how to use back references to other column values to use them as input to other row generator functions. One example could be that you were creating a table of employees and you wanted the name to be more realistic. So we could have a country column in the row as well and use that as an input to create the name, so it looks like a name from that country. Extending Codemonth search to include Oracle Text fuzzy and stem searches 4F273D15A90FB8B6E050007F010064DE Wed, 10 May 2017 15:16:22 +0700 My previous entry showed how the contains operator of Oracle Text made it possible to build a search function for my website. It was a basic text search with no additional capability. For quick recap of how the query looked, this is what I could run to search the description of my projects for the word package. Oracle text and implementing codemonth website search 4F05A541DF061EBAE050007F01000BEF Mon, 08 May 2017 23:11:46 +0700 I promised on the ODTUG codetalk series to implement search on the codemonth website. So this entry is a short description of the first steps I have done to add simple search capability to the site. PLSQL promises vs dbms_scheduler chains 4ED68FB41473CD23E050007F01007873 Sat, 06 May 2017 15:01:32 +0700 10 lines of code vs 80 lines of code! When implementing JS promises in PLSQL, one of the main use cases is the ability that promises gives, to easily chain together dependent steps defined as functions. Also the ability to easily pass output from one step along to the next step in the chain. Adding support for Edition Based Redefinition for PROMISES_NINJA 4E61B48C078C1354E050007F01007F1B Sun, 30 Apr 2017 19:36:45 +0700 As part of another project, where I need to support the Edition Based Redefinition feature, I needed to add support inside the promise class for the dbms_scheduler job_class parameter. The job_class parameter can point to a service, and a service can be assigned a default edition to run in. This allows me to execute code inside a promise, and automatically support any edition that has been created in the database, as well as assigned to a service. Version your Oracle ORDS REST API using Edition Based Redefinition 4DD602D1F1385ACEE050007F01000E47 Sun, 23 Apr 2017 20:56:00 +0700 <strong style="font-style: italic;">Updated, May 4 2017: New approach</strong> One of the last entries in my blog described how I could <a href="">quickly publish all random_ninja functions as rest endpoints</a>. While I am still waiting for the ORDS team at Oracle to come out with automatic publish of functions and procedures including input parameters, I wanted to add another layer of functionality to my REST endpoints. Versioning; And for that we can use the cool Oracle feature called Edition Based Redefinition. Completing JWT_NINjA for full JWT support 4CBC2BC376CF83D6E050007F01003814 Sun, 09 Apr 2017 09:08:11 +0700 <p>In the single <a href="">blog post</a> I wrote on the JWT_NINJA package back in June last year, I ended the post by saying that I needed to implement both a verify and a parse function for the package.</p><p>Well, it took me a year, but I finally had a reason to go and implement it. As I am getting into Oracle ORDS and trying to get some experience in building REST services, I want to be able to use <a href="">JWT</a> as the token generator for the application that I am building.</p><p>So a quick refresh on how to generate a token using the package:</p> <pre><code class="language-sql"> select jwt_ninja.jwt_generate(p_signature_key => 'my secret password', p_reg_claim_issuer => 'My Application', p_reg_claim_subject => '147654') as token from dual; TOKEN ------------------------------------------------------------------------------------------------------------------------------------- eyAiYWxnIjogIkhTMjU2IiwgInR5cCI6ICJKV1QiIH0.eyAiaWF0IjogMTQ5MTcxNzIxODAwMCwgImlzcyI6ICJNeSBBcHBsaWNhdGlvbiIs ICJzdWIiOiAiMTQ3NjU0In0.Uy6KeKeO1/52+URTbhk1+spOIO3OYamLhViPJ1ZmjUU </code></pre> <p>So now that we have a token, we would store that on the client side, and every request from the client would include this token for us to validate. So to validate the token there is now a procedure jwt_ninja.jwt_verify_and_decode that will do just that. The third argument (p_do_parse) indicates if you only want the token to be verified or if you want to parse the fields inside the payload. The default is to only verify the validity of the signature and the message and not do the parse. If it is set to true, the values of the different <a href="">JWT claims</a> will be set and found in the output parameters of the procedure.</p><p> <pre><code class="language-sql"> declare mysecret varchar2(4000) := 'my secret password'; mytoken varchar2(4000) := jwt_ninja.jwt_generate(p_signature_key => 'my secret password', p_reg_claim_issuer => 'My Application', p_reg_claim_subject => '147654'); doparse boolean := false; myresult boolean; l_reg_claim_issuer varchar2(4000) := null; l_reg_claim_subject varchar2(4000) := null; l_reg_claim_audience varchar2(4000) := null; l_reg_claim_expiration date := null; l_reg_claim_notbefore date := null; l_reg_claim_issuedat date := null; l_reg_claim_jwtid varchar2(4000) := null; begin jwt_ninja.jwt_verify_and_decode(mytoken, mysecret, doparse, myresult, l_reg_claim_issuer, l_reg_claim_subject, l_reg_claim_audience, l_reg_claim_expiration, l_reg_claim_notbefore, l_reg_claim_issuedat, l_reg_claim_jwtid); if myresult then dbms_output.put_line('Verified'); dbms_output.put_line('Iss: ' || l_reg_claim_issuer); dbms_output.put_line('Sub: ' || l_reg_claim_subject); else dbms_output.put_line('Not verified'); end if; end; / </code></pre></p> <p>So know I can "close" this codemonth for now, since I can start to use it in my other project.</p> Take a random REST 4CA6C3C984F556E6E050007F01006CD5 Sat, 08 Apr 2017 10:07:37 +0700 <p>I needed to test <a href="">ORDS</a> for some webservice related projects at work. So I decided to build a rest interface for all the different random data functions in the <a href="">RANDOM_NINJA</a> package to test out, just how easy it is.</p><p>First order is to setup ORDS. Like always Tim over at <a href="">oracle-base</a> have a couple of really good articles on installing and setting up ORDS itself. So no need for me to duplicate that.</p><p>After setting up ORDS, and enabling my schema for rest services like in this <a href="">oracle-base article</a>, it is time to enable the functions in the different data domain packages. I decided to keep the URL scheme simple, so that the path would be /[package_name]/[function_name]/ for any of the functions that are part of the RANDOM_NINJA package.</p> Generating randomized incremental values 4AB10279DD66941AE050007F01003083 Tue, 14 Mar 2017 10:35:06 +0700 <p>In the previous entries, I explained the <a href="">basics of testdata_ninja</a> and then I explained how to <a href="">create foreign key relations</a> between your test data and existing tables. This time I will show how you can create data that are random but always increasing, either to create time patterns or to create unique id columns for primary keys. I will extend the format that I have already used in the previous entries, so the code will be familiar. If we look at the output of the test_days generator we have created, it currently looks like this: <pre><code class="language-sql"> SQL> select * from table(tdg_test_days.test_days); NAME BIRTHDAY MY_PK_REF -------------------------------------------------- ------------------ ---------- Arianna Turner 02-MAR-93 00:24:58 1 Sofie Dam 17-MAY-91 17:21:09 1 Camila King 27-OCT-62 18:16:56 10 Malik Henriksen 24-NOV-65 06:23:51 10 .... [more rows] .... </code></pre> There is no clear column with possible unique values, so I will create a new column where the data generator is an "incrementor". Like all the other different data fields, it is in the 3 field definition of the block that we define how to generate the data. So far the format looks like this: <pre><code class="language-sql"> name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5 </code></pre> So let us add a column in the beginning, that increments randomly but is unique nonetheless: <pre><code class="language-sql"> people_pk#number#^numiterate~1~3¤5 </code></pre> and here is the explanation of the fields, which are separated by the "~" character: <ul> <li>incrementor type (numiterate for numbers and datiterate for dates)</li> <li>start from (number or date to start increment from)</li> <li>increment range (for numbers min and max separated by the "¤" character and dates interval type (seconds, minutes etc), min and max separated by the "¤" character for example: seconds¤1¤5)</li> </ul> So if we now take a look at the full format in the code, it would look like this: <pre><code class="language-sql"> declare the_format varchar2(4000) := 'people_pk#number#^numiterate~1~3¤5@name#varchar2(50)#person_random.r_name@birthday#date#person_random.r_birthday@my_pk_ref#number#£t_people¤person_num_pk¤simple¤5'; begin testdata_ninja.generator_create('test_days', the_format); end; / </code></pre> And would create a result like the following: <pre><code class="language-sql"> 1 Sigurd Andreasen 20-NOV-63 12:33:49 1 6 Ella Madsen 30-JAN-80 07:09:39 1 9 Alexandra White 16-JUL-53 16:32:07 1 13 Benjamin Holst 08-APR-88 13:39:38 1 17 Lea Jepsen 24-MAY-88 17:21:01 1 21 Eli Morgan 07-AUG-86 15:54:27 10 26 Oskar Bruun 20-JUL-69 22:40:56 10 31 Jens Friis 26-APR-63 16:32:21 10 35 Camila Hall 05-DEC-78 23:30:19 10 </code></pre>