tag:blogger.com,1999:blog-78493670405892706732024-03-19T04:56:09.851-07:00Obsessed with Oracle PL/SQLFor the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.comBlogger311125tag:blogger.com,1999:blog-7849367040589270673.post-25450099467657709882020-12-14T14:41:00.003-08:002020-12-14T14:41:33.812-08:00New Ranking Tags Feature of Oracle Dev Gym <iframe allowfullscreen="" class="BLOG_video_class" height="362" src="https://www.youtube.com/embed/RovHBc0oylM" width="435" youtube-src-id="RovHBc0oylM"></iframe><br /><br /><p></p><div>You can take quizzes, workouts and classes at the Oracle Dev Gym solely with the objective of learning and improving your expertise in Oracle technologies.</div><div><br /></div><div>You can also play <i>competitively</i>, which means that you will be ranked when you take a tournament quiz. You can see all the rankings for tournaments by clicking on the Leaderboard tab.</div><div><br /></div><div>But what if you'd like to see how you ranked compared to your co-workers or friends?</div><div><br /></div><div>What if your entire dev team wants to take a workout together and then compare how you all did?</div><div><br /></div><div>I have one answer to those questions: ranking tags!</div><div><br /></div><div>Click on your name in upper right, select Profile Settings.</div><div><br /></div><div>Click on the Ranking Tags tab.</div><div><br /></div><div>It's empty! OK, now it's time to talk to your friends or co-workers. Decide on a tag that you can use to identify your little circle. Aim for something obviously unique. These are "just" tags, so if you use something like "ORADEV" and so does someone else, you will be in the same ranking filter list.</div><div><br /></div><div>So decide on a tag, invite your friends to do the same thing. You can see everyone with the same tag in the profile settings page.</div><div><br /></div><div>Once you've done that, you can choose the tag both on Leaderboard ranking reports and on a new Workout Rankings modal. You will see a button to open this modal when (a) you've completed the workout and (b) you've added at least one ranking tag to your profile.</div><div><br /></div><div>The two minute video above shows you all of this. Enjoy!</div>Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-51662719656171015582020-12-01T08:09:00.002-08:002020-12-01T08:12:51.320-08:00Changes to Oracle Dev Gym Tournaments in 2021<br/>
<p>The more things change, the more they stay the same?</p>
<p>Or is it: No matter how much we want things to stay the same, they inevitably must change?</p><p>Or perhaps it's: 2020's been such a horrible year in many respects, let's change things up in 2021 in every way we can!</p>
<p>Whichever platitude or desire fits best, at the <a href="DevGym.oracle.com" target="_blank">Oracle Dev Gym</a>, things are about to change.</p><p>From 2010 to 2014, this website (then known as the PL/SQL Challenge) offered daily quizzes on PL/SQL, plus SQL, Logic, database design, APEX (on and off), Java (for a while). In 2014, when I rejoined Oracle, the PL/SQL quiz also went to a weekly cadence.</p><p>For 2021, we are going to switch things up a bit more. Starting January 9, the SQL, PL/SQL and database design quizzes will be "converged" into a single weekly Oracle Database quiz.</p><p>I know that this change may not be very popular with the many players who have, with incredible dedication, taken pretty much every single one of those quizzes over the years. </p><p>I hope, though, that everyone who visits the Dev Gym will benefit from the wider scope of topics covered in the new quiz, the focus on newer features, and the additional workouts and classes that will be offered on the site.</p><p>You can read more of the details about this change on Chris Saxon's <a href="https://blogs.oracle.com/sql/new-tournament-quiz-format-coming-on-oracle-dev-gym-in-2021">SQL blog</a>.</p><p><br /></p>Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-87616741360962260632020-09-29T12:02:00.005-07:002020-10-19T10:54:43.070-07:00Different SYSDATE behaviors in SQL and PL/SQL<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtL1ir-A0eVOtgePCieceMDL6siN8PNNwLDM3pHMvOsG5QVfzpdMMcJ7WcURZkkBzL3-vUlAtYzvIaRUWZACKGX-L9iO0WMtVjh1c-9tatf_raemzOXbq390K2VGV26D0wVkY3sBIuU0Y/s913/a.png" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="561" data-original-width="913" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtL1ir-A0eVOtgePCieceMDL6siN8PNNwLDM3pHMvOsG5QVfzpdMMcJ7WcURZkkBzL3-vUlAtYzvIaRUWZACKGX-L9iO0WMtVjh1c-9tatf_raemzOXbq390K2VGV26D0wVkY3sBIuU0Y/s16000/a.png" /></a></div><br /><p><br /></p><p>The SYSDATE function is available in both SQL and PL/SQL. They both return the current date-time (down to nearest second) for the database, So it would be reasonable to assume that they "act" the same in both SQL statements and PL/SQL blocks.</p><p>That would, however, be a bad assumption to make, because in reality:</p><blockquote><p>In SQL, SYSDATE is called just once for the entire statement.</p><p>In PL/SQL, SYSDATE is called every time it is invoked.</p></blockquote><p>Wow. Mind blown. </p><p>Let's take a look.</p><p>In the script below, I create a table and insert four rows. Then I create a package that keeps track of distinct dates added to a collection and show those dates. Finally, a function that uses the package.</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE tab (id INT)
/
BEGIN
INSERT INTO tab VALUES (1);
INSERT INTO tab VALUES (2);
INSERT INTO tab VALUES (3);
INSERT INTO tab VALUES (4);
COMMIT;
END;
/
CREATE OR REPLACE PACKAGE tracker
AUTHID DEFINER
IS
TYPE when_t IS TABLE OF INTEGER INDEX BY VARCHAR2 (100);
dates when_t;
PROCEDURE add_date (date_in IN DATE);
FUNCTION store_and_return_date (date_in IN DATE,
call_in_plsql_in IN VARCHAR2)
RETURN DATE;
PROCEDURE show_count;
END;
/
CREATE OR REPLACE PACKAGE BODY tracker
IS
PROCEDURE add_date (date_in IN DATE)
IS
BEGIN
dates (TO_CHAR (date_in, 'YYYY-MM-DD HH:MI:SS')) := 0;
END;
PROCEDURE show_count
IS
l_index VARCHAR2 (100) := dates.FIRST;
BEGIN
DBMS_OUTPUT.put_line ('Date Count = ' || dates.COUNT);
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (l_index);
l_index := dates.NEXT (l_index);
END LOOP;
dates.delete;
END;
FUNCTION store_and_return_date (date_in IN DATE,
call_in_plsql_in IN VARCHAR2)
RETURN DATE
IS
d DATE;
BEGIN
/* This is the value for SYSDATE passed in from The SELECT */
add_date (date_in);
IF call_in_plsql_in = 'YES'
THEN
/* Now we call SYSDATE inside PL/SQL. Will it be the same
value as passed in for date_in, or different? The
count in the collection will tell us */
add_date (SYSDATE);
END IF;
DBMS_SESSION.sleep (1);
RETURN date_in;
END;
END;
/
</code></pre>
When I call the store_and_return_date, I can specify whether or not I want SYSDATE to be called again from within the PL/SQL function - which will be invoked below inside a SELECT statement.<div><br /></div><div>Notice that I use the date (converted explicitly to a string) as the index in the associative array. That makes it easy to keep track of <i>unique</i> timestamps added.</div><div><br /></div><div>In the first query below, I do <i>not</i> call SYSDATE inside PL/SQL, only in the call <i>to </i>the function, which happens inside the SQL engine.</div><div><br /></div><div>In the second query, I call SYDATE in PL/SQL in addition to the invocation in the SELECT statement.<br /><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>SELECT tracker.store_and_return_date (
SYSDATE, call_in_plsql_in => 'NO')
FROM tab
/
BEGIN
tracker.show_count;
END;
/
SELECT tracker.store_and_return_date (
SYSDATE, call_in_plsql_in => 'YES')
FROM tab
/
BEGIN
tracker.show_count;
END;
/
</code></pre>And here's the output:<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>Date Count = 1
2020-09-29 01:28:58
Date Count = 4
2020-09-29 01:29:02
2020-09-29 01:29:03
2020-09-29 01:29:04
2020-09-29 01:29:05
</code></pre></div>
<p>
And there you see the difference between SQL and PL/SQL engines when it comes to executing SYSDATE. There are four rows in the TAB table. But the value for SYSDATE when executed in SQL doesn't change for the duration of the query, so there is just one element in the array.
</p>
<p>
When I ask for SYSDATE to also be called in PL/SQL, it is executed and the current date-time returned - even when that function is called from within a SQL statement.</p> Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com6tag:blogger.com,1999:blog-7849367040589270673.post-79914040688542565652020-09-02T10:52:00.000-07:002020-09-02T10:52:23.847-07:00Implementing Enhanced Table Auditing in the Oracle Dev Gym<p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyEUPho1fOjMYcm9U84yglD60zYNsCl41Fjw4SA6Onj-g-0u8IZqKuoh5OObD-p7Zvu6xVaPwa1Oe798P4OZw9d6oqanZgECpRdxt1oEbMSXkBGG9mQfyjeRUUVajbCJujxtMbvaqV5TA/s464/a.png" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="348" data-original-width="464" height="278" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyEUPho1fOjMYcm9U84yglD60zYNsCl41Fjw4SA6Onj-g-0u8IZqKuoh5OObD-p7Zvu6xVaPwa1Oe798P4OZw9d6oqanZgECpRdxt1oEbMSXkBGG9mQfyjeRUUVajbCJujxtMbvaqV5TA/w371-h278/a.png" width="371" /></a></div><br />I'd like to start off this post by thanking <a href="https://twitter.com/connor_mc_d">Connor McDonald</a>, a member of my Oracle Developer Advocates team.<p></p><p>I'm a traditional sort of Oracle developer. I learned a long time ago to use row-level triggers to keep track of who created and changed a row and when. </p><p>So, for example, for the DG_CLASSES table of the <a href="devgym.oracle.com" target="_blank">Oracle Dev Gym</a> (which is used both for Dev Gym classes and AskTOM Office Hours), I have these two triggers:</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>TRIGGER dg_classes_bir
BEFORE INSERT
ON dg_classes
FOR EACH ROW
DECLARE
BEGIN
:new.created_on := SYSDATE;
:new.created_by := qdb_config.apex_user;
:new.changed_on := SYSDATE;
:new.changed_by := qdb_config.apex_user;
END dg_classes_bir;
</code></pre>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>TRIGGER dg_classes_bur
BEFORE UPDATE
ON dg_classes
FOR EACH ROW
DECLARE
BEGIN
:new.changed_on := SYSDATE;
:new.changed_by := qdb_config.apex_user;
END dg_classes_bur;
</code></pre>
<p>Clearly, this kind of auditing has some drawbacks, including:</p><p></p><ul style="text-align: left;"><li>I have no idea <i>what</i> was changed, just that <i>something</i> was changed.</li><li>Those triggers fire all the time, so if I wanted to, say, do a batch load of rows and <i>preserve</i> the existing created-changed column values, I have to disable the triggers, do the load, re-enable the triggers.</li></ul><div>Connor recently published a blog post and utility for <a href="https://connor-mcdonald.com/2020/08/04/level-up-your-audit-trigger-game/" target="_blank">enhanced table auditing</a>. I strongly suggest you read that before continuing. You will understand more thoroughly the drawbacks of my approach. And you will understand why I decided to use his utility in the Oracle Dev Gym. </div><div><br /></div><div>I'd like to share how I went about it, including some pages I added to the Dev Gym to give us easy access to the auditing data. Perhaps you'd want to do something similar.</div><div><br /></div><div>The installation of Connor's code went smoothly (not that I expected anything differently). Then it was time to generate all the code needed for auditing on a select number of key tables. Again, Connor made it really easy by offering a single procedure, , that created all the code for a specific table. </div><div><br /></div><div>I built a layer of code over that to handle multiple tables and disable those pesky, out-of-date triggers:</div><div>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
s VARCHAR2 (100) := 'QDB_PROD';
a VARCHAR2 (100) := 'EXECUTE';
TYPE tables_t IS TABLE OF VARCHAR2 (1000);
l_tables tables_t
:= tables_t (
'DG_CLASSES',
'QDB_COMPEV_ANSWERS',
'QDB_COMP_EVENTS',
'DG_STUDENTS',
'QDB_USERS',
'DG_WORKOUTS',
'DG_USER_WORKOUTS');
BEGIN
/* Enable auditing in the Dev Gym schema */
INSERT INTO schema_list VALUES ('QDB_PROD');
FOR indx IN 1 .. tables_in.COUNT
LOOP
/* Creates audit table, package, triggers */
audit_util.generate_audit_support (
p_owner => schema_in,
p_table_name => tables_in (indx),
p_action => action_in);
/* Disable current BUI and BUR triggers. Can delete later. */
EXECUTE IMMEDIATE
'ALTER TRIGGER ' || tables_in (indx) || '_BIR DISABLE';
EXECUTE IMMEDIATE
'ALTER TRIGGER ' || tables_in (indx) || '_BUR DISABLE';
END LOOP;
END;
/
</code></pre></div><div>After I ran this block, I had 7 new tables, 7 new packages and 7 new triggers in my schema. I didn't write any of that code and will likely never look at it or change it. </div><div><br /></div><div>Now, whenever a row was inserted, changed or deleted in those tables, a row is inserted into the "generic" AUDIT_HEADER table and another row is inserted into the appropriate AUD$<i>dg_table_name </i>auditing table. And it works perfectly!</div><div><br /></div><div>But the question then confronting me was: how do I view and work with this data? Clearly, the answer is to build some pages in the admin interface of the Dev Gym. </div><div><br /></div><div>The first and simplest step was to build an interactive report on the AUDIT_HEADER table. Here's my first pass, 100% default behavior:</div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX54kWMD3JKh-i-HBRalPtdc4coQG-jaLZ3Fk-ptsxhM97ReNyxdFoJGGP6mxe7JlOEp9v3GD6rj9bSkZPx3iQ4fc7ptp9SZ5V_KviAV2lFP_JBWdLebSHrLQgGPLy-C30sDvZ8vv8MJQ/s1333/a.png" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="813" data-original-width="1333" height="488" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX54kWMD3JKh-i-HBRalPtdc4coQG-jaLZ3Fk-ptsxhM97ReNyxdFoJGGP6mxe7JlOEp9v3GD6rj9bSkZPx3iQ4fc7ptp9SZ5V_KviAV2lFP_JBWdLebSHrLQgGPLy-C30sDvZ8vv8MJQ/w800-h488/a.png" width="800" /></a></td></tr></tbody></table><div><br /></div><div>Nice! But, wait....what does this tell me and what does it <i>not</i> tell me? I can now see when changes were made to the audited tables....but this "generic" table doesn't contain any of the really <i>interesting</i> data, such as <i>which row</i> in the audited table, and the values in that row before the change.</div><div><br /></div><div>I'd really like to:</div><div><ul style="text-align: left;"><li>see the primary key value of the row that was affected. This will make it easier, for example, to filter the above report to show all changes to, say, user 4750.</li><li>view all the data for that row in the specific audit table</li><li>edit the affected row using an existing edit page in the Dev Gym</li></ul><div>I'm going to show you (the interesting parts of) how I went about this, because it wasn't immediately obvious to me how to make it all work. </div><div><br /></div><div>There is a not-so-interesting part I will mention first: I had to create a display-only form for each of the AUD$ tables (such as AUD$DG_CLASSES). That's not very interesting because APEX makes it so, so VERY easy to do this: create a page of type form, based it on the table of interest, select the columns you want to see. After the page is created, change items to display only, remove ability to insert and change. It's tedious, but not very - compared to the effort you'd have to make if you building your UI in - gasp - JavaScript. So consider all that done. :-)</div><div><br /></div><div>Now, here's the enhanced report:</div></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhukiJfIUWGyHKcjEP3G8n-YDOa97v537wczMfs_rYeyszCepigXrLIPIlo7nw-hWlfASvHxx-qxvWFsrFaSCCYa887t8nFD-8qyEYYvLiEcWy0IM6ouZBmuqyd8-y0TarT8gTBHWlm12k/s1163/a.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="760" data-original-width="1163" height="523" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhukiJfIUWGyHKcjEP3G8n-YDOa97v537wczMfs_rYeyszCepigXrLIPIlo7nw-hWlfASvHxx-qxvWFsrFaSCCYa887t8nFD-8qyEYYvLiEcWy0IM6ouZBmuqyd8-y0TarT8gTBHWlm12k/w800-h523/a.png" width="800" /></a></div><br /><div>The link on the aud$id column (primary key in AUDIT_HEADER table) takes me to a page for the audit table for the specific table name (e.g., if DG_CLASSES then go to the form for AUD$DG_CLASSES).</div><div><br /></div><div>The link on the key value takes me to an existing page that edits the entity, such as the Class Editor page.</div><div><br /></div><div>So that's what I want....but for those of you familiar with defining links on report columns, you might have a sense of the challenge. I cannot hard-code the page number for either of those links. They depend on the table name in the header row, which means it will/might change for each row. So my link specifications needs to be well, rather dynamic. Here's one of them:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg66DSiGAFLh7vBWjeqR_9w6smxB-N6Jt9ww-wQxjT9lC6wobPzPOHtx6rLLqdEqpIHuAh9WddGl9v1LeKUr1dWpvfljC1FGRFfU5vbbphwonu2bzdBpkv__uVspVI-hwyPyo0nGo7pwmk/s612/a.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="499" data-original-width="612" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg66DSiGAFLh7vBWjeqR_9w6smxB-N6Jt9ww-wQxjT9lC6wobPzPOHtx6rLLqdEqpIHuAh9WddGl9v1LeKUr1dWpvfljC1FGRFfU5vbbphwonu2bzdBpkv__uVspVI-hwyPyo0nGo7pwmk/s0/a.png" /></a></div><div class="separator" style="clear: both; text-align: left;">The "#" indicates that these are columns from the report. Talk about dynamic!</div><p>And I'll need even more than that. To drill down to the edit page for an entity, I need to figure out (a) the name of the primary key column for a given table and the primary key value for a given audit header row. </p><p>So let's take a look at the query underlying the report, and then the code I reference in that query.</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>SELECT aud$tstamp,
aud$id,
table_name,
dg_audit_util.audit_key_from_header (aud$id) key_value,
dml,
descr,
action,
client_id,
HOST,
module,
os_user,
form_page pagenum,
'P' || form_page || '_AUD_ID' pageitem,
edit_page keypage,
'P' || edit_page || '_' || audit_util.key_colname_from_header (aud$id)
keypageitem
FROM (SELECT a.*,
dg_audit_util.form_page (table_name)
form_page,
dg_audit_util.edit_page (table_name)
edit_page
FROM audit_header a)
</code></pre>
<p>Here's the package specification:</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>PACKAGE dg_audit_util
IS
FUNCTION form_page (table_name_in IN VARCHAR2)
RETURN INTEGER;
FUNCTION edit_page (table_name_in IN VARCHAR2)
RETURN INTEGER;
FUNCTION audit_key_from_header (aud$id_in IN NUMBER)
RETURN INTEGER;
FUNCTION key_colname_from_header (aud$id_in IN NUMBER)
RETURN VARCHAR2;
END;
</code></pre>
<p>Here's the package body. Actually I don't think the details are all that interesting. In the first two functions I am simply encapsulating the lists of relevant page numbers. The key_colname_from_header "reverse engineers" my naming convention which is that (a) table names include an application prefix, are plural and in most cases that means simply added an "S" to the entity name, (b) primary key column name is table name singular form (entity name) with "_ID" added to it. </p><p>So...an example:</p><p>Entity = User</p><p>Table name = QDB_USERS</p><p>Primary key = USER_ID</p><p>But then of course there are exceptions like "DG_CLASSES" and "QDB_QUIZZES," which must be handled by special case.</p><p>Entity = Class</p><p>Table name = DG_CLASSES</p><p>Primary key = CLASS_ID</p><p>The audit_key_from_header gets the primary key value from the row in the per-entity audit table (e.g., AUD$DG_CLASSES)</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>PACKAGE BODY dg_audit_util
IS
FUNCTION form_page (table_name_in IN VARCHAR2)
RETURN INTEGER
IS
BEGIN
RETURN CASE table_name_in
WHEN 'DG_CLASSES' THEN 75
WHEN 'QDB_COMPEV_ANSWERS' THEN 84
WHEN 'QDB_USERS' THEN 90
WHEN 'DG_STUDENTS' THEN 92
WHEN 'DG_USER_WORKOUTS' THEN 101
WHEN 'QDB_COMP_EVENTS' THEN 93
ELSE 106
END;
END;
FUNCTION edit_page (table_name_in IN VARCHAR2)
RETURN INTEGER
IS
BEGIN
RETURN CASE table_name_in
WHEN 'DG_CLASSES' THEN 35
WHEN 'QDB_COMPEV_ANSWERS' THEN 3
WHEN 'QDB_USERS' THEN 109
WHEN 'DG_STUDENTS' THEN NULL
WHEN 'DG_USER_WORKOUTS' THEN NULL
WHEN 'QDB_COMP_EVENTS' THEN 54
ELSE 106
END;
END;
FUNCTION key_colname_from_header (aud$id_in IN NUMBER)
RETURN VARCHAR2
IS
l_name VARCHAR2 (1000);
l_col_name VARCHAR2 (1000);
BEGIN
SELECT table_name
INTO l_name
FROM audit_header
WHERE aud$id = aud$id_in;
l_col_name :=
CASE l_name
WHEN 'DG_CLASSES'
THEN
'CLASS_ID'
WHEN 'QDB_RESULT_CHOICES'
THEN
'RESULT_ID'
ELSE
SUBSTR (l_name,
INSTR (l_name, '_') + 1,
LENGTH (l_name) - INSTR (l_name, '_') - 1)
|| '_ID'
END;
RETURN l_col_name;
END;
FUNCTION audit_key_from_header (aud$id_in IN NUMBER)
RETURN INTEGER
IS
l_name VARCHAR2 (1000);
l_key INTEGER;
BEGIN
SELECT table_name
INTO l_name
FROM audit_header
WHERE aud$id = aud$id_in;
EXECUTE IMMEDIATE
'select '
|| key_colname_from_header (aud$id_in)
|| ' from aud$'
|| l_name
|| ' where aud$id = :id'
INTO l_key
USING aud$id_in;
RETURN l_key;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
END;
</code></pre>Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-52031431344707208982020-08-17T11:04:00.007-07:002020-09-10T08:55:41.789-07:00The differences between deterministic and result cache features<p> EVERY once in a while, a developer gets in touch with a question like this:</p><p></p><blockquote>I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'? </blockquote><p></p><p>So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to <i>be</i> deterministic.</p><p>From <a href="https://en.wikipedia.org/wiki/Deterministic_algorithm" target="_blank">Wikipedia</a>:</p><p></p><blockquote>In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states. </blockquote><p></p><p>Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get the same results - no matter when, where or how often you run it.</p><p>What, you may be wondering, would be a side effect in a PL/SQL function? Any of these (this list is not exhaustive):</p><p></p><ul style="text-align: left;"><li>Any and every SQL statement</li><li>Referencing an out-of-scope variable (aka, "global")</li><li>Invoking a non-deterministic subprogram</li></ul><p></p><p>Next we'll take a look at how you use these two features in your code. Then I will delve into the differences. We will, by the way, focus on functions in this post. It is possible to declare a procedure as deterministic, but this has no known impact that I am aware of and is not commonly used. And you cannot declare a procedure as "result cached."</p><p><b>1. Defining a function as deterministic</b></p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN INTEGER
, end_in IN INTEGER
)
RETURN VARCHAR2 <b>DETERMINISTIC</b>
IS
BEGIN
RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
</code></pre>
<p>This simple encapsulation of SUBSTR allows me to specify the start and end positions, rather than the start position and number of characters. I hope you will agree that this is <i>deterministic</i>. </p><p>So that's all you have to do: add the DETERMINISTIC keyword to the header of the function or procedure.</p>
<p>What it does for you:</p><p></p><ul style="text-align: left;"><li>Makes it possible to use the function in a function-based index</li><li><i>Might</i> improve performance by caching and reusing function return values</li></ul><div>Here's a quick little demonstration of the caching impact of a deterministic function:</div>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
DBMS_OUTPUT.put_line ('pass_number executed');
RETURN 0;
END;
/
DECLARE
n NUMBER := 0;
BEGIN
FOR rec IN (SELECT pass_number (1)
FROM all_objects
WHERE ROWNUM < 6)
LOOP
n := n + 1;
END LOOP;
DBMS_OUTPUT.put_line (n + 1);
END;
/
pass_number executed
6
</code></pre>
<p>Notice that even though the function was invoked 5 times, the body of the function was executed just once. Oracle Database created a tiny, little, short-lived cache, just for this function and just for the server call (PL/SQL block or SQL statement) in which it was invoked.</p><p>Rob van Wijk offers lots more details on the behavior and performance of deterministic functions <a href="http://rwijk.blogspot.com/2008/04/deterministic-clause.html">here</a>. </p><p><b>2. Defining a function as "result cached"</b></p><p>Let's now change make that betwnstr function a result-cached function:</p>
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN INTEGER
, end_in IN INTEGER
)
RETURN VARCHAR2
<b>RESULT_CACHE</b>
IS
BEGIN
RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
</code></pre>
<p>Gee, that was (deceptively) easy. I just add the RESULT_CACHE keyword. Notice that I removed the DETERMINISTIC keyword, but I did that only for clarity's sake. A function can have both of these keywords in its header. It can, in other words, be deterministic and result-cached.</p><p>What it does for you:</p><p></p><ul style="text-align: left;"><li>Tells Oracle Database that you want to use some memory in the SGA or Shared Global Area to cache argument values and returned values.</li><li>From that point on, whenever the function is invoked by any session in the database instance, the body of the function will only be executed if it has not already been called with those same input values.</li><li>If there is a "hit" in the cache for that combination of arguments, the return value(s) will simply be grabbed from the cache and returned to the calling block.</li><li>If the function relies on (references) any database tables, when any user commits changes to that table, the cache for the function will be automatically wiped out.</li></ul><div>Hopefully you can tell that there is a <i>lot</i> more to the result cache feature than to the deterministic one. And the above list just scratches the surface. </div><div><br /></div><div>RESULT_CACHE is waaaaaay more powerful and can also have much greater impact (good and bad) on the performance of your application than deterministic. If you want to make use of this feature, you definitely should read through the extensive documentation <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-subprograms.html#GUID-250BDBBB-0CD6-4D56-9DDF-6FCEDC110D00">here</a>. You also find several result cache scripts on Oracle LiveSQL <a href="https://livesql.oracle.com/apex/f?p=590:49:::NO:RP,49:P49_SEARCH:result%20cache">here</a>.</div><p></p><div>And now for a quick little demonstration of the caching impact of a result-cached function:</div><div><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 669px;"><code>CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
RETURN NUMBER
RESULT_CACHE
IS
BEGIN
DBMS_OUTPUT.put_line ('pass_number executed for ' || i);
RETURN 0;
END;
/
DECLARE
n NUMBER := 0;
BEGIN
FOR rec IN (SELECT pass_number (100)
FROM all_objects
WHERE ROWNUM < 6)
LOOP
n := n + 1;
END LOOP;
DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1));
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
END;
/
pass_number executed for 100
All done 6
Returned 0
pass_number executed for 200
Returned 0
pass_number executed for 300
Returned 0
Returned 0
Returned 0
Returned 0
</code></pre><p>I call pass_number a total of three times with an argument value of 100. Notice that the function body is executed just once, out of those three times - even though the calls came from two different blocks.</p><p>And I call pass_number twice each with argument values of 200 and 300, but the function body is executed just once for each of those. </p><p>If I disconnect from my session and reconnect, then run those same two blocks (without recompiling the function), I will see only:</p></div><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 669px;"><code>All done 6
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
</code></pre><p>The cache for a result-cached function persists across blocks, across sessions, across users. It is a complex feature that can have a ripple effect, both good and bad, across your application.</p><p>Which is to say: if you are not careful about how you use RESULT_CACHE, you could cause unintended negative consequences, some of which are explored in this article: <a href="https://blogs.oracle.com/plsql-and-ebr/how-to-write-a-safe-result-cached-plsql-function">How to write a safe result-cached function</a>.
</p><h2 style="text-align: left;">How these two features are similar</h2><div>Both DETERMINISTIC and RESULT_CACHE cause caching of data that can improve performance by sidestepping the need to execute the body<i> </i>of a function.</div>
<h2 style="text-align: left;">How they are different</h2><div>The caching that the DETERMINISTIC keyword leads to has a narrow scope (only your session) and short lifespan (the caching occurs for the duration of the SQL statement that executes the function). So the overall performance impact will likely not be too great.</div><div><br /></div><div>Data cached by the result cache feature are available to all users of a database instance. And that data remains cached until the cache is invalidated or flushed. It has much greater potential for improving performance of your application - but it also presents more of a danger of having a negative impact as well.</div>
<h2 style="text-align: left;">When to use deterministic</h2><div>You should add the DETERMINISTIC keyword to your function header whenever it actually <i>is</i> deterministic. By doing so, you give the PL/SQL and SQL engines more information that they can use to optimize performance and make the features available (such as a function-based index).</div><div><br /></div><div>Even if it doesn't help now, it might in the future, and it can do no harm to put it there....but....</div><div><br /></div><div>But you should <i>never</i> use that keyword with a function that is <i>not</i> truly deterministic. Sometimes this kind of "lying" will be caught by Oracle and rejected, sometimes it could cause problems in your application.</div>
<h2 style="text-align: left;">When to use result cache</h2><div>This is trickier to answer. Adding the RESULT_CACHE keyword to your function has a ripple effect through the entire database instance and overall application performance.</div><div><br /></div><div>You need to work closely with your DBA to ensure that your dev, test and production instances are configured properly (the SGA memory area for all the result caches needs to be properly sized; you need to do everything you can to avoid latch contention).</div><div><br /></div><div>And you need to very carefully select which functions should have the RESULT_CACHE keyword added to them. Some basic criteria include:</div><div><ul style="text-align: left;"><li>It is invoked with the same argument values repeatedly? </li><li>If the function relies on a table, is the data in the table or view static (example: materialized view)? That's a good candidate. </li><li>If the function relies on a table, is the data queried much more frequently than it is updated? Also a good possibility then. Remember: the cache will be invalidated when changes to a dependent table are committed.</li><li>Does the function have any session-specific dependencies, such as reliance on NLS parameter settings? If so, then the cached values might not match the session-dependent values. A good example is using TO_CHAR in your function without a format mask. VPD and reliance on system context values is another. Generally the solution for this is to move all such dependencies to the parameter list. </li></ul></div><div>Just remember: any deterministic function is a good <i>candidate</i> for the RESULT_CACHE keyword, but not every result-cached function is deterministic.</div>Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com14tag:blogger.com,1999:blog-7849367040589270673.post-35110591268310957062020-08-12T11:22:00.001-07:002020-08-12T11:22:40.866-07:00Generate code to move rows to string indexed collections<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1irdyFtWFrvghIAvJRxQsJKZGcHdkLUwFj74B4ivMv488ShkoxEgrdBkIHf7EgxsnIGwZ-k1SRes7lVmzRKO-cUSGrydALa5OT2v7PubwMHDIEvgz9hIDytqouCt9_EuuTMVcBOi83Qw/s1600/a.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="609" data-original-width="878" height="276" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1irdyFtWFrvghIAvJRxQsJKZGcHdkLUwFj74B4ivMv488ShkoxEgrdBkIHf7EgxsnIGwZ-k1SRes7lVmzRKO-cUSGrydALa5OT2v7PubwMHDIEvgz9hIDytqouCt9_EuuTMVcBOi83Qw/s400/a.png" width="400" /></a></div>
Write code for a living?<br />
<br />
Feeling kind of lazy?<br />
<br />
Then maybe you should find a way to <i>generate</i> some code. That's the focus of this blog post.<br />
<br />
The bulk processing feature of PL/SQL was introduced a long, long time ago. It lets us, among other things, do this:<br />
<br />
Lovely, concise syntax. One downside, however, is that the target collection in the BULK COLLECT INTO clause must be indexed by integer (which means all nested tables and varrays, but only INDEX BY - associative array - collections that are index by PLS_INTEGER or variations therein).<br />
<br />
This means that if you do want to use a string-indexed collection, you need to first "dump" it into an integer-indexed array, and then move it over to a string-indexed array.<br />
<br />
I've done this, and my code usually looks like this:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
CURSOR c IS select last_name, first_name, employee_id
from employees where department_id = 30;
TYPE t IS TABLE OF c%ROWTYPE;
c_limit CONSTANT PLS_INTEGER := 100;
l_rows_by_integer t;
TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);
l_rows_by_varchar2 vct;
l_index VARCHAR2(32767);
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_rows_by_integer LIMIT c_limit;
EXIT WHEN l_rows_by_integer.COUNT = 0;
FOR indx IN 1 .. l_rows_by_integer.COUNT
LOOP
l_rows_by_varchar2 (l_rows_by_integer (indx).last_name)
:= l_rows_by_integer (indx);
END LOOP;
END LOOP;
CLOSE c;
/* Iterate through the string-indexed collection */
l_index := l_rows_by_varchar2.FIRST;
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).last_name);
l_index := l_rows_by_varchar2.NEXT (l_index);
END LOOP;
END;
</code></pre>
Well, dang, that's a lot of code to have to write!<br />
<br />
I have two solutions to that problem:<br />
<br />
1. You can get this done with less code.<br />
<br />
2. You can use my procedure to generate the code.<br />
<br />
First, getting it done with less code: why populate an integer-indexed collection? Why not just use a cursor FOR loop instead? Here's the alternative, a good bit shorter.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
CURSOR c IS select last_name, first_name, employee_id
from employees where department_id = 30;
TYPE vct IS TABLE OF c%ROWTYPE INDEX BY VARCHAR2(32767);
l_rows_by_varchar2 vct;
l_index VARCHAR2(32767);
BEGIN
FOR rec IN (select last_name, first_name, employee_id
from employees where department_id = 30)
LOOP
l_rows_by_varchar2 (rec.last_name) := rec;
END LOOP;
CLOSE c;
/* Iterate through the string-indexed collection */
l_index := l_rows_by_varchar2.FIRST;
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE (l_rows_by_varchar2(l_index).last_name);
l_index := l_rows_by_varchar2.NEXT (l_index);
END LOOP;
END;
</code></pre>
Still, why write that code at all? I was inspired by Connor McDonald's recent blog post and fantastic <a href="https://connor-mcdonald.com/2020/08/04/level-up-your-audit-trigger-game/">new auditing utility</a> that generates a lot of code.<br />
<br />
So I wrote a couple of procedures you can use to generate either of the patterns above. You can get all the code on <a href="https://livesql.oracle.com/apex/livesql/file/content_KICOURVDJ7G3525KSU8RLGCSG.html">LiveSQL</a>. As you will quickly see, I wrote the above code first, then turned it into a template string with tags, like "#COLUMN#". I replace the tags with the values you provide, then spit it out with DBMS_OUTPUT. Simple enough.<br />
<br />
Here's an example of calling the procedure to generate the text, in this case showing how to use an expression to construct the string index values.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>BEGIN
gen_select_into_index_by2 (
'select last_name, first_name, last_name||''-''||first_name combined_name, employee_id
from employees where department_id = 30', 'employees', 'combined_name');
END;</code></pre>
Hope you find it useful!Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com2tag:blogger.com,1999:blog-7849367040589270673.post-60398822045210198282020-05-26T05:58:00.000-07:002020-05-26T06:46:51.972-07:00Office Hours June 2020: Exploring the PL/SQL ProfilersThe PL/SQL engine offers two profiler utilities to help identify performance bottlenecks in your application:<br />
<br />
1. DBMS_PROFILER<br />
<br />
That's the name of the package that provides an API to the profiler that computes the time that your PL/SQL program spends at <i>each line, in each subprogram</i>. Saves runtime statistics in database tables, which you can then query.<br />
<br />
2. DBMS_HPROF<br />
<br />
The hierarchical profiler; this utility reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. It accounts for SQL and PL/SQL execution times separately. Requiring no special source or compile-time preparation, it generates reports in HTML. You can also store profiler data and results in relational format in database tables for custom report generation (such as third-party tools offer).<br />
<br />
You can find lots more information about these two profilers in the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-D99A60B1-E3ED-4252-A85C-8FA732EE9957">documentation</a>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRxfD_NgxLEHJXmbvL-B2TX7z__6_wp1KT3x4-VnNygQS3ui6ZP1wQT-9Cll1GMbdwaa_lBH_fQO7M26q2Rnpgy_GJLjxSJV4491poWMSlh30-Yq7rgKvenhkjoqfmk5ktFzALNM0PLPI/s1600/shashank.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="349" data-original-width="330" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRxfD_NgxLEHJXmbvL-B2TX7z__6_wp1KT3x4-VnNygQS3ui6ZP1wQT-9Cll1GMbdwaa_lBH_fQO7M26q2Rnpgy_GJLjxSJV4491poWMSlh30-Yq7rgKvenhkjoqfmk5ktFzALNM0PLPI/s320/shashank.png" width="302" /></a></div>
In our <a href="https://asktom.oracle.com/pls/apex/asktom.search?oh=401">June 2nd 2020 Office Hours</a> session, I am very pleased to have Shashank Barki, an experienced database developer who has made extensive use of DBMS_PROFILER, present his experiences and lessons learned on these key utilities.<br />
<br />
Shashank is a Senior Principal Data Engineer at <a href="https://www.manh.com/">Manhattan Associates</a>. He has 13 years experience as a developer, cloud architect and data analyst. In each of his jobs, he has promoted the use of PL/SQL to improve the performance, security and maintainability of their applications.<br />
<br />
We hope you can join us on June 2nd, 11 AM Eastern, to learn more about PL/SQL profilers.<br />
<div>
<br /></div>
Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-88187549212684772732020-04-16T12:37:00.002-07:002020-04-17T04:32:17.866-07:00Virtual Private Database: Beyond the Basics<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHp3cCqMajV_Ax5kqwlKIn07eTSLB-W9P2ofRnyoNIi_tgFqDdiefyqwHvboOGYhznA31cfkhn1-W-X6CB0suZ0V050gL0EADA8lX-91kFved8i5Yak-dWR6H2-_UqogGfj8K8O_XdupI/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="602" data-original-width="1077" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHp3cCqMajV_Ax5kqwlKIn07eTSLB-W9P2ofRnyoNIi_tgFqDdiefyqwHvboOGYhznA31cfkhn1-W-X6CB0suZ0V050gL0EADA8lX-91kFved8i5Yak-dWR6H2-_UqogGfj8K8O_XdupI/s1600/a.png" /></a>
Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic.<br />
<br />
One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data.<br />
<br />
Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward:<br />
<ul>
<li>A patient can only see their own information.</li>
<li>A doctor can see only the information about their own patients.</li>
<li>A clinic administrator can see information only about the patients in their clinic.</li>
</ul>
In all three cases, the user would sign on to the application and execute this identical query and only <i>their</i> rows would appear.<br />
<br />
<div style="text-align: center;">
<span style="font-family: "courier new" , "courier" , monospace;"><b>SELECT * FROM patients</b></span></div>
<br />
Of course, there are lots of different and very interesting aspects to setting up your policies. Back on March 3, 2020, Praveen Kumar of Wipro shared his thoughts about VPD with 100 developers on <a href="https://asktom.oracle.com/pls/apex/asktom.search?oh=401">PL/SQL Office Hours</a>.<br />
<br />
He ran out of time before he could explore some of the more interesting challenges and beyond-the-basics features, so we are bringing him back on May 5th at 11 AM Eastern to complete his thoughts! We hope you'll join us. We encourage you to watch last session's <a href="https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:RP,551:P551_CLASS_ID:6967">video</a> beforehand to get the most out of the May session.<br />
<h2>
Our Presenter: Praveen Kumar of Wipro</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE5xQ5qLN8AAffOk9AmRKcZN7_vHvW6OtHx-vw20rkqcTURUaqAsya5ADAWWotBZaoET6K34uZ70BmU7I9VtB4aQUZjUXo1WAA42KEXKp9nTfAioQHqCPBfhNSoecsni7LvLMw55dCJjk/s1600/praveen.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="349" data-original-width="376" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE5xQ5qLN8AAffOk9AmRKcZN7_vHvW6OtHx-vw20rkqcTURUaqAsya5ADAWWotBZaoET6K34uZ70BmU7I9VtB4aQUZjUXo1WAA42KEXKp9nTfAioQHqCPBfhNSoecsni7LvLMw55dCJjk/s320/praveen.png" width="320" /></a></div>
<a href="http://twitter.com/k_p1729">Parveen Kumar</a> is a Java and Oracle Developer with Wipro, based in the UK. He has over eight years experience focused mainly on developing and designing applications using Oracle Database as a primary database. He aims to keep the business logic inside the database and expose data through PL/SQL APIs.<br />
<br />
Parveen has in various projects taken advantage of native support for XML, JSON and Object datatypes, SOAP/REST APIs for Web Programming and security features like VPD/RLS, Oracle Wallet for Web APIs and other features which provide true fine grained access to different types of users.<br />
<br />
In the May Office Hours session, Praveen will cover these topics and maybe even some more:<br />
<ul>
<li>Policy Groups and how they work and provide better control</li>
<li>Application roles in conjunction with VPD more detail</li>
<li>Performance issues and analyzing and fixing them</li>
<li>Drawbacks and real-time challenges of working with VPDs </li>
<li>Maintenance of VPD policies</li>
</ul>
Follow <a href="https://asktom.oracle.com/pls/apex/asktom.search?oh=401">this link</a> to subscribe to my monthly PL/SQL Office Hours program, so that you will receive email reminders for this and future sessions.<br />
<br />
You can also view recordings of the dozens of past sessions, including:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhiEArnzBUmxheMe5x3dIbbaUTZUYtU7MWvV4MRYvh5lklG_kG-6Wp41x8CO75AKckuVqs_g4w1jXZY2pt_2h8Jtx3IHUWrpZw4K9BR2n_3UHfhFPCjGTbG3w1XpFze8C9KrVlI4gSGw/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="768" data-original-width="833" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhiEArnzBUmxheMe5x3dIbbaUTZUYtU7MWvV4MRYvh5lklG_kG-6Wp41x8CO75AKckuVqs_g4w1jXZY2pt_2h8Jtx3IHUWrpZw4K9BR2n_3UHfhFPCjGTbG3w1XpFze8C9KrVlI4gSGw/s1600/a.png" /></a></div>
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-91830140723061061122020-03-31T13:17:00.001-07:002020-04-02T06:20:37.877-07:00Why DBMS_OUTPUT.PUT_LINE should not be in your application code<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyhZIlYuzl5sgENVofxz8tAqi0HqwGijK3nTN9tBhdkx9C1s7_l1w_KQW5p2cxXsc1Q5rFkDoNFHxHYM5wve6Z11tHRaDv6M6nO2IKYnELgWNheQ44Lq77-DZlWpNDb4zPTJ7_my3g-3Y/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /><img border="0" data-original-height="686" data-original-width="1186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyhZIlYuzl5sgENVofxz8tAqi0HqwGijK3nTN9tBhdkx9C1s7_l1w_KQW5p2cxXsc1Q5rFkDoNFHxHYM5wve6Z11tHRaDv6M6nO2IKYnELgWNheQ44Lq77-DZlWpNDb4zPTJ7_my3g-3Y/s1600/a.png" /></a></div>
<br />
A database developer recently came across my <a href="https://www.oracle.com/webfolder/technetwork/tutorials/plsql/BulletProofPLSQL.pdf">Bulletproof PL/SQL</a> presentation, which includes this slide.<br />
<br />
That first item in the list caught his attention:<br />
<blockquote class="tr_bq">
Never put calls to DBMS_OUTPUT.PUT_LINE in your application code.</blockquote>
So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-)<br />
<br />
But in the meantime, allow me to explain.<br />
<br />
First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen.<br />
<br />
[Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.]<br />
<br />
Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen?<br />
<br />
Not without a lot of difficulty, that's for sure. I will talk below about what you want to use instead of DBMS_OUTPUT.PUT_LINE (instead of writing output to a screen, in other words), but really, I should modify my advice as follows:<br />
<blockquote class="tr_bq">
There should be <i><b>at most one</b></i> call to DBMS_OUTPUT.PUT_LINE in your application code.</blockquote>
Notice my reference to "application code." By this, I mean come you are writing code to be deployed into production, as opposed to demonstration or "let's try this" scripts.<br />
<br />
So, now maybe it should appear just <i>once</i>? Steven, explain yourself already.<br />
<br />
OK, first my reasons for avoiding widespread use of DBMS_OUTPUT.PUT_LINE, then suggestions on what to do instead.<br />
<br />
The downsides of DBMS_OUTPUT.PUT_LINE include....<br />
<ul>
<li>You won't see anything unless you have enabled server output (example in SQL*Plus: SET SERVEROUTPUT ON). In some editors, it is enabled by default. In others, you must take action.</li>
<li>Will only accept a string or a datatype that can be implicitly converted to a string.</li>
<li>The buffer is volatile: if for any reason your session terminates, that output is gone forever.</li>
<li>Production environments and screen output do not mix. In other words, you will not be able to see this output in production.</li>
<li>When a PL/SQL block terminates with an unhandled exception, text in the DBMS_OUTPUT buffer might not be flushed out to the screen (depends on the host environment).</li>
<li>It is a very crude debugging/tracing mechanism. Writing to the buffer is either enabled or disabled - globally in your application. No nuances beyond that.</li>
<li>In some IDEs (editors), you have to explicitly enable output (outside your application code) before you will see anything.</li>
</ul>
I bet readers of this blog post will chime in with other drawbacks to this built-in.<br />
<br />
As the slide above implies, my comment about this built-in came in the context of tracing - getting real-time feedback on what your code is doing.<br />
<br />
Here's an example of a program that uses DBMS_OUTPUT.PUT_LINE for tracing.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.put_line (min_sal_in);
FOR rec IN (SELECT *
FROM employees
WHERE salary >= min_sal_in)
LOOP
DBMS_OUTPUT.put_line ('employee_id = ' || rec.employee_id);
do_more_stuff (rec);
DBMS_OUTPUT.put_line ('new salary = ' || rec.salary);
END LOOP;
END;
</code></pre>
Suppose I took this approach. I don't <i>always</i> want to see the output; I'm really only interested when debugging and testing the code. No problem, you might say. Just disable server output. Sure - but then you also don't see the output from this program that is <i>not</i> trace-related.<br />
<br />
And when the code is deployed to production, the limitations of DBMS_OUTPUT are undeniable: you have no ability to trace whatever your users are doing that might be causing a problem.<br />
<br />
Even worse, a very common pattern followed by developers is that they (OK, <i>we</i> - I still do it now and again when I am feeling <i>very</i> lazy) put in calls to DBMS_OUTPUT.PUT_LINE all over their code while they are getting it to work (ie, testing, sort of).<br />
<br />
Then when they feel they've got it all fixed, they <i>remove the calls to DBMS_OUTPUT.PUT_LINE. </i>After all, all those output statements are making a mess of the code - and they're not even really a part of the application.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
FOR rec IN (SELECT *
FROM employees
WHERE salary >= min_sal_in)
LOOP
do_more_stuff (rec);
END LOOP;
END;
</code></pre>
Ahhhhh, much better. :-)<br />
<br />
On the one hand, you might say: "Well, that's good then, right, Steven? You said not to put it in your code."<br />
<br />
On the other hand, I might say: "Maybe - but only if that's because you really have removed all the bugs from your code and none will ever be introduced."<br />
<br />
The bottom line is that any high quality piece of code should and <i>will</i> include instrumentation/tracing. Moving code into production without this ability leaves you blind when users have problems. So you definitely want to keep that tracing in, but you definitely do not want to use DBMS_OUTPUT.PUT_LINE - or at least rely <i>only</i> on the built-in to get the job done.<br />
<br />
What should you do instead? Build your own wrapper around the built-in or use a an existing trace utility like the open source <a href="http://www.oraopensource.com/logger/">Logger</a>. And if you can't use Logger for whatever reason, use it as model for your own.<br />
<br />
So what do I mean by "build your own wrapper <i>around</i> the built-in"? I mean that the only way to get output to your screen is through this package and sometimes you certainly want that. But most of the time, and especially in production, you'd much rather send that trace information to a table, where the data persists and anyone with the privileges can query from it. And you need to be able to turn tracing on and off as needed, even while the application is running. It would also be nice if you could easily trace a Boolean value.<br />
<br />
To give you a sense of what this wrapper might look like, I offer to your the Super Simple Trace Utility. First, I have two tables, one to store the current configuration for the utility, the other to hold the trace information itself.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE trace_config
(
send_to VARCHAR2 (1),
trace_on VARCHAR2 (1)
)
/
INSERT INTO trace_config (send_to, trace_on)
VALUES ('T', 'N')
/
COMMIT
/
CREATE TABLE trace_table
(
message VARCHAR2 (4000),
callstack CLOB,
created_by VARCHAR2 (30),
created_at DATE
)
/</code></pre>
My package specification is, hopefully, self-explanatory:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PACKAGE trace_mgr
IS
PROCEDURE to_screen;
PROCEDURE to_table;
PROCEDURE turn_on;
PROCEDURE turn_off;
FUNCTION sending_to_screen RETURN BOOLEAN;
FUNCTION trace_is_on RETURN BOOLEAN;
PROCEDURE put (msg_in IN VARCHAR2);
PROCEDURE put (boolean_in IN BOOLEAN);
END;
/
</code></pre>
Let's take a look, finally, at the package body.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PACKAGE BODY trace_mgr
IS
PROCEDURE to_screen
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET send_to = 'S';
COMMIT;
END;
PROCEDURE to_table
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET send_to = 'T';
COMMIT;
END;
PROCEDURE turn_on
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config
SET trace_on = 'Y';
COMMIT;
END;
PROCEDURE turn_off
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET trace_on = 'N';
COMMIT;
END;
FUNCTION trace_is_on
RETURN BOOLEAN
IS
l_trace trace_config.trace_on%TYPE;
BEGIN
SELECT trace_on INTO l_trace FROM trace_config;
RETURN l_trace = 'Y';
END;
FUNCTION sending_to_screen
RETURN BOOLEAN
IS
l_send_to trace_config.send_to%TYPE;
BEGIN
SELECT send_to INTO l_send_to FROM trace_config;
RETURN l_send_to = 'S';
END;
PROCEDURE put (msg_in IN VARCHAR2)
IS
FUNCTION fullmsg
RETURN VARCHAR2
IS
BEGIN
RETURN ' At: '
|| TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS')
|| ' Msg: '
|| msg_in;
END;
BEGIN
IF trace_is_on
THEN
IF sending_to_screen
THEN
DBMS_OUTPUT.put_line (fullmsg);
ELSE
INSERT INTO trace_table (MESSAGE,
callstack,
created_by,
created_at)
VALUES (msg_in,
DBMS_UTILITY.format_call_stack,
USER,
SYSDATE);
END IF;
END IF;
END;
PROCEDURE put (boolean_in IN BOOLEAN)
IS
BEGIN
DBMS_OUTPUT.put_line (
CASE boolean_in
WHEN TRUE THEN 'TRUE'
WHEN FALSE THEN 'FALSE'
ELSE 'NULL'
END);
END;
END;
/
</code></pre>
Now my do_stuff procedure can be converted easily over to the following, and I control the behavior of the trace package from outside of this procedure.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>PROCEDURE do_stuff (min_sal_in IN NUMBER)
IS
BEGIN
trace_mgr.put (min_sal_in);
FOR rec IN (SELECT *
FROM employees
WHERE salary>= min_sal_in)
LOOP
trace_mgr.put ('employee_id = ' || rec.employee_id);
do_more_stuff (rec);
trace_mgr.put ('new salary = ' || rec.salary);
END LOOP;
END;
</code></pre>
You can download my Super Simple Trace Utility from <a href="https://livesql.oracle.com/apex/livesql/file/content_JVW8YRK3HR7CLI2LLTYBJNKCH.html">LiveSQL</a>. But you'd be much better off with Logger. :-)Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com4tag:blogger.com,1999:blog-7849367040589270673.post-60698132174502152602020-03-10T04:41:00.003-07:002020-03-10T04:41:37.014-07:00An Application Alerting Utility<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhts4wLsv0hHdQLc4M4sPB_y3VHqQccy9_g1jTcYXyDNPDn0oegZxp7o3kRUA2Wr33KtcHRbjiNXeQGZ-zJN81V-_PlQT1GRUe-hQNwnA-c5daORRhyYMvtaIxYldzWdioMLEpT1HJ02Uo/s1600/a.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="665" data-original-width="508" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhts4wLsv0hHdQLc4M4sPB_y3VHqQccy9_g1jTcYXyDNPDn0oegZxp7o3kRUA2Wr33KtcHRbjiNXeQGZ-zJN81V-_PlQT1GRUe-hQNwnA-c5daORRhyYMvtaIxYldzWdioMLEpT1HJ02Uo/s320/a.jpg" width="244" /></a></div>
A few weeks ago, Mike Hichwa asked me to come up with a package that would implement a simple alerting utility: specify a triggering event (based on a query or a PL/SQL expression) and then take the specified actions when it is triggered.<br />
<br />
Seeing as he is my boss, I said "OK" and got to work (my favorite kind of work: writing some PL/SQL code). We did our usual bit to expand scope, then did our usual bit to agree that this is enough for a first pass. And then Mike said: "Go ahead and write a blog post about it, share it with the community."<br />
<br />
So here goes.<br />
<br />
The basic idea is we start up a job using DBMS_SCHEDULER that runs every N minutes. When it runs, it checks to see if any alerts need to be triggered. If so, it then takes one or more actions associated with that alert.<br />
<br />
Let's start with our tables.<br />
<h3>
<b>Utility configuration
</b></h3>
How often should the job wake up? Should it disable checking for alerts? What is the name of the "callout" for sending emails? And if I am using <a href="http://apex.oracle.com/">Oracle Application Express</a>, what is the workspace name? (needed to use APEX_MAIL to send emails)<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE alert_config
(
job_wakeup_minutes INTEGER,
keep_checking VARCHAR2 (1),
send_email_proc_name VARCHAR2 (300),
apex_workspace_name VARCHAR2(100)
)
</code></pre>
<h3>
<b>Alerts
</b></h3>
All the registered alerts. The trigger code is executed to determine if the alert actions should be run. The trigger type describes the code and how to execute it. You can set the frequency with which the alert is checked, how often to check again once it was triggered, and more.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE alerts (
id NUMBER NOT NULL
CONSTRAINT alerts_id_pk PRIMARY KEY,
alert_name VARCHAR2 (100)
CONSTRAINT alerts_alert_name_unq UNIQUE,
description VARCHAR2 (4000),
trigger_code VARCHAR2 (4000),
trigger_type VARCHAR2 (100),
check_frequency VARCHAR2 (1000),
recurrence_frequency VARCHAR2 (1000),
ignore_failures VARCHAR2 (1),
is_active VARCHAR2 (1),
last_run_id NUMBER,
created DATE NOT NULL,
created_by VARCHAR2 (255) NOT NULL,
updated DATE NOT NULL,
updated_by VARCHAR2 (255) NOT NULL,
CONSTRAINT trigger_type_choices CHECK
(trigger_type IN ('ONEROW',
'NOROWS',
'MULTROWS',
'RETURNTRUE',
'RETURNFALSE')),
CONSTRAINT ignore_failures_yn
CHECK (ignore_failures IN ('Y', 'N')),
CONSTRAINT active_yn CHECK (is_active IN ('Y', 'N'))
)
</code></pre>
<h3>
<b>Alert Actions
</b></h3>
The action(s) associated with an alert. In my just-a-little-more-than proof-of-concept version, I implement support for running PL/SQL procedures and sending emails. The rest, I leave up to those of you who have need and/or interest. I will be happy to incorporate them back into the utility if you send me the code.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE alert_actions (
id NUMBER NOT NULL
CONSTRAINT alert_actions_id_pk PRIMARY KEY,
alert_id NUMBER
CONSTRAINT alert_actions_alert_id_fk
REFERENCES alerts ON DELETE CASCADE,
action_sequence INTEGER,
action_type VARCHAR2 (100) NOT NULL,
action_code VARCHAR2 (4000),
send_email_to VARCHAR2 (4000),
email_subject VARCHAR2 (4000),
email_body CLOB,
email_query VARCHAR2 (4000),
created DATE NOT NULL,
created_by VARCHAR2 (255) NOT NULL,
updated DATE NOT NULL,
updated_by VARCHAR2 (255) NOT NULL,
CONSTRAINT action_type_choices CHECK
(action_type IN ('EMAIL',
'SMS',
'PROC',
'REST'))
)
</code></pre>
<h3>
<b>Alert Runs
</b></h3>
Let's keep track of each run of an alert.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE alert_runs (
id NUMBER NOT NULL
CONSTRAINT alert_runs_id_pk PRIMARY KEY,
alert_id NUMBER
CONSTRAINT alert_runs_alert_id_fk
REFERENCES alerts ON DELETE CASCADE,
started_at DATE,
completed_at DATE,
completed_successfully VARCHAR2 (1),
results VARCHAR2 (4000),
errors VARCHAR2 (4000),
created DATE NOT NULL,
created_by VARCHAR2 (255) NOT NULL,
updated DATE NOT NULL,
updated_by VARCHAR2 (255) NOT NULL,
CONSTRAINT success_yn
CHECK (completed_successfully IN ('Y', 'N'))
)
</code></pre>
<h3>
<b>The Alert Log
</b></h3>
Very simple table to store tracing and <a href="https://github.com/OraOpenSource/Logger">error</a> logging. You might want to substitute this and the associated code in my package with Logger.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE alert_log (
id NUMBER NOT NULL
CONSTRAINT alert_log_id_pk PRIMARY KEY,
info VARCHAR2 (4000),
created DATE NOT NULL,
created_by VARCHAR2 (255) NOT NULL,
updated DATE NOT NULL,
updated_by VARCHAR2 (255) NOT NULL
)</code></pre>
<h3>
Start the alert checking job</h3>
<div>
Well, that's easy. Let's have it wake up every minute.</div>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>BEGIN
alert_mgr.start_check_alerts_job (every_n_minutes_in => 1);
END;</code></pre>
<h3>
Register alerts</h3>
Now I will register my first - and very basic - alert.
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>BEGIN
alert_mgr.register_alert (
alert_name_in => 'Always True',
description_in => 'Trigger is TRUE, display string',
trigger_code_in => 'TRUE',
trigger_type_in => 'RETURNTRUE',
check_frequency_in => 'H',
recurrence_frequency_in => 'D',
ignore_failures_in => 'Y',
action_type_in => 'PROC',
action_code_in => 'alert_mgr.log(''PROC EXECUTED'')',
send_email_to_in => '',
is_active_in => 'Y');
END;
</code></pre>
The type of trigger code is "RETURNTRUE" which means that when a PL/SQL expression returns TRUE, the alert is triggered. In this case, the trigger code is, well, a little bit trivial: TRUE.<br />
<br />
I tell the utility to check once an hour but to not re-trigger the alert more than once per day. And if (WHEN!) the trigger code evaluates to TRUE, execute a PL/SQL procedure, which in this case simply writes a row out to the alert_log table.<br />
<br />
About those frequencies: for my basic utility I implement only the following frequencies:<br />
<ul>
<li>H = Hourly</li>
<li>D = Daily</li>
<li>NNN = the number of minutes between checks.</li>
</ul>
<div>
So if you want the alert to be checked every two hours, pass 120 for check_frequency_in.</div>
<div>
<br /></div>
<div>
You might want to make things more flexible by using DBMS_SCHEDULER syntax.<br />
<br /></div>
Here's a more elaborate alert, with two actions to be taken if triggered:
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
l_actions alert_mgr.actions_t;
BEGIN
l_actions (1).action_type := 'PROC';
l_actions (1).action_code := 'alert_mgr.log(''Action 1'')';
l_actions (2).action_type := 'EMAIL';
l_actions (2).send_email_to := 'steven.feuerstein@oracle.com';
l_actions (2).email_subject := 'Subject is action 2';
l_actions (2).email_body := '<p>The body of the email</p>';
alert_mgr.register_alert (
alert_name_in => 'Two actions',
description_in => 'Use the array approach',
trigger_code_in => 'select count(*) from dual',
trigger_type_in => 'ONEROW',
check_frequency_in => 'H',
recurrence_frequency_in => 'H',
ignore_failures_in => 'Y',
actions_in => l_actions,
is_active_in => 'Y');
END;
</code></pre>
In this case, the type of trigger code is "ONEROW" or "Execute alert actions if the query in trigger_code returns just one row."<br />
<br />
You can also specify a query to go with your email, which I demonstrate below. The query returns two items: subject and body. These will then be used to send an email with that associated subject and body to each email address provided in the send_email_to field.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
l_index integer := 1;
l_actions alert_mgr.actions_t;
BEGIN
DELETE FROM earthquake_alerts;
l_index := 1;
l_actions (l_index).action_type := 'EMAIL';
l_actions (l_index).send_email_to :=
'steven.feuerstein@oracle.com';
l_actions (l_index).email_query := q'[
SELECT 'Magnitude ' || magnitude ||
' Earthquake detected in ' || location subject,
'Earthquake ID ' || id body
FROM earthquakes
WHERE magnitude >= 6.5
AND id NOT IN (SELECT earthquake_id FROM earthquake_alerts)]';
/* Record as notified */
l_index := 2;
l_actions (l_index).action_type := 'PROC';
l_actions (l_index).action_code := 'earthquake_mgr.record_notifications;';
/* Record in log */
l_index := 3;
l_actions (l_index).action_type := 'PROC';
l_actions (l_index).action_code :=
'alert_mgr.log(
''Earthquakes checked at '' ||
TO_CHAR (SYSDATE, ''YYYY-MM-DD HH24:MI:SS''))';
alert_mgr.register_alert (
alert_name_in => 'Notify at 6.5',
description_in =>
'Send an email for earthquakes rated at 6.5 or higher. '
|| 'Don''t send a notification twice for the same earthquake.',
trigger_code_in =>
'SELECT 1 FROM earthquakes
WHERE magnitude >= 6.5
AND id NOT IN (
SELECT earthquake_id FROM earthquake_alerts)',
trigger_type_in =>
alert_mgr.c_trigger_type_select_mult_rows,
check_frequency_in => '1',
recurrence_frequency_in => '1',
ignore_failures_in => 'Y',
actions_in => l_actions,
is_active_in => 'Y');
END;
</code></pre>
<h3>
Sending Mail</h3>
The main alerting package does not contain any email-related code. The way you send emails may vary greatly, so we made all of that code dynamic. If you want to send emails, you need to register the email procedure with the utility. I do this below, using the email package that comes with the utility (and relies on APEX_MAIL):
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>BEGIN
/* Set up email using APEX and the default API from the utility */
alert_mgr.register_email_proc (
proc_name_in => 'alert_email_mgr.send_email',
workspace_name_in => 'ALERTS');
END;
</code></pre>
In other words, if you plan to use APEX_MAIL to send emails with the alerting utility, then execute the above block with the name of your workspace, and you will be all set.
<br />
<br />
Note: I do not push emails directly onto the queue in the package body, so you may want to change that after installation.<br />
<h3>
Installing the App Alert Utility</h3>
Initially, I will make the code for App Alerts available on <a href="https://livesql.oracle.com/apex/livesql/file/content_JSCKP4KHK8OIW92C5RGA5Q9RJ.html">LiveSQL</a>. It will also soon be available on Github.<br />
<br />
After you click on the link to the script, log in to run the script, and then click on the download icon:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDsCvqaKdSLyYhZ7wF7UxhjNHMJsdR-qr4xUtWyueO4g4qKW8L6w7LVcoFwTFYA6vTyIBTSRh3rMEZoRVkeDbXOCDOwgmQ0GyL9JP78S8uvH-Kqj8d3uT4wGV52tK5QAzL5_YVwRNTFjU/s1600/a.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="366" data-original-width="1076" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDsCvqaKdSLyYhZ7wF7UxhjNHMJsdR-qr4xUtWyueO4g4qKW8L6w7LVcoFwTFYA6vTyIBTSRh3rMEZoRVkeDbXOCDOwgmQ0GyL9JP78S8uvH-Kqj8d3uT4wGV52tK5QAzL5_YVwRNTFjU/s1600/a.jpg" /></a></div>
<h3>
Problems? Suggestions?</h3>
If you run into any problems using this code or if you have suggestions for improving it, please leave a comment on this blog post, DM me on <a href="http://twitter.com/sfonplsql">Twitter</a> or send me an <a href="mailto:Steven.feuerstein@oracle.com">email</a>.Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-16983935875178375932020-03-03T14:05:00.006-08:002020-03-03T14:05:41.040-08:00Results are in for the PL/SQL Challenge Championship for 2019!<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0gozwkcwVKKmSvGExzgFzN9v2P24VGUA7Het1GreJhXrFzbP81lY1C5P5WaFX2G0KaTd2KG8EaiqpqQSRBHPLg5d6LUgaCxpE06f1eanHQCoHf-qCt52s_4eca8mt-4IITj-OE3kdv0Q/s1600/a.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="422" data-original-width="538" height="313" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0gozwkcwVKKmSvGExzgFzN9v2P24VGUA7Het1GreJhXrFzbP81lY1C5P5WaFX2G0KaTd2KG8EaiqpqQSRBHPLg5d6LUgaCxpE06f1eanHQCoHf-qCt52s_4eca8mt-4IITj-OE3kdv0Q/s400/a.jpg" width="400" /></a></div>
Another <a href="http://devgym.oracle.com/">Oracle Dev Gym</a> championship completed! Find below the rankings for the PL/SQL Challenge Championship for 2019. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.<br />
<br />
Congratulations first and foremost to our top-ranked players:<br />
<br />
<strong>1st Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:114777329069120::NO::P26_USER_ID:130439&cs=1N5MNIO6CvaWVYmRwmkdfm0RFo4dg00WZHQiY1H6uYjXpy6Q7UsBOFb0IvJp0XnP5Nb6ieNMm4VKdyuUzLswu7g">MarkusId</a><br />
<br />
2nd Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:114777329069120::NO::P26_USER_ID:4294&cs=1DsRzN0lU-TIsvLNIHVbFmb3pEVZ7ilGXlEwb786wysggYN7qfT_sYn5qruchnLXNfEKm_OsP-8rjAb1U9NKPOQ">mentzel.iudith</a><br />
<br />
3rd Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:114777329069120::NO::P26_USER_ID:487&cs=18hyqy7W0tdf3bgdyfPTS2PywjFd3UAgvN8KbOrCvzVDwXExXbjkqP6-RQ4_Fae5Dw5A4kU07VqVQ0uxPDaWlaQ">NielsHecker</a></strong><br />
<br />
Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, we will make the championship as a whole available as a workout, so you can take it just like these players did.<br />
<br />
Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community.
<br />
<br />
<table border="1" cellspacing="0" style="border-collapse: collapse; border: 1px solid blue;">
<tbody>
<tr>
<th style="width: 10%;">Rank</th>
<th style="width: 40%;">Name</th>
<th style="width: 20%;">Total Time</th>
<th style="width: 20%;">% Correct</th>
<th style="width: 10%;">Total Score</th>
</tr>
<tr>
<td>1</td>
<td>MarkusId (1)</td>
<td>28 m</td>
<td>83%</td>
<td>4185</td>
</tr>
<tr>
<td>2</td>
<td>mentzel.iudith (6)</td>
<td>37 m</td>
<td>83%</td>
<td>4151</td>
</tr>
<tr>
<td>3</td>
<td>NielsHecker (6)</td>
<td>39 m</td>
<td>81%</td>
<td>3940</td>
</tr>
<tr>
<td>4</td>
<td>Ivan Blanarik (6)</td>
<td>33 m</td>
<td>78%</td>
<td>3916</td>
</tr>
<tr>
<td>5</td>
<td>Stelios Vlasopoulos (6)</td>
<td>39 m</td>
<td>78%</td>
<td>3790</td>
</tr>
<tr>
<td>6</td>
<td>Sandra99 (4)</td>
<td>29 m</td>
<td>78%</td>
<td>3781</td>
</tr>
<tr>
<td>7</td>
<td>Jan Šerák (6)</td>
<td>36 m</td>
<td>75%</td>
<td>3703</td>
</tr>
<tr>
<td>8</td>
<td>Sartograph (3)</td>
<td>39 m</td>
<td>75%</td>
<td>3690</td>
</tr>
<tr>
<td>9</td>
<td>mcelaya (5)</td>
<td>36 m</td>
<td>75%</td>
<td>3656</td>
</tr>
<tr>
<td>10</td>
<td>PZOL (5)</td>
<td>35 m</td>
<td>75%</td>
<td>3607</td>
</tr>
<tr>
<td>11</td>
<td>Maxim Borunov (6)</td>
<td>39 m</td>
<td>72%</td>
<td>3542</td>
</tr>
<tr>
<td>12</td>
<td>Andrey Zaytsev (6)</td>
<td>34 m</td>
<td>69%</td>
<td>3462</td>
</tr>
<tr>
<td>13</td>
<td>korolkov_d_a (1)</td>
<td>39 m</td>
<td>69%</td>
<td>3442</td>
</tr>
<tr>
<td>14</td>
<td>siimkask (6)</td>
<td>21 m</td>
<td>69%</td>
<td>3415</td>
</tr>
<tr>
<td>15</td>
<td>li_bao (6)</td>
<td>22 m</td>
<td>67%</td>
<td>3411</td>
</tr>
<tr>
<td>16</td>
<td>Michal P. (4)</td>
<td>38 m</td>
<td>69%</td>
<td>3346</td>
</tr>
<tr>
<td>17</td>
<td>JeroenR (5)</td>
<td>27 m</td>
<td>67%</td>
<td>3242</td>
</tr>
<tr>
<td>18</td>
<td>seanm95 (6)</td>
<td>32 m</td>
<td>64%</td>
<td>3220</td>
</tr>
<tr>
<td>19</td>
<td>Mike Tessier (4)</td>
<td>32 m</td>
<td>67%</td>
<td>3219</td>
</tr>
<tr>
<td>20</td>
<td>Karel_Prech (6)</td>
<td>39 m</td>
<td>64%</td>
<td>3140</td>
</tr>
<tr>
<td>21</td>
<td>Aleksei Davletiarov (2)</td>
<td>38 m</td>
<td>64%</td>
<td>3096</td>
</tr>
<tr>
<td>22</td>
<td>Chad Lee (6)</td>
<td>35 m</td>
<td>61%</td>
<td>3056</td>
</tr>
<tr>
<td>23</td>
<td>Chase Mei (6)</td>
<td>25 m</td>
<td>61%</td>
<td>3050</td>
</tr>
<tr>
<td>24</td>
<td>patch72 (6)</td>
<td>16 m</td>
<td>61%</td>
<td>3033</td>
</tr>
<tr>
<td>25</td>
<td>Oleksiy Ponomarenko (4)</td>
<td>10 m</td>
<td>58%</td>
<td>2909</td>
</tr>
<tr>
<td>26</td>
<td>lmikhailov (1)</td>
<td>24 m</td>
<td>58%</td>
<td>2852</td>
</tr>
<tr>
<td>27</td>
<td>msonkoly (5)</td>
<td>38 m</td>
<td>61%</td>
<td>2847</td>
</tr>
<tr>
<td>28</td>
<td>RalfK (2)</td>
<td>09 m</td>
<td>56%</td>
<td>2813</td>
</tr>
<tr>
<td>29</td>
<td>Otto Palenicek (4)</td>
<td>34 m</td>
<td>56%</td>
<td>2763</td>
</tr>
<tr>
<td>30</td>
<td>Ludovic Szewczyk (3)</td>
<td>16 m</td>
<td>56%</td>
<td>2734</td>
</tr>
<tr>
<td>31</td>
<td>NickL (4)</td>
<td>35 m</td>
<td>53%</td>
<td>2659</td>
</tr>
<tr>
<td>32</td>
<td>Nikolay Loginov (1)</td>
<td>12 m</td>
<td>50%</td>
<td>2499</td>
</tr>
<tr>
<td>33</td>
<td>Rytis Budreika (6)</td>
<td>07 m</td>
<td>47%</td>
<td>2470</td>
</tr>
<tr>
<td>34</td>
<td>craig.mcfarlane (1)</td>
<td>37 m</td>
<td>50%</td>
<td>2352</td>
</tr>
<tr>
<td>35</td>
<td>swesley_perth (4)</td>
<td>11 m</td>
<td>44%</td>
<td>2203</td>
</tr>
</tbody>
</table>
<h2>
Championship Performance History</h2>
After each name, the quarter in which he or she played, and the ranking in that championship.<br />
<br />
<table border="1" cellspacing="0" style="border-collapse: collapse; border: 1px solid blue;">
<tbody>
<tr>
<th style="width: 30%;">Name</th>
<th style="width: 70%;">History</th>
</tr>
<tr>
<td>MarkusId</td>
<td>2019:1st</td>
</tr>
<tr>
<td>mentzel.iudith</td>
<td>2014:1st, 2015:2nd, 2016:18th, 2017:2nd, 2018:1st, 2019:2nd</td>
</tr>
<tr>
<td>NielsHecker</td>
<td>2014:21st, 2015:1st, 2016:15th, 2017:3rd, 2018:6th, 2019:3rd</td>
</tr>
<tr>
<td>Ivan Blanarik</td>
<td>2014:16th, 2015:16th, 2017:17th, 2018:4th, 2019:4th</td>
</tr>
<tr>
<td>Stelios Vlasopoulos</td>
<td>2014:37th, 2015:19th, 2016:24th, 2017:5th, 2018:14th, 2019:5th</td>
</tr>
<tr>
<td>Sandra99</td>
<td>2015:28th, 2017:15th, 2019:6th</td>
</tr>
<tr>
<td>Jan Šerák</td>
<td>2014:24th, 2015:8th, 2016:7th, 2017:22nd, 2018:13th, 2019:7th</td>
</tr>
<tr>
<td>Sartograph</td>
<td>2017:10th, 2018:31st, 2019:8th</td>
</tr>
<tr>
<td>mcelaya</td>
<td>2015:38th, 2016:34th, 2017:29th, 2018:11th, 2019:9th</td>
</tr>
<tr>
<td>PZOL</td>
<td>2015:35th, 2017:23rd, 2018:29th, 2019:10th</td>
</tr>
<tr>
<td>Maxim Borunov</td>
<td>2015:9th, 2016:17th, 2017:8th, 2018:12th, 2019:11th</td>
</tr>
<tr>
<td>Andrey Zaytsev</td>
<td>2014:2nd, 2015:5th, 2016:1st, 2017:21st, 2018:2nd, 2019:12th</td>
</tr>
<tr>
<td>korolkov_d_a</td>
<td>2019:13th</td>
</tr>
<tr>
<td>siimkask</td>
<td>2014:15th, 2015:14th, 2016:13th, 2017:12th, 2018:19th, 2019:14th</td>
</tr>
<tr>
<td>li_bao</td>
<td>2014:36th, 2017:1st, 2019:15th</td>
</tr>
<tr>
<td>Michal P.</td>
<td>2015:32nd, 2017:16th, 2019:16th</td>
</tr>
<tr>
<td>JeroenR</td>
<td>2014:7th, 2015:20th, 2016:6th, 2018:7th, 2019:17th</td>
</tr>
<tr>
<td>seanm95</td>
<td>2014:34th, 2015:4th, 2016:9th, 2017:18th, 2018:17th, 2019:18th</td>
</tr>
<tr>
<td>Mike Tessier</td>
<td>2017:33rd, 2018:18th, 2019:19th</td>
</tr>
<tr>
<td>Karel_Prech</td>
<td>2014:4th, 2015:6th, 2016:11th, 2017:9th, 2018:5th, 2019:20th</td>
</tr>
<tr>
<td>Aleksei Davletiarov</td>
<td>2018:15th, 2019:21st</td>
</tr>
<tr>
<td>Chad Lee</td>
<td>2014:13th, 2015:28th, 2016:19th, 2017:7th, 2019:22nd</td>
</tr>
<tr>
<td>Chase Mei</td>
<td>2014:25th, 2015:26th, 2016:3rd, 2017:20th, 2018:8th, 2019:23rd</td>
</tr>
<tr>
<td>patch72</td>
<td>2014:22nd, 2015:11th, 2017:35th, 2019:24th</td>
</tr>
<tr>
<td>Oleksiy Ponomarenko</td>
<td>2016:10th, 2017:4th, 2018:10th, 2019:25th</td>
</tr>
<tr>
<td>lmikhailov</td>
<td>2019:26th</td>
</tr>
<tr>
<td>msonkoly</td>
<td>2015:15th, 2017:13th, 2018:26th, 2019:27th</td>
</tr>
<tr>
<td>RalfK</td>
<td>2018:28th, 2019:28th</td>
</tr>
<tr>
<td>Otto Palenicek</td>
<td>2016:29th, 2017:28th, 2018:25th, 2019:29th</td>
</tr>
<tr>
<td>Ludovic Szewczyk</td>
<td>2019:30th</td>
</tr>
<tr>
<td>NickL</td>
<td>2015:21st, 2018:22nd, 2019:31st</td>
</tr>
<tr>
<td>Nikolay Loginov</td>
<td>2019:32nd</td>
</tr>
<tr>
<td>Rytis Budreika</td>
<td>2014:18th, 2015:12th, 2016:32nd, 2017:24th, 2019:33rd</td>
</tr>
<tr>
<td>craig.mcfarlane</td>
<td>2019:34th</td>
</tr>
<tr>
<td>swesley_perth</td>
<td>2016:21st, 2017:32nd, 2019:35th</td>
</tr>
</tbody>
</table>
</div>
<div>
<div>
</div>
</div>
Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com2tag:blogger.com,1999:blog-7849367040589270673.post-62439094877036466102020-02-19T11:13:00.004-08:002020-02-19T11:16:22.113-08:00Rankings for Logic Annual Championship for 2019<div>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh25SLEVkxCP-mSDGsr1jdJiPqFIOdCP_yI2zwrCliJTzHLAHiZpS3hOMN_fEf-BgvOZGKtUaoohRGR8iZTEuKlLBG0PmMPdAMICKLRScR1wSv9fIrmbPJNbjC0PRoFyOeu_TJ0dcY3hzI/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="383" data-original-width="746" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh25SLEVkxCP-mSDGsr1jdJiPqFIOdCP_yI2zwrCliJTzHLAHiZpS3hOMN_fEf-BgvOZGKtUaoohRGR8iZTEuKlLBG0PmMPdAMICKLRScR1wSv9fIrmbPJNbjC0PRoFyOeu_TJ0dcY3hzI/s1600/a.png" /></a></div>
You will find below the rankings for the Logic Annual Championship for 2019, held in February 2020 on the <a href="http://devgym.oracle.com/">Oracle Dev Gym</a>. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.<br />
<br />
Congratulations first and foremost to our top-ranked players:<br />
<br />
<strong>1st Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:115511390122040::NO::P26_USER_ID:11966&cs=1A2vBujg8nmI0VXvUW9sw8y1jdJLGEp9XO804PGJuWBU_nwiWP34yPkrrtroZ_lNdS_HjJHncYHrG121_xKZKJA">Stelios Vlasopoulos</a><br />
<br />
2nd Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:115511390122040::NO::P26_USER_ID:29400&cs=1TWiPBhNPjsnnrMAJXqMEtoTY-PhqW1ie5GFfO5HBK_dHHCtFiG7wYHj2g90orQA6mnTSqY_wWIZ1goBCX-9rpA">Sartograph</a><br />
<br />
3rd Place: <a href="https://devgym.oracle.com/pls/apex/f?p=10001:PROFILE:115511390122040::NO::P26_USER_ID:487&cs=18hyqy7W0tdf3bgdyfPTS2PywjFd3UAgvN8KbOrCvzVDwXExXbjkqP6-RQ4_Fae5Dw5A4kU07VqVQ0uxPDaWlaQ">NielsHecker</a> </strong><br />
<br />
Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Workout, so you can take it just like these players did.<br />
<br />
Finally, many thanks to Eli Feuerstein, Logic Quizmaster who provided a very challenging set of quizzes, and our deepest gratitude to our reviewers, especially Livio Curzola, who has once again performed an invaluable service to our community.<br />
<br />
<table border="1" cellspacing="0" style="border-collapse: collapse; border: 1px solid blue;">
<tbody>
<tr>
<th style="width: 10%;">Rank</th>
<th style="width: 40%;">Name</th>
<th style="width: 20%;">Total Time</th>
<th style="width: 20%;">% Correct</th>
<th style="width: 10%;">Total Score</th>
</tr>
<tr>
<td>1</td>
<td>Stelios Vlasopoulos (7)</td>
<td>10 m</td>
<td>100%</td>
<td>4449</td>
</tr>
<tr>
<td>2</td>
<td>Sartograph (5)</td>
<td>10 m</td>
<td>100%</td>
<td>4446</td>
</tr>
<tr>
<td>3</td>
<td>NielsHecker (7)</td>
<td>12 m</td>
<td>100%</td>
<td>4436</td>
</tr>
<tr>
<td>4</td>
<td>Ankitg97 (1)</td>
<td>23 m</td>
<td>100%</td>
<td>4381</td>
</tr>
<tr>
<td>5</td>
<td>Talebian (6)</td>
<td>25 m</td>
<td>100%</td>
<td>4370</td>
</tr>
<tr>
<td>6</td>
<td>Pavel Zeman (6)</td>
<td>36 m</td>
<td>100%</td>
<td>4320</td>
</tr>
<tr>
<td>7</td>
<td>Michal P. (3)</td>
<td>39 m</td>
<td>100%</td>
<td>4303</td>
</tr>
<tr>
<td>8</td>
<td>whab@tele2.at (5)</td>
<td>44 m</td>
<td>100%</td>
<td>4280</td>
</tr>
<tr>
<td>9</td>
<td>Tarush Shenoy (1)</td>
<td>49 m</td>
<td>100%</td>
<td>4253</td>
</tr>
<tr>
<td>10</td>
<td>Mike Tessier (5)</td>
<td>54 m</td>
<td>100%</td>
<td>4230</td>
</tr>
<tr>
<td>11</td>
<td>Eric Levin (6)</td>
<td>57 m</td>
<td>100%</td>
<td>4212</td>
</tr>
<tr>
<td>12</td>
<td>mcelaya (5)</td>
<td>57 m</td>
<td>100%</td>
<td>4211</td>
</tr>
<tr>
<td>13</td>
<td>Sayyad Hussen (1)</td>
<td>57 m</td>
<td>100%</td>
<td>4210</td>
</tr>
<tr>
<td>14</td>
<td>SQLMonster (1)</td>
<td>58 m</td>
<td>100%</td>
<td>4209</td>
</tr>
<tr>
<td>15</td>
<td>Tony Winn (5)</td>
<td>58 m</td>
<td>100%</td>
<td>4208</td>
</tr>
<tr>
<td>16</td>
<td>MarkusId (2)</td>
<td>28 m</td>
<td>96%</td>
<td>4168</td>
</tr>
<tr>
<td>17</td>
<td>Chad Lee (7)</td>
<td>40 m</td>
<td>96%</td>
<td>4110</td>
</tr>
<tr>
<td>18</td>
<td>mentzel.iudith (7)</td>
<td>42 m</td>
<td>96%</td>
<td>4099</td>
</tr>
<tr>
<td>19</td>
<td>Sandra99 (7)</td>
<td>58 m</td>
<td>96%</td>
<td>4022</td>
</tr>
<tr>
<td>20</td>
<td>li_bao (5)</td>
<td>30 m</td>
<td>92%</td>
<td>3974</td>
</tr>
<tr>
<td>21</td>
<td>Rytis Budreika (7)</td>
<td>56 m</td>
<td>92%</td>
<td>3842</td>
</tr>
<tr>
<td>22</td>
<td>gabt (2)</td>
<td>58 m</td>
<td>92%</td>
<td>3834</td>
</tr>
<tr>
<td>23</td>
<td>JasonC (7)</td>
<td>34 m</td>
<td>88%</td>
<td>3767</td>
</tr>
<tr>
<td>24</td>
<td>ted (7)</td>
<td>41 m</td>
<td>88%</td>
<td>3729</td>
</tr>
<tr>
<td>25</td>
<td>Dan Kiser (6)</td>
<td>56 m</td>
<td>79%</td>
<td>3281</td>
</tr>
<tr>
<td>26</td>
<td>lmikhailov (1)</td>
<td>56 m</td>
<td>79%</td>
<td>3280</td>
</tr>
<tr>
<td>27</td>
<td>craig.mcfarlane (6)</td>
<td>59 m</td>
<td>79%</td>
<td>3263</td>
</tr>
<tr>
<td>28</td>
<td>Stanislovas (4)</td>
<td>54 m</td>
<td>75%</td>
<td>3103</td>
</tr>
<tr>
<td>29</td>
<td>richdellheim (7)</td>
<td>58 m</td>
<td>75%</td>
<td>3085</td>
</tr>
<tr>
<td>30</td>
<td>JohnTay1or (1)</td>
<td>52 m</td>
<td>71%</td>
<td>2924</td>
</tr>
<tr>
<td>31</td>
<td>Ludovic Szewczyk (3)</td>
<td>58 m</td>
<td>71%</td>
<td>2898</td>
</tr>
<tr>
<td>32</td>
<td>seanm95 (7)</td>
<td>55 m</td>
<td>67%</td>
<td>2722</td>
</tr>
<tr>
<td>33</td>
<td>Vijay Mahawar (7)</td>
<td>55 m</td>
<td>67%</td>
<td>2721</td>
</tr>
<tr>
<td>34</td>
<td>Cor van Berkel (6)</td>
<td>56 m</td>
<td>67%</td>
<td>2719</td>
</tr>
<tr>
<td>35</td>
<td>NickL (6)</td>
<td>58 m</td>
<td>67%</td>
<td>2706</td>
</tr>
<tr>
<td>36</td>
<td>msonkoly (5)</td>
<td>59 m</td>
<td>67%</td>
<td>2703</td>
</tr>
<tr>
<td>37</td>
<td>ijaveed (1)</td>
<td>25 m</td>
<td>50%</td>
<td>2121</td>
</tr>
<tr>
<td>38</td>
<td>Henry_A (3)</td>
<td>04 m</td>
<td>46%</td>
<td>2040</td>
</tr>
</tbody>
</table>
<h2>
Championship Performance History</h2>
After each name, the quarter in which he or she played, and the ranking in that championship.<br />
<br />
<table border="1" cellspacing="0" style="border-collapse: collapse; border: 1px solid blue;">
<tbody>
<tr>
<th style="width: 30%;">Name</th>
<th style="width: 70%;">History</th>
</tr>
<tr>
<td>Stelios Vlasopoulos</td>
<td>2013:16th, 2014:29th, 2015:19th, 2016:8th, 2017:1st, 2018:1st, 2019:1st</td>
</tr>
<tr>
<td>Sartograph</td>
<td>2015:24th, 2016:21st, 2017:5th, 2018:3rd, 2019:2nd</td>
</tr>
<tr>
<td>NielsHecker</td>
<td>2013:3rd, 2014:21st, 2015:11th, 2016:27th, 2017:19th, 2018:13th, 2019:3rd</td>
</tr>
<tr>
<td>Ankitg97</td>
<td>2019:4th</td>
</tr>
<tr>
<td>Talebian</td>
<td>2014:10th, 2015:9th, 2018:14th, 2019:5th</td>
</tr>
<tr>
<td>Pavel Zeman</td>
<td>2014:7th, 2015:1st, 2016:3rd, 2017:2nd, 2018:2nd, 2019:6th</td>
</tr>
<tr>
<td>Michal P.</td>
<td>2017:24th, 2018:23rd, 2019:7th</td>
</tr>
<tr>
<td>whab@tele2.at</td>
<td>2015:30th, 2017:33rd, 2018:18th, 2019:8th</td>
</tr>
<tr>
<td>Tarush Shenoy</td>
<td>2019:9th</td>
</tr>
<tr>
<td>Mike Tessier</td>
<td>2015:40th, 2016:20th, 2017:4th, 2018:19th, 2019:10th</td>
</tr>
<tr>
<td>Eric Levin</td>
<td>2014:22nd, 2015:36th, 2016:28th, 2017:18th, 2019:11th</td>
</tr>
<tr>
<td>mcelaya</td>
<td>2015:25th, 2017:32nd, 2018:29th, 2019:12th</td>
</tr>
<tr>
<td>Sayyad Hussen</td>
<td>2019:13th</td>
</tr>
<tr>
<td>SQLMonster</td>
<td>2019:14th</td>
</tr>
<tr>
<td>Tony Winn</td>
<td>2013:25th, 2016:25th, 2017:7th, 2018:16th, 2019:15th</td>
</tr>
<tr>
<td>MarkusId</td>
<td>2019:16th</td>
</tr>
<tr>
<td>Chad Lee</td>
<td>2013:34th, 2014:31st, 2015:38th, 2016:5th, 2017:8th, 2018:20th, 2019:17th</td>
</tr>
<tr>
<td>mentzel.iudith</td>
<td>2013:4th, 2014:18th, 2015:22nd, 2016:6th, 2017:6th, 2018:11th, 2019:18th</td>
</tr>
<tr>
<td>Sandra99</td>
<td>2013:17th, 2014:19th, 2015:7th, 2016:4th, 2017:10th, 2018:4th, 2019:19th</td>
</tr>
<tr>
<td>li_bao</td>
<td>2015:39th, 2019:20th</td>
</tr>
<tr>
<td>Rytis Budreika</td>
<td>2013:12th, 2014:3rd, 2015:28th, 2017:29th, 2019:21st</td>
</tr>
<tr>
<td>gabt</td>
<td>2018:15th, 2019:22nd</td>
</tr>
<tr>
<td>JasonC</td>
<td>2013:35th, 2014:12th, 2015:26th, 2016:2nd, 2017:9th, 2018:22nd, 2019:23rd</td>
</tr>
<tr>
<td>ted</td>
<td>2013:27th, 2015:6th, 2017:14th, 2019:24th</td>
</tr>
<tr>
<td>Dan Kiser</td>
<td>2016:22nd, 2017:35th, 2019:25th</td>
</tr>
<tr>
<td>lmikhailov</td>
<td>2019:26th</td>
</tr>
<tr>
<td>craig.mcfarlane</td>
<td>2014:8th, 2015:5th, 2017:16th, 2018:12th, 2019:27th</td>
</tr>
<tr>
<td>Stanislovas</td>
<td>2016:31st, 2017:26th, 2018:24th, 2019:28th</td>
</tr>
<tr>
<td>richdellheim</td>
<td>2013:31st, 2014:6th, 2015:8th, 2016:13th, 2017:31st, 2018:10th, 2019:29th</td>
</tr>
<tr>
<td>JohnTay1or</td>
<td>2019:30th</td>
</tr>
<tr>
<td>Ludovic Szewczyk</td>
<td>2018:26th, 2019:31st</td>
</tr>
<tr>
<td>seanm95</td>
<td>2013:24th, 2014:26th, 2015:33rd, 2016:12th, 2017:22nd, 2018:5th, 2019:32nd</td>
</tr>
<tr>
<td>Vijay Mahawar</td>
<td>2015:27th, 2016:24th, 2017:17th, 2018:17th, 2019:33rd</td>
</tr>
<tr>
<td>Cor van Berkel</td>
<td>2014:36th, 2015:17th, 2018:27th, 2019:34th</td>
</tr>
<tr>
<td>NickL</td>
<td>2014:14th, 2015:23rd, 2017:20th, 2018:7th, 2019:35th</td>
</tr>
<tr>
<td>msonkoly</td>
<td>2015:21st, 2017:23rd, 2018:21st, 2019:36th</td>
</tr>
<tr>
<td>ijaveed</td>
<td>2019:37th</td>
</tr>
<tr>
<td>Henry_A</td>
<td>2014:35th, 2019:38th</td>
</tr>
</tbody>
</table>
</div>
</div>
Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-22098752231032624582020-02-12T08:36:00.003-08:002020-04-17T11:10:53.236-07:00PL/SQL Office Hours: Virtual Private Database in the Wild <div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQbXTn7bZNEec_2s2Dq16331VNSQ2BM1-BW9OokDfkrcxsU2abwWheMYDRfpDwWzFz66VmJ1lLw723T3AN0I0GfYPFP9ofncGyzI6SxP5G3m5QNSfRZ1QsCCvvLabywI9ifAyhncyWCQk/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="612" data-original-width="954" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQbXTn7bZNEec_2s2Dq16331VNSQ2BM1-BW9OokDfkrcxsU2abwWheMYDRfpDwWzFz66VmJ1lLw723T3AN0I0GfYPFP9ofncGyzI6SxP5G3m5QNSfRZ1QsCCvvLabywI9ifAyhncyWCQk/s1600/a.png" /></a></div>
Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic.<br />
<br />
One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data.<br />
<br />
Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward:<br />
<br />
A patient can only see their own information.<br />
A doctor can see only the information about their own patients.<br />
A clinic administrator can see information only about the patients in their clinic.<br />
<br />
In all three cases, the user would sign on to the application and execute the same query:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>SELECT * FROM patients</b></span><br />
<br />
and only <i>their</i> rows would appear.<br />
<br />
Of course, there are lots of different and very interesting aspects to setting up your policies.<br />
<br />
The <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/using-oracle-vpd-to-control-data-access.html#GUID-06022729-9210-4895-BF04-6177713C65A7">documentation</a> for VPD provides many details for a successful implementation, but there's not substitute for real world experience. That's what you'll hear about in our March 3, 2020 10 AM Eastern <a href="https://asktom.oracle.com/pls/apex/asktom.search?oh=401">PL/SQL Office Hours</a> session from AskTOM.<br />
<h2>
Our Presenter: Praveen Kumar of Wipro</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE5xQ5qLN8AAffOk9AmRKcZN7_vHvW6OtHx-vw20rkqcTURUaqAsya5ADAWWotBZaoET6K34uZ70BmU7I9VtB4aQUZjUXo1WAA42KEXKp9nTfAioQHqCPBfhNSoecsni7LvLMw55dCJjk/s1600/praveen.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="349" data-original-width="376" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE5xQ5qLN8AAffOk9AmRKcZN7_vHvW6OtHx-vw20rkqcTURUaqAsya5ADAWWotBZaoET6K34uZ70BmU7I9VtB4aQUZjUXo1WAA42KEXKp9nTfAioQHqCPBfhNSoecsni7LvLMw55dCJjk/s320/praveen.png" width="320" /></a></div>
<a href="http://twitter.com/k_p1729">Parveen Kumar</a> is a Java and Oracle Developer with Wipro, based in the UK. He has over eight years experience focused mainly on developing and designing applications using Oracle Database as a primary database. He aims to keep the business logic inside the database and expose data through PL/SQL APIs. He has in various projects taken advantage of native support for XML, JSON and Object datatypes, SOAP/REST APIs for Web Programming and security features like VPD/RLS, Oracle Wallet for Web APIs and other features which provide true fine grained access to different types of users.<br />
<br />
In the March Office Hours session, Praveen will show how Virtual Private Database functionality can be applied across an entire application to control what end users can see based on their role/access level. The business driver for this use of VPD is to ensure appropriate access to the sensitive/financial data in the application.<br />
<br />
Follow <a href="https://asktom.oracle.com/pls/apex/asktom.search?oh=401">this link</a> to subscribe to my monthly PL/SQL Office Hours program, so that you will receive email reminders. You can also view recordings of the dozens of past sessions, including:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhiEArnzBUmxheMe5x3dIbbaUTZUYtU7MWvV4MRYvh5lklG_kG-6Wp41x8CO75AKckuVqs_g4w1jXZY2pt_2h8Jtx3IHUWrpZw4K9BR2n_3UHfhFPCjGTbG3w1XpFze8C9KrVlI4gSGw/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="768" data-original-width="833" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlhiEArnzBUmxheMe5x3dIbbaUTZUYtU7MWvV4MRYvh5lklG_kG-6Wp41x8CO75AKckuVqs_g4w1jXZY2pt_2h8Jtx3IHUWrpZw4K9BR2n_3UHfhFPCjGTbG3w1XpFze8C9KrVlI4gSGw/s1600/a.png" /></a></div>
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-715160595420348772020-01-21T06:09:00.000-08:002020-01-21T06:09:55.842-08:00PL/SQL Puzzle: Getting the "right" error message to appearI posted the following puzzle on <a href="https://twitter.com/sfonplsql/status/1217863694807420935" target="_blank">Twitter</a>:<br />
<blockquote class="tr_bq">
<span style="background-color: white; color: #14171a; font-family: , , , "segoe ui" , "roboto" , "ubuntu" , "helvetica neue" , sans-serif; font-size: 23px; white-space: pre-wrap;">What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution?</span></blockquote>
Try it yourself before reading the rest of the post!<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-AEjus8DXILblqAyzoOrShfekOGyrOc1oFSv7wwGXUycrEB6GedITzxbrjciE2eIKNDhdr9H202eQCdCprh4tQ3xZVNHulBtsTCjjlaYeIDur2P1KbOP2ty2XLitYxLGnksei59_Q_lA/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="422" data-original-width="823" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-AEjus8DXILblqAyzoOrShfekOGyrOc1oFSv7wwGXUycrEB6GedITzxbrjciE2eIKNDhdr9H202eQCdCprh4tQ3xZVNHulBtsTCjjlaYeIDur2P1KbOP2ty2XLitYxLGnksei59_Q_lA/s1600/a.png" /></a></div>
<span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><b>White space</b></span><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">so you do not immediately</span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">see my answer. </span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">:-)</span></b><br />
<div>
<b><span style="font-size: x-large;"><br /></span></b></div>
Here are the answers from the TwitterSphere:<br />
<br />
Change line 5's assignment to <span style="font-family: "courier new" , "courier" , monospace;"><b>dbms_sql.number_table(1=>1,2=>1)</b></span><br />
<br />
In other words, try to insert the same value twice. Since there is a unique index on the column, that will cause ORA-00001 to be raised.<br />
<br />
So that will do it, right?<br />
<br />
Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution:<br />
<br />
The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is <i>unsigned</i>. In other words, 1 rather than -1 is stored. Unfortunately, the SQLERRM function assumes that the error code you pass it will be signed (negatively). So you must multiply the value in the pseudo-collection by -1. Then SQLERRM will return the right string.<br />
<br />
Or as Dirk puts it:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>(sqlerrm (sql%bulk_exceptions (indx).error_code));</b></span><br />
<br />
must be<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>(sqlerrm (0 - sql%bulk_exceptions (indx).error_code));</b></span><br />
<br />
You don't really need the 0, though. You can write simply:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace; font-weight: bold;">(sqlerrm (-sql%bulk_exceptions (indx).error_code));</span><br />
<br />
You can see all these variations at work in my <a href="https://livesql.oracle.com/apex/livesql/file/content_JJ5LEHSJ4HQ4HK6ZUKOKR1MPM.html">LiveSQL script</a>.<br />
<br />
Actually, I was surprised that I did not receive any "silly" answers. After all, there are lots of ways to get "ORA-00001: unique constraint" to appear on the screen, such as:<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
two_ts DBMS_SQL.number_table := DBMS_SQL.number_table (1=>1,2=>2);
BEGIN
DBMS_OUTPUT.PUT_LINE ('ORA-00001: unique constraint');
RETURN;
FORALL indx IN 1 .. 2
SAVE EXCEPTIONS
INSERT INTO t VALUES (two_ts(indx));
DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' inserted');
ROLLBACK;
EXCEPTION
WHEN others
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
(SQLERRM (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
END LOOP;
END;
</code></pre>
<br />
Yes, that's right, simply display it on the screen and then shortcut everything else with RETURN; (or not, let the rest of the code execute unchanged).<br />
<br />
I guess it was a serious week for the Oracle Database developer community. :-)Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-56143792596394025992020-01-10T04:36:00.000-08:002020-01-10T04:36:26.950-08:00Oracle Dev Gym PL/SQL Championship for 2019 PlayersThroughout 2019, over 1200 Oracle Database developers participated in the <a href="http://devgym.oracle.com/" target="_blank">Oracle Dev Gym</a> PL/SQL Challenge weekly tournament. The top 50 ranked players shown below will compete for top honors in a championship on February 18, 2020.<br />
<br />
The number in parentheses after their names are the number of championships in which they have already participated. As you can see, there are some very dedicated players here!<br />
<br />
Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!<br />
<br />
<table border="1" cellspacing="0" style="border-collapse: collapse; border: 1px solid blue;">
<tbody>
<tr>
<th style="width: 40%;">Name</th>
<th align="center" style="width: 10%;">Rank</th>
</tr>
<tr>
<td>Stelios Vlasopoulos (16)</td>
<td>1</td>
</tr>
<tr>
<td>mentzel.iudith (19)</td>
<td>2</td>
</tr>
<tr>
<td>NielsHecker (20)</td>
<td>3</td>
</tr>
<tr>
<td>Chad Lee (16)</td>
<td>4</td>
</tr>
<tr>
<td>Peterman (6)</td>
<td>5</td>
</tr>
<tr>
<td>siimkask (19)</td>
<td>6</td>
</tr>
<tr>
<td>_tiki_4_ (12)</td>
<td>7</td>
</tr>
<tr>
<td>Chase Mei (5)</td>
<td>8</td>
</tr>
<tr>
<td>Ludovic Szewczyk (4)</td>
<td>9</td>
</tr>
<tr>
<td>li_bao (7)</td>
<td>10</td>
</tr>
<tr>
<td>MarkusId (0)</td>
<td>11</td>
</tr>
<tr>
<td>Andrey Zaytsev (8)</td>
<td>12</td>
</tr>
<tr>
<td>Michal P. (3)</td>
<td>13</td>
</tr>
<tr>
<td>Arjun Barath (0)</td>
<td>14</td>
</tr>
<tr>
<td>Ivan Blanarik (13)</td>
<td>15</td>
</tr>
<tr>
<td>Maxim Borunov (6)</td>
<td>16</td>
</tr>
<tr>
<td>lmikhailov (0)</td>
<td>17</td>
</tr>
<tr>
<td>mcelaya (4)</td>
<td>18</td>
</tr>
<tr>
<td>tonyC (5)</td>
<td>19</td>
</tr>
<tr>
<td>patch72 (6)</td>
<td>20</td>
</tr>
<tr>
<td>Karel_Prech (9)</td>
<td>21</td>
</tr>
<tr>
<td>Sandra99 (3)</td>
<td>22</td>
</tr>
<tr>
<td>Oleksiy Ponomarenko (4)</td>
<td>23</td>
</tr>
<tr>
<td>Mike Tessier (3)</td>
<td>24</td>
</tr>
<tr>
<td>Vyacheslav Stepanov (18)</td>
<td>25</td>
</tr>
<tr>
<td>Jan Šerák (5)</td>
<td>26</td>
</tr>
<tr>
<td>seanm95 (6)</td>
<td>27</td>
</tr>
<tr>
<td>msonkoly (4)</td>
<td>28</td>
</tr>
<tr>
<td>Rakesh Dadhich (11)</td>
<td>29</td>
</tr>
<tr>
<td>pjas (2)</td>
<td>30</td>
</tr>
<tr>
<td>PZOL (5)</td>
<td>31</td>
</tr>
<tr>
<td>korolkov_d_a (0)</td>
<td>32</td>
</tr>
<tr>
<td>Henry_A (6)</td>
<td>33</td>
</tr>
<tr>
<td>Talebian (6)</td>
<td>34</td>
</tr>
<tr>
<td>Sartograph (2)</td>
<td>35</td>
</tr>
<tr>
<td>Rytis Budreika (7)</td>
<td>36</td>
</tr>
<tr>
<td>syukhno (2)</td>
<td>37</td>
</tr>
<tr>
<td>NickL (4)</td>
<td>38</td>
</tr>
<tr>
<td>swesley_perth (5)</td>
<td>39</td>
</tr>
<tr>
<td>Nikolay Loginov (0)</td>
<td>40</td>
</tr>
<tr>
<td>Sachi (3)</td>
<td>41</td>
</tr>
<tr>
<td>craig.mcfarlane (0)</td>
<td>42</td>
</tr>
<tr>
<td>RalfK (1)</td>
<td>43</td>
</tr>
<tr>
<td>JeroenR (12)</td>
<td>44</td>
</tr>
<tr>
<td>umir (0)</td>
<td>45</td>
</tr>
<tr>
<td>JasonC (4)</td>
<td>46</td>
</tr>
<tr>
<td>Otto Palenicek (3)</td>
<td>47</td>
</tr>
<tr>
<td>Aleksei Davletiarov (1)</td>
<td>48</td>
</tr>
<tr>
<td>ted (2)</td>
<td>49</td>
</tr>
<tr>
<td>StasKa (0)</td>
<td>50</td>
</tr>
</tbody>
</table>
Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-68963764775198585742019-12-16T04:10:00.001-08:002019-12-16T04:12:57.705-08:00PL/SQL Office Hours: DB Setup and Teardown for Automated Testing<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhepqOUdMk0HjeuI0gIUcqV6EgFJYKV2BJv7W3lIPnsAJg9Rz3t2kpJml28sMm8knCYqtreAGjJHzdPqDWJbrsKSDugmxUeRXAP_RKOdq08yKqJ1EAmA9U5MtPD_hhlBQKQcwbgq6yht6c/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="779" data-original-width="1101" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhepqOUdMk0HjeuI0gIUcqV6EgFJYKV2BJv7W3lIPnsAJg9Rz3t2kpJml28sMm8knCYqtreAGjJHzdPqDWJbrsKSDugmxUeRXAP_RKOdq08yKqJ1EAmA9U5MtPD_hhlBQKQcwbgq6yht6c/s1600/a.png" /></a></div>
On <b>January 14, 2020 at 9 AM Eastern</b>, I am very pleased to hold a <a href="https://asktom.oracle.com/pls/apex/asktom.search?office=401" target="_blank">PL/SQL Office Hours session</a> on one of the biggest challenges faced by developers setting up automated tests for database code: setup and teardown.<br />
<br />
No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories and your questions, and let's all work together on improving our code quality!<br />
<br />
For this session, we have two presenters: Deepti Bandari and Jasmin Fluri.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcQipkxRYdK-qp8DZdDmoFBL969ZTwefx0izk4Rci3l_BRyr64Tjwwzp1WH6fKIxUdi9WvA8y2y7ygU4GwNY8gQ_erZUACI7JprPrhasvkTwlkvkWB4njb1_9VBu9s00eQlZI4XXdFxOg/s1600/dee.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="344" data-original-width="407" height="270" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcQipkxRYdK-qp8DZdDmoFBL969ZTwefx0izk4Rci3l_BRyr64Tjwwzp1WH6fKIxUdi9WvA8y2y7ygU4GwNY8gQ_erZUACI7JprPrhasvkTwlkvkWB4njb1_9VBu9s00eQlZI4XXdFxOg/s320/dee.png" width="320" /></a></div>
<a href="http://twitter.com/deebandari" target="_blank">Deepti Bandari</a> is a senior software engineer at Fidelity Investments since 2013. Her focus areas include database design and development, test automation and promoting engineering excellence. She will share her experience with data setup and teardown for testing pl/sql, including:<br />
<br />
* Testing complex code in a monolithic database. * How to setup data so tests can run fast, in isolation, and are repeatable<br />
* Working with hierarchical data with a lot of dependent tables<br />
* When to rely on existing data in environments<br />
<br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix7RqfUSYQLHpMhcXUzKgyijuWYzTDHbYf17g4dqt7TcWjWgBOpwC0iIICxUhkDI_vZt0JXNC0JIK5BxBWNcTrvBaMCq63F1GzeRF6-qMjGiiQTyfHOFIShEyQNknqDkF9n5isd0suuMw/s1600/jasmin_fluri.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="454" data-original-width="680" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix7RqfUSYQLHpMhcXUzKgyijuWYzTDHbYf17g4dqt7TcWjWgBOpwC0iIICxUhkDI_vZt0JXNC0JIK5BxBWNcTrvBaMCq63F1GzeRF6-qMjGiiQTyfHOFIShEyQNknqDkF9n5isd0suuMw/s400/jasmin_fluri.jpg" width="400" /></a></div>
<a href="http://twitter.com/jfluri_" target="_blank">Jasmin Fluri</a> is an independent consultant at Schaltstelle GmbH and lectures on software engineering and code review at the University of Applied Sciences North-western Switzerland. Her focus as a database developer and DevOps engineer lies on continuous integration and delivery pipelines, automation of recurring tasks, PL/SQL development, data engineering, and data warehousing.<br />
<br />
Jasmin will talk about stages of testing and management of test data. She will show examples on how to prepare and arrange unit test with test data in development environments. Important points on what to consider when starting with unit testing and test data are explained.<br />
<div>
<br /></div>
We hope to see you at the session. And I wouldn't be at all surprised if you have some experiences in this area, as well. We hope you will share them with all attendees. Please do <a href="mailto:steven.feuerstein@oracle.com" target="_blank">get in touch</a> in advance if you'd like to present those experiences as more than a comment in the chat. I'll add you to our agenda.<br />
<br />
Finally, subscribe for reminders at the PL/SQL Office Hours <a href="https://asktom.oracle.com/pls/apex/asktom.search?office=401" target="_blank">home page</a> so that you can get email reminders about this and other sessions!Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-19912709249416690192019-12-11T04:23:00.000-08:002020-04-13T12:03:35.653-07:00PL/SQL Puzzle: when implicit conversions come callingI posted the following puzzle on <a href="https://twitter.com/sfonplsql/status/1204362313752596480" target="_blank">Twitter</a>:<br />
<blockquote class="tr_bq">
A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen?</blockquote>
Try it yourself before reading the rest of the post!<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZBIftxlc4iWjprhhkP2V-HSYnao51Xf_P3Gawst6-9dBRRAfdxkdjmwB2rOGNXsYgyyhEN1RRlWmuYfBjpcXNul3Gs3HSMoxa-yvzaNq88QmlG46geB0XDeSYA8CQQB5BK7pbfEuL6WE/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="681" data-original-width="668" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZBIftxlc4iWjprhhkP2V-HSYnao51Xf_P3Gawst6-9dBRRAfdxkdjmwB2rOGNXsYgyyhEN1RRlWmuYfBjpcXNul3Gs3HSMoxa-yvzaNq88QmlG46geB0XDeSYA8CQQB5BK7pbfEuL6WE/s1600/a.png" /></a></div>
<span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><b>White space</b></span><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">so you do not immediately</span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">see my answer. </span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">:-)</span></b><br />
<div>
<b><span style="font-size: x-large;"><br /></span></b></div>
The output is:<br />
<br />
10<br />
1<br />
9<br />
<br />
I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion.<br />
<br />
As noted by several people, this is a collection indexed by <i>strings</i>, not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle.<br />
<br />
The value being used in the assignment of 100 to elements in the array is an integer (indx).<br />
<br />
Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2" ... "9", and finally "10". These are the actual index values used.<br />
<br />
So certainly ten elements are added to the collection and so the count returned is 10.<br />
<br />
But when it comes to FIRST and LAST, you have to understand how PL/SQL will determine the "lowest" defined index value and the "highest" defined index value.<br />
<br />
For integer-indexed collections, it is clear: whichever number is lowest or highest.<br />
<br />
But for a string-indexed collection, "lowest" and "highest" are determined by the character set ordering. And in this case "1" comes before "2", "2" before "3" ... "8" before "9".... but "10"? That is <i>not</i> greater than "9". It is greater than "1" and less than "2" as you can see by running this block:<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
TYPE t IS TABLE OF INTEGER
INDEX BY VARCHAR2 (3);
tt t;
l_index VARCHAR2 (3);
BEGIN
FOR indx IN 1 .. 10
LOOP
tt (indx) := 100;
END LOOP;
l_index := tt.FIRST;
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (l_index);
l_index := tt.NEXT (l_index);
END LOOP;
END;
1
10
2
3
4
5
6
7
8
9
</code></pre>
<br />
Sometimes PL/SQL makes it too, too easy to work with compatible datatypes. :-)<br />
<br />
Check out my <a href="https://livesql.oracle.com/apex/livesql/file/content_JC3YOYT7533XBW5FCGNDQYRQY.html" target="_blank">LiveSQL script </a>demonstrating all of this.<br />
<br />
Also, <a href="https://twitter.com/hasanfalizada" target="_blank">Hasan Alizada</a> was kind enough to offer this screenshot on Twitter offering a very nice explanation. Thanks, Hasan!<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZjd9lZSuEn_tbjD98uO11CiMK5gQmb6HiiQCl6jTIwqxnRbdHT3MXsvLMaT1cYNC1qOTIrSvLFyBJmAL2XWIl5maUeNtMSFoDjlQM6XcWL9BeM6V_umEHRGKZDrRKiicCSNDfByv6r70/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="616" data-original-width="662" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZjd9lZSuEn_tbjD98uO11CiMK5gQmb6HiiQCl6jTIwqxnRbdHT3MXsvLMaT1cYNC1qOTIrSvLFyBJmAL2XWIl5maUeNtMSFoDjlQM6XcWL9BeM6V_umEHRGKZDrRKiicCSNDfByv6r70/s1600/a.png" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com3tag:blogger.com,1999:blog-7849367040589270673.post-77028620315146349132019-12-04T09:11:00.002-08:002019-12-04T11:34:17.296-08:00Dynamic Polymorphism - Why, What, How<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSXKnHSmw6P87m3hmvbNHzh_jxa1B9Gy8JyMdWCl80grOKvjliNBwm6eKdYe1JVNZ27GVLBtdI_pvs-Tm-yeVbtKAZ5cWEGNBhKpGFm9ZE5TCF-lAwzR1VKbSD5O682MwSmpTP5VODLAg/s1600/a.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" data-original-height="616" data-original-width="924" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSXKnHSmw6P87m3hmvbNHzh_jxa1B9Gy8JyMdWCl80grOKvjliNBwm6eKdYe1JVNZ27GVLBtdI_pvs-Tm-yeVbtKAZ5cWEGNBhKpGFm9ZE5TCF-lAwzR1VKbSD5O682MwSmpTP5VODLAg/s320/a.png" width="320" /></a></div>
Dynamic means "run-time."<br />
<br />
Polymorphism means "multiple shapes."<br />
<br />
Synonyms for dynamic polymorphism include "runtime polymorphism" and "dynamic method dispatch."<br />
<br />
If you are a "traditional" relational database developer, these terms might sound unfamiliar. But how about <i>overloading</i>? Are you familiar with that?<br />
<br />
Overloading occurs when you have more than one subprogram (procedure and/or function) with the same name in the declaration section of a block, package specification or package body. These subprograms need to differ by parameter list or type (procedure vs function) in a way that is sufficient for the compiler to distinguish.<br />
<br />
Well, guess what? Another name for overloading is "static polymorphism."<br />
<br />
Static means "compile-time."<br />
<br />
Polymorphism means "multiple shapes."<br />
<br />
Why, you might be wondering, does the Oracle Database need to wait till runtime to determine which method in which type in the hierarchy should be called?<br />
<br />
After all, it doesn't have any troubling sorting that out with overloading of subprograms in packages!<br />
<br />
The answer to that question lies in one word: substitutability.<br />
<br />
It's a topic I've touched on both directly and indirectly in my previous posts in this series. The best way to think about substitutability is that if I have the following type hierarchy....<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TYPE food_ot AS OBJECT (
name VARCHAR2(100),
food_group VARCHAR2 (50),
grown_in VARCHAR2 (100)
)
NOT FINAL
;
CREATE TYPE dessert_t UNDER food_ot (
contains_chocolate VARCHAR2(1),
year_created NUMBER(4)
)
NOT FINAL
;
</code></pre>
....then this follows:<br />
<blockquote class="tr_bq">
Every dessert is a food, but not every item of food is a dessert.</blockquote>
And now with the S word:<br />
<blockquote class="tr_bq">
Where I have an instance of food, I can <i>substitute</i> it with an instance of dessert.</blockquote>
This ability to substitute is precisely what drives the need for dynamic polymorphism in object-oriented languages. Let's find out why.<br />
<br />
Since polymorphism has to do with choosing the right method, let's enhance my food-related hierarchy to include a member method in each type.
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TYPE food_ot AS OBJECT (
name VARCHAR2 (100),
food_group VARCHAR2 (100),
grown_in VARCHAR2 (100),
MEMBER FUNCTION price
RETURN NUMBER
)
NOT FINAL;
/
CREATE OR REPLACE TYPE BODY food_ot
IS
MEMBER FUNCTION price
RETURN NUMBER
IS
BEGIN
RETURN (CASE self.food_group
WHEN 'PROTEIN' THEN 3
WHEN 'FRUIT' THEN 2
WHEN 'VEGETABLE' THEN 1
END);
END;
END;
/
CREATE TYPE dessert_ot
UNDER food_ot (
contains_chocolate VARCHAR2 (1),
year_created NUMBER (4),
OVERRIDING MEMBER FUNCTION price
RETURN NUMBER
)
NOT FINAL;
/
CREATE OR REPLACE TYPE BODY dessert_ot
IS
OVERRIDING MEMBER FUNCTION price
RETURN NUMBER
IS
BEGIN
RETURN 100;
END;
END;
/
</code></pre>
I'm keeping the price formula really simple for desserts. :-)
<br />
<br />
And now consider the following block:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
TYPE foodstuffs_nt IS TABLE OF food_ot;
fridge_contents foodstuffs_nt
:= foodstuffs_nt (food_ot ('Brussels Sprouts', 'VEGETABLE', 'Farm'),
dessert_ot ('Strawberries',
'FRUIT',
'Backyard',
'N',
2001));
BEGIN
FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST
LOOP
DBMS_OUTPUT.put (
CASE
WHEN fridge_contents (indx) IS OF (ONLY food_ot)
THEN
'Food'
WHEN fridge_contents (indx) IS OF (ONLY dessert_ot)
THEN
'Dessert'
END
|| ' price:');
DBMS_OUTPUT.put_line (fridge_contents (indx).price ());
END LOOP;
END;
/
</code></pre>
We can see from this code why overloading or static polymorphism is not sufficient when it comes to executing the right method.<br />
<br />
When the block is compiled, the PL/SQL engine knows that the fridge_contents nested table is filled with instances of type food_t. It could even, I suppose, notice that the nested table contains instances of food_t and dessert_t.<br />
<br />
But it sure is hard to see how at compile time. the PL/SQL engine would know which price method should be used in the call to DBMS_OUTPUT.PUT_LINE. After all, fridge_contents (indx) at compile time is of type food_t (and, of course, because of substitutability, it could also be any subtype of food_t, but what compiler could sort <i>that</i> out?).<br />
<br />
It is only when the block is executed that PL/SQL can check to see the actual type of the instance in that element of the collection and invoke the appropriate method.<br />
<br />
And as you can see from my use of the IS OF syntax, it is possible for both <i>us</i> and the PL/SQL engine to get that type.<br />
<br />
You can run this code for yourself on <a href="https://livesql.oracle.com/apex/livesql/file/content_JB45RTM5JFFBXPLS5S667UKH6.html" target="_blank">LiveSQL</a>.<br />
<div>
<h2>
Check Out the Entire Series</h2>
Visit <a href="https://stevenfeuersteinonplsql.blogspot.com/2019/09/object-types-and-object-oriented.html" target="_blank">this post</a> that gives you quick access to all the articles in the series.</div>
<br />
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com3tag:blogger.com,1999:blog-7849367040589270673.post-66602213724557228252019-12-02T11:13:00.000-08:002019-12-02T11:19:26.542-08:00Wait, that's NOT a reserved word?When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time.<br />
<br />
Here's the puzzle:<br />
<blockquote class="tr_bq">
After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)?</blockquote>
a. "No dummy"<br />
b. Unhandled VALUE_ERROR exception<br />
c. Unhandled NO_DATA_FOUND exception<br />
d. Compilation error<br />
e. Your guess is as good as mine.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM62CshuntTsHNPz4U5AK3dVSvp69ESTAsqORsp8brpmM6G3lWyPZ88SNhpEAmycKCwVHAy35xMkgyUtoOYLNn9VWdZhSyEOOzYNBL2mcETHMAoIFr3Up9OuZTNKndO2h340tERXt4vow/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="492" data-original-width="643" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM62CshuntTsHNPz4U5AK3dVSvp69ESTAsqORsp8brpmM6G3lWyPZ88SNhpEAmycKCwVHAy35xMkgyUtoOYLNn9VWdZhSyEOOzYNBL2mcETHMAoIFr3Up9OuZTNKndO2h340tERXt4vow/s1600/a.png" /></a></div>
<br />
Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPzH2nHH17llo6DNNWeaIKYPzjMkAf-24fRF-1TTn1RkwnSvOMbep6C08Clun2FP5IACs1MHrPzPcoMPBwNiIeRJAApwPm5WBp7vxOY22B6Ns4IC_GiHz5MS4SCzKPEVKveJEOT9R_V0Y/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="547" data-original-width="732" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPzH2nHH17llo6DNNWeaIKYPzjMkAf-24fRF-1TTn1RkwnSvOMbep6C08Clun2FP5IACs1MHrPzPcoMPBwNiIeRJAApwPm5WBp7vxOY22B6Ns4IC_GiHz5MS4SCzKPEVKveJEOT9R_V0Y/s1600/a.png" /></a></div>
OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!".<br />
<br />
After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2.<br />
<br />
So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all.<br />
<br />
And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So "No dummy" it is, right?<br />
<br />
Wrong.<br />
<br />
Now, a number of people in Twitterland also chose (d) Compilation error.<br />
<br />
They reasoned that "no_data_found" and "pls_integer" are reserved words in PL/SQL. So if you try to use them for your own identifiers, the PL/SQL compiler slaps you down.<br />
<br />
That's very solid reasoning....and it would be true <i>if</i> those were actually reserved words.<br />
<br />
Now, IF is definitely a reserved word, so if I try to declare a variable with <i>that</i> name, nuh-uh:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibYGto9QOZ1wxbOI6FCdd55MP5p1Utf2aex2S8CxI_EaRIri9WEZvfv7zfee758-Fz8x7Q2JLqqQVAxAgyDkQLycUnV3AOTol5_dllVxZyLSBmiOq9z6T3tCHVS2jx7pGXkdCPVyOBrVc/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="253" data-original-width="779" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibYGto9QOZ1wxbOI6FCdd55MP5p1Utf2aex2S8CxI_EaRIri9WEZvfv7zfee758-Fz8x7Q2JLqqQVAxAgyDkQLycUnV3AOTol5_dllVxZyLSBmiOq9z6T3tCHVS2jx7pGXkdCPVyOBrVc/s1600/a.png" /></a></div>
<br />
NO_DATA_FOUND and PLS_INTEGER are <i>not</i> reserved words, which you can confirm with a query in your database:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9ATui2VZ4aRCZKNRAa84FB-tvSw36pggtW8ptBR8gOiZJ0Ad4HX3Z613nRS3Dutszur3NDBY5WocyJKlhPIf_LyrQIMURe0hSZNwb-UtwfmVJDBUB3mfcXjoYXmags3HJxBlc5_eI9cg/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="299" data-original-width="571" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9ATui2VZ4aRCZKNRAa84FB-tvSw36pggtW8ptBR8gOiZJ0Ad4HX3Z613nRS3Dutszur3NDBY5WocyJKlhPIf_LyrQIMURe0hSZNwb-UtwfmVJDBUB3mfcXjoYXmags3HJxBlc5_eI9cg/s1600/a.png" /></a></div>
That's right - I got a NO_DATA_FOUND exception searching for "NO_DATA_FOUND" as a reserved word. :-)<br />
<br />
OK but to be completely honest with you, that view contains SQL keywords. Check <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-reserved-words-keywords.html#GUID-9BAA3A99-41B1-45CB-A91E-1E482BC1F927" target="_blank">Appendix D of the PL/SQL Language Reference</a> for PL/SQL Reserved Words and Keywords.<br />
<br />
So "no_data_found" and "pls_integer" are not reserved words, which means I can use them myself in my code. Which then means that any usage of those names in my block will be resolved to <i>my</i> variables or types or whatever they are, and not the pre-defined element in the PL/SQL language.<br />
<br />
Specifically the "when no_data_found" handler will trap only the no_data_found exception that I declared in my code, which is <i>not</i> the same exception as that raised by the PL/SQL runtime engine.<br />
<br />
In fact, the only way that handler could trap anything is if "RAISE no_data_found" was executed in this block.<br />
<br />
I hope that makes sense. It is of course very confusing and even if "no_data_found" is not a reserved word, you should certainly avoid using it for your own identifiers in your code.<br />
<br />
But if "no_data_found" is not a reserved word, what is it? Is it a keyword? Not even that. It doesn't appear in the D-2 table, but you should think of it as a keyword since the word on keywords is "You can use keywords as ordinary user-defined identifiers, but it is not recommended."<br />
<br />
In fact, "no_data_found" is an exception declared in a special package called STANDARD. That package is owned by SYS and, along with DBMS_STANDARD, define many elements of the PL/SQL language, including exceptions like DUP_VAL_ON_INDEX and VALUE_ERROR and types like PLS_INTEGER.
<br />
<br />
You can view the package source as follows:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code> SELECT line, text
FROM all_source
WHERE TYPE = 'PACKAGE' AND name = 'STANDARD' AND owner = 'SYS'
ORDER BY line
</code></pre>
You can also view the package body, but it's not very helpful, except to reveal to you that the PL/SQL dev team has access to functionality that we do not. Which should not come as a surprise to anybody.<br />
<br />
In that package specification, you will find lines of code like this:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjN75coFdZdzGLZAML26yzYLbPAFYXzvUx_h7OszlBzyZGZsIldxGT4sedJiVQz4nVygNEORo6KqfkSyXgCPQFgCz1EkfhPBxRN2VT1AWkXntM7IL_B9v-1nV75rajHP3IlS9vxP1dBMC8/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="469" data-original-width="792" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjN75coFdZdzGLZAML26yzYLbPAFYXzvUx_h7OszlBzyZGZsIldxGT4sedJiVQz4nVygNEORo6KqfkSyXgCPQFgCz1EkfhPBxRN2VT1AWkXntM7IL_B9v-1nV75rajHP3IlS9vxP1dBMC8/s1600/a.png" /></a></div>
<br />
At which point, I am sure you are now saying to yourself: "What? What is that number for NO_DATA_FOUND? 100? I thought the error code for this exception was -1403." Yeah, it's a rather strange outlier of an exception. Bottom line is that the ANSI standard error code for "no data found" is 100, so we go with that, but when the exception is raised, SQLCODE returns -1403.<br />
<br />
Get over it.<br />
<br />
And there you have it. Now you know that you, too, can write code that is so hard to read, so confusing, so downright strange that everyone will conclude that you must be a genius.<br />
<br />
Since, however, neither you nor I are a genius, I suggest you <i>never</i> write code like this.<br />
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com2tag:blogger.com,1999:blog-7849367040589270673.post-42591144393571518802019-11-26T07:38:00.000-08:002019-11-26T07:38:16.657-08:00PL/SQL Puzzle: what assumptions am I making?Almost certainly, whenever you write a procedure or function, you make certain assumptions. Some of them are quite reasonable, such as "I assume my database is up and running." Some of them are scary, such as "I assume my users will never change their minds."<br />
<br />
But many simply go unnoticed. You don't even realize you are making an assumption until it smacks you in face, most likely in production, when an unexpected error exposes the assumption.<br />
<br />
So in this PL/SQL puzzle, as I state on <a href="https://twitter.com/sfonplsql/status/1199033109997608960" target="_blank">Twitter</a>:<br />
<blockquote class="tr_bq">
The procedure shown below compiles without error. What assumptions am I making so that when it executes, it does not terminate with an exception?</blockquote>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQyilIBkeUsbwXEH7PsdgZat_zQYa0NzmL8KfAwOF3X0FJ-Ggrv1PsKbBCt67V9V15erGV_3waB10F2-rgWgNAHJr5_kD3SdgDLgA-5xSDn8lda8lGLwoEtHHaunMXVr0ADEaZPwNmrTA/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="477" data-original-width="802" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQyilIBkeUsbwXEH7PsdgZat_zQYa0NzmL8KfAwOF3X0FJ-Ggrv1PsKbBCt67V9V15erGV_3waB10F2-rgWgNAHJr5_kD3SdgDLgA-5xSDn8lda8lGLwoEtHHaunMXVr0ADEaZPwNmrTA/s1600/a.png" /></a></div>
<br />
<span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><b>White space</b></span><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">so you do not immediately</span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">see my answers. </span></b><br />
<b><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;"><br /></span><span style="font-size: x-large;">:-)</span></b><br />
<br />
OK, let's dive in. I provide below all of the assumptions I was aware, and also some others that were provided in Twitter on the very active discussion that followed. As usual, I learned something new from the community!<br />
<br />
Line 3: by hardcoding the datatype to VARCHAR2(100) we assume that the last names in the employees table never exceed that length. Better to use:
TABLE OF employees.last_name%TYPE<br />
<br />
Lines 7 - 10: An unlimited BULK COLLECT assumes there will always be enough PGA (session memory) for the collection, no matter how big the table gets.
A nasty assumption, bound to fail long after you left the project. Use FETCH with LIMIT instead as demonstrated in this <a href="https://livesql.oracle.com/apex/livesql/file/content_CWS9H46VW6EN764N4KFI8ZWA5.html" target="_blank">LiveSQL script</a>.<br />
<br />
Also, <a href="https://twitter.com/GebalJacek" target="_blank">Jacek Gebal</a> suggests another, related assumption: The number of rows in the collection doesn't exceed the limit for nested tables. Since that limit is 2**31 (2 raised to the 31st power), I am pretty sure you will run out of PGA memory first. But he's right: it's still an assumption. :-)<br />
<br />
Line 12: Assumes that there is at least 1 element in the collection. Otherwise, FIRST and LAST return NULL and PL/SQL raises<br />
<br />
<b><span style="font-family: "courier new" , "courier" , monospace;">ORA-06502: PL/SQL: numeric or value error </span></b><br />
<br />
What should you do instead?
Much better:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>FOR indx IN 1 .. l_employees.COUNT </b></span><br />
<br />
Which assumes a sequentially filled collection from 1. So what is better about that?
BULK COLLECT always fills a collection from index 1 and sequentially from there.<br />
<br />
Line 14: Assumes that do_more_stuff accepts VARCHAR2 or CLOB values and also does not raise exception.
<br />
<br />
Line 16 - There is no exception handler! So one really big fat assumption I make is that none of the code in the procedure will cause an exception to be raised. In particular, as pointed out by <a href="https://twitter.com/abdul_samed" target="_blank">Abul Samed</a>, I assume that Exception handling is done in procedure do_more_stuff.<br />
<br />
Or....I have decided that I do not care if an exception is raised in either of the procedures, because my standards dictate that I only handle exceptions at the outermost block and this procedure is called by others. I don't generally consider that a good idea. I like to handle exceptions locally, log any application state specific to the block (such as values of local variables).<br />
<br />
What else did I assume?<br />
<br />
Did I miss anything? Do you have any stories to share about assumptions you've made in your code or seen in other developers' code that resulted in some less-than-optimal results?<br />
<div>
<br /></div>
<div>
<br /></div>
Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com2tag:blogger.com,1999:blog-7849367040589270673.post-2182628535093537472019-11-21T09:25:00.002-08:002019-12-03T11:01:33.086-08:00Tips on Writing PL/SQL in APEX Apps<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqIYYpUAqTmJjamIYDyIZCQG89zsJlddWWO50TcP0HeSo8nRfB5-J33fiILLIoaw1XppWfYYRH97fhN8qZW4HLcpDEYnqqWxikdQXaQhMk6ypGonenW0vcHOfLdcXl4AZ01tb9McyyJq4/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="291" data-original-width="842" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqIYYpUAqTmJjamIYDyIZCQG89zsJlddWWO50TcP0HeSo8nRfB5-J33fiILLIoaw1XppWfYYRH97fhN8qZW4HLcpDEYnqqWxikdQXaQhMk6ypGonenW0vcHOfLdcXl4AZ01tb9McyyJq4/s1600/a.png" /></a></div>
On December 3 for our <a href="https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:RP,551:P551_CLASS_ID:6667" target="_blank">AskTOM Office Hours session</a>, three deeply experienced and highly respected APEX prosshared their wisdom on how best to write and manage PL/SQL code for <a href="http://apex.oracle.com/" target="_blank">Oracle Application Express</a> projects.<br />
<br />
You can watch the recording and follow links to related resources <a href="https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:RP,551:P551_CLASS_ID:6667" target="_blank">here</a>.<br />
<br />
Our experts were:<br />
<h2>
Karen Cannell</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNovAv3L03SVqEVdRXCYeL-4k2z9wREpA9Y9F8XwHz-Ah6xPPo6MI2R1LBIBvlxg6td3vf2QVKmn7yxppTBm0AIhnYF7AVkvvzpgRokJ3vpsRxToOe7XaQyUX0rure06zHglPUsjFk82A/s1600/a.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="180" data-original-width="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNovAv3L03SVqEVdRXCYeL-4k2z9wREpA9Y9F8XwHz-Ah6xPPo6MI2R1LBIBvlxg6td3vf2QVKmn7yxppTBm0AIhnYF7AVkvvzpgRokJ3vpsRxToOe7XaQyUX0rure06zHglPUsjFk82A/s1600/a.png" /></a></div>
<a href="http://twitter.com/thtechnology" target="_blank">Karen</a> promotes APEX topics and best practices through local, regional and national user group presentations and papers. A devoted user group volunteer since 2007, she is especially active in ODTUG, where she serves as the editor of the ODTUG Technical Journal. She is former Associate Editor of IOUG SELECT Magazine. She is co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4. Her most recent presentation at Kscope15 focused on APEX Interactive Reports with a Deep Dive: APEX 5 New Features and Upgrade Cheat Sheet.<br />
<br />
Karen has delivered Application Express solutions since its Web DB and HTMLDB beginnings, and continues to leverage the Oracle suite of tools to build quality web applications for clients in government, medical, and engineering industries. She is known for thinking outside the box to implement maintainable APEX solutions – most are still in production today (upgraded, of course!). . A mechanical engineer by degree (one of them), she has analyzed, designed, developed, converted, upgraded, enhanced, and otherwise improved legacy and commercial database applications for over 25 years, concentrating on Oracle technologies since 1994 and APEX since it was born. Karen is President of <a href="http://thtechnology.com/" target="_blank">TH Technology</a>, a small consulting firm providing Oracle technology services, lately focused on APEX, where she continues to deliver quality solutions for her clients.<br />
<h2>
<span style="font-size: large;">Scott Spendolini</span></h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhs7I78WbNSsRGQCdT6Arl9_7Db3wg5uT531C0CUjqUWxh5dJ5JANWZL-USW2A1DsXa1K21hf81V9YEsKscNNhYKTurs7N2TlnLxYLcdBmHzbHdLu-tYXTHEn9oHXEw-FGOwu-qyl9QvXY/s1600/a.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="177" data-original-width="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhs7I78WbNSsRGQCdT6Arl9_7Db3wg5uT531C0CUjqUWxh5dJ5JANWZL-USW2A1DsXa1K21hf81V9YEsKscNNhYKTurs7N2TlnLxYLcdBmHzbHdLu-tYXTHEn9oHXEw-FGOwu-qyl9QvXY/s1600/a.png" /></a></div>
Scott is Vice President of the APEX+ Practice of <a href="http://viscosityna.com/" target="_blank">Viscosity, NA</a>, where he manages a team of highly-skilled Oracle APEX developers. He is also responsible for developing and delivering Oracle APEX-related curriculum, as well as managing the development of several APEX-based products.<br />
<br />
Throughout his professional career, he has assisted various clients with their Oracle APEX development and training needs. Spendolini is a long-time and regular presenter at many Oracle-related conferences, including Oracle OpenWorld, Kscope, and RMOUG. He is a recipient of the Oracle Ace Director designation, author of Expert Oracle Application Express Security and co-author of Pro Oracle Application Express. Spendolini is also an Oracle Certified Oracle Application Express developer.<br />
<br />
Prior to reigniting and running Sumner Technologies from 2015 to 2018, Spendolini was an APEX Practice director at Accenture and Enkitec from June 2012 through April 2015. Before joining Enkitec as part of an acquisition, he co-founded and ran Sumneva and Sumner Technologies from 2005 through 2012, which focused on Oracle APEX services, education & solutions. Spendolini started his professional career at Oracle Corporation, where he worked with Oracle eBusiness Suite for almost seven years and was a Senior Product Manager for Oracle APEX for just over three years.
<br />
<h2>
Scott Wesley</h2>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs_NgAq0-b8pIu4eT2BiYr8Vod5IrNlYDe6NxzzuQQR9juoFHHFlQxe3hhdore10tXtmZU4SAFAZvrOFG5_TRw7j8RarBSE08jnF0RhujLYzd2QmEvDiQQynAZN0b6QhEjgEnzHQWrl4g/s1600/a.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="177" data-original-width="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgs_NgAq0-b8pIu4eT2BiYr8Vod5IrNlYDe6NxzzuQQR9juoFHHFlQxe3hhdore10tXtmZU4SAFAZvrOFG5_TRw7j8RarBSE08jnF0RhujLYzd2QmEvDiQQynAZN0b6QhEjgEnzHQWrl4g/s1600/a.png" /></a></div>
<br />
<a href="http://twitter.com/swesley_perth" target="_blank">Scott</a> clearly has a passion for Oracle APEX. In the time since he's left Forms behind, he's recorded a video series; written a book; trained developers around Australia; answered forum questions from around the world; and written a variety of posts at <a href="http://grassroots-oracle.com/">http://grassroots-oracle.com</a>.
<br />
<br />
Scott has been consulting with clients around Perth since 2000, enjoying his time with the Sage Computing crew for just over a decade. He presents regularly at Australian events, and was recognised as an Oracle ACE in 2014. and often weaves a side interest in science & astronomy into his work.<br />
<br />
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-49292695584112391572019-11-13T04:57:00.000-08:002019-11-13T06:05:23.705-08:00PL/SQL Puzzle: No extra code please!I published yet another PL/SQL puzzle <a href="https://twitter.com/sfonplsql/status/1194263037647212544" target="_blank">on Twitter</a> yesterday. Generated lots of interest and interesting replies. I don't think any single person caught <i>everything</i>, but as usual the community came through.<br />
<br />
I will repeat the puzzle here. If you haven't already seen it on Twitter, please try to solve it yourself before looking at my answer.<br />
<blockquote class="tr_bq">
What text can be removed from lines 3 though 12 in the code below so that after the anonymous block is executed, "121212" is still displayed on the screen?
</blockquote>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz45t-y4A3pFz0qY0PMeEmshGTuGxReL3s-Zjm3llxmQSkxWke7dwho41etjlEF00lQ0yWTE_QCcXtdq93qqAf_AbsmES99norupPLY1GOSgD6xq3QwP7G7ErTKqkos_xpqQc9H0zlKGA/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="613" data-original-width="668" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjz45t-y4A3pFz0qY0PMeEmshGTuGxReL3s-Zjm3llxmQSkxWke7dwho41etjlEF00lQ0yWTE_QCcXtdq93qqAf_AbsmES99norupPLY1GOSgD6xq3QwP7G7ErTKqkos_xpqQc9H0zlKGA/s1600/a.png" /></a></div>
<br />
<span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;"><b>White space</b></span><br />
<b><span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;">so you do not immediately</span></b><br />
<b><span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;">see my answers. </span></b><br />
<b><span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;"><br /></span>
<span style="font-size: x-large;">:-)</span></b><br />
<br />
OK, let's dive in.<br />
<br />
Notice, first of all, that I asked about <i>text</i> that can be removed, not lines. So you can remove entire lines or portions of lines. I refuse to accept that whitespace is text, so blank lines don't count. :-)<br />
<br />
Here are the opportunities for removal that I found:<br />
<br />
4 - Remove the IN keyword. That's the default for parameters (though I generally always include it in my code).<br />
<br />
5 - Remove AUTHID DEFINER. Again, that is the default (<a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-subprograms.html#GUID-41D23DE7-3C07-41CF-962B-F92B696594B5" target="_blank">definer's rights</a>) and it means that this procedure will compile with the directly-granted privileges of the schema (roles need not apply).<br />
<br />
7 - Remove entirely. The variable is not used. If it was, you could remove just ":= NULL" because by default variables are assigned the NULL not-value.<br />
<br />
9 - The value will be displayed through line 19, so it is not needed here.<br />
<br />
11 - A RETURN in a procedure? Huh? Yes, you can issue a RETURN in a procedure (and pipelined table function) that returns nothing but control. And it will do that. But as the last line in your procedure? No. That's what a procedure will do as a matter of course. Definitely not needed and <i>not</i> recommended.<br />
<br />
That leaves just:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE PROCEDURE my_proc (
arg1 NUMBER, arg2 OUT NUMBER)
IS
BEGIN
arg2 := arg1;
END;
</code></pre>
<br />
Now, if I had been a bit more relaxed in my puzzle rules and said "What text can be removed between lines 3 and 20?" you would probably would have had more fun - as you can see in the Twitter feed.<br />
<br />
Because then you could remove lines 10 and 20, just keep line 19 to display the value, and even remove the TO_CHAR explicit conversion, because that number will be implicitly converted to a string when it is passed too DBMS_OUTPUT.PUT_LINE (which does not have an overloading for numbers). You could then also remove the OUT parameter modifier, since the second parameter is no longer being assigned a value in the procedure!<br />
<br />
Did I miss anything? Let me know on the Twitter feed or in comments below. Have an idea for a puzzle of your own? I encourage you to throw it up on Twitter or you can also <a href="mailto:Steven.feuerstein@oracle.com" target="_blank">pass it along to me</a> and I will publish it (giving you credit of course).Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com0tag:blogger.com,1999:blog-7849367040589270673.post-65467436932868447932019-11-07T10:17:00.002-08:002019-11-07T16:07:01.739-08:00PL/SQL Puzzle: What code can be removed?I published a <a href="https://twitter.com/sfonplsql/status/1192164495654825984" target="_blank">PL/SQL puzzle on Twitter</a> on November 6 2019. I asked the following question:<br />
<blockquote class="tr_bq">
Which lines of code can be removed (either entirely or in part) from the block below and not affect the output of the program in any way?</blockquote>
I neglected to mention in my original tweet a few important assumptions:<br />
<ol>
<li>You are running this code on Oracle Database 10g or higher.</li>
<li>Server output is turned on.</li>
<li>Whitespace (spaces, tabs, new-lines) don't count.</li>
</ol>
Here's the code. I will publish it as an image, just as I did on Twitter, so that you can give it a go yourself, before taking a look at the answers from me and others below that.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgArlDo0u12OrLUUfr2M6TjNeQ7QbnDRPGSVV5EyfMIMfmpSSsXItigm9cxgwylY6BabM96Xy3F3mTJsjq3aYvnVza8m_5_e22j2wxzipvl4gzRb7wqUvXIfyBDGBqDSpV5yChmfUn3Pdg/s1600/plsql_quiz.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="869" data-original-width="905" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgArlDo0u12OrLUUfr2M6TjNeQ7QbnDRPGSVV5EyfMIMfmpSSsXItigm9cxgwylY6BabM96Xy3F3mTJsjq3aYvnVza8m_5_e22j2wxzipvl4gzRb7wqUvXIfyBDGBqDSpV5yChmfUn3Pdg/s1600/plsql_quiz.png" /></a></div>
Check out the Twitter conversation for all the answers that were submitted. It's a fun read!<br />
<br />
Here are the full lines that I believe can be removed:<br />
<br />
2 - There is not need to declare the iterator used in a FOR loop, numeric or cursor versions.<br />
<br />
7 - There is no need to declare an "empty" collection to be used to initialize l_objects.<br />
<br />
10 - Collections are empty after declaring, always. So no reason to delete.<br />
<br />
12 - 15 - Invoking the LAST method on an empty collection always returns NULL, so that call too DBMS_OUTPUT.PUT_LINE will never happen.<br />
<br />
17 - This line has no impact on the behavior of the program because SELECT-BULK COLLECT-INTO always wipes out whatever was in the target collection before filling it.<br />
<br />
28 - You don't need - and shouldn't use - an EXIT statement inside a FOR loop. It will automatically terminate when all the index values in the collection have been touched.<br />
<br />
And here are pieces of code that can be removed from remaining lines:<br />
<br />
5 - We do not have to declare this as an associative array. We can remove "INDEX BY PLS_INTEGER", which makes it a nested table. A SELECT-BULK COLLECT-INTO always initializes and extends nested tables and varrays<br />
<br />
8 - ":= l_empty" There is no need to initialize a collection with an empty one. It is automatically set to that state.<br />
<br />
In which case, the end result is nothing more than this:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>DECLARE
TYPE objects_t IS TABLE OF all_objects.object_name%TYPE;
l_objects objects_t;
BEGIN
SELECT object_name
BULK COLLECT INTO l_objects
FROM all_objects
WHERE object_name LIKE '%TABLE%'
ORDER BY object_name;
FOR indx IN 1 .. l_objects.COUNT
LOOP
DBMS_OUTPUT.put_line (l_objects (indx));
END LOOP;
END;
</code></pre>
You can run (and play around with) both versions on LiveSQL with <a href="https://livesql.oracle.com/apex/livesql/file/content_I7MK5SLZ8O4DZJPP2EXOFOKIW.html" target="_blank">this script</a>.<br />
<br />
Did I miss anything? Do you disagree with any of my removals?<br />
<br />
<br />
<br />
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com1tag:blogger.com,1999:blog-7849367040589270673.post-8527589217412594012019-10-25T08:02:00.000-07:002019-10-25T08:02:15.294-07:00PL/SQL Puzzle: Add one statement to stop exceptions<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEa4fuD6VZoMCzVlUa5nYqVQ90LulwdX53k1TaWXhacrF2j0e4EodzPp1eG9WymqDAzIFu4RuSUENk4N-mbqmYfbiK8-j4c88jc2_h4vTjRgrW5XXb6udvlXnGngQoJerYIQL8HKIzyDg/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="611" data-original-width="922" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEa4fuD6VZoMCzVlUa5nYqVQ90LulwdX53k1TaWXhacrF2j0e4EodzPp1eG9WymqDAzIFu4RuSUENk4N-mbqmYfbiK8-j4c88jc2_h4vTjRgrW5XXb6udvlXnGngQoJerYIQL8HKIzyDg/s1600/a.png" /></a></div>
OK, folks, here's a PL/SQL puzzle. It was originally posted on Twitter. I give you the link to that thread at the end of this post. But first....don't you want to try to solve the puzzle yourself? :-)<br />
<br />
Please note that the solutions to the puzzle have absolutely <b>NOTHING</b> to do with writing good code. They are simply exercising various features of the PL/SQL language.<br />
<h2>
The puzzle</h2>
Can you come up with just <b>ONE STATEMENT</b> to add to plsqlpuzzle_proc so that it can execute without terminating with an unhandled exception? Use this <a href="https://livesql.oracle.com/apex/livesql/s/i49rcjpg292gyg2fgtlvv0mdx" target="_blank">LiveSQL script</a> as a starting point for your attempted solutions.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE TABLE plsqlpuzzle (n NUMBER)
/
CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
ORA-01403: no data found
</code></pre>
I offer below the solutions that I and others came up with. But first a nice big chunk of white space so you do not <i>see</i> those solutions immediately.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4VXybc6gpj4UxcYNiqkvwOKMMy5ljZCjYKqIQFNfn5O97Wzu5YH5TLaRy6G5dnOoaa04GruGNiBI9Cs1xYU-K0n7eAjrV1XxZcMXXgt82n6gwaVmxaVMpaCzm1Vk2gfj-hfGPZENHdNk/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="592" data-original-width="840" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4VXybc6gpj4UxcYNiqkvwOKMMy5ljZCjYKqIQFNfn5O97Wzu5YH5TLaRy6G5dnOoaa04GruGNiBI9Cs1xYU-K0n7eAjrV1XxZcMXXgt82n6gwaVmxaVMpaCzm1Vk2gfj-hfGPZENHdNk/s1600/a.png" /></a></div>
<br />
So. You looked at the code and you came up with some ideas? I hope so!<br />
<br />
If by chance you have a solution that we do <i>not</i> show below, please add it via comment. I will move it to the post, give you credit and you will be <b><span style="color: red;">FAMOUS</span></b>. :-)<br />
<h2>
Why not just insert a row?</h2>
As you likely know, a SELECT-INTO (implicit single-row query) raises the NO_DATA_FOUND exception if no rows are identified. Since the plsqlpuzzle table is empty, an immediate thought is to use that one statement to add a row to the table. Let's try that.<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 653px;"><code>CREATE TABLE plsqlpuzzle (n NUMBER)
/
CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
INSERT INTO plsqlpuzzle VALUES (100);
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
ORA-01403: no data found
</code></pre>
Huh? <i>Still</i> getting a NO_DATA_FOUND exception? But...but...the table has a row. Yes, it does, but the NO_DATA_FOUND exception is also raised when I try to "read" a "row" in a collection (look at an element in a collection at a specific index value). Well, a collection <i>is</i> kind of like a table, right?<br />
<br />
So while the one row in the table fixes the problem with NO_DATA_FOUND from SELECT-INTO, it does not stop the exception from being raised from this line:<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 653px;"><code>DBMS_OUTPUT.put_line (t (1).n);
</code></pre>
<h2>
RETURNING to the scene of the crime</h2>
Sorry, there's no crime. Just needed a catchy title for the header. :-)<br />
<br />
So a couple of clever developers (see the Twitter thread link at bottom of post) came up with a modification (or two) to the INSERT statement to get around this problem. Namely: RETURNING.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
INSERT INTO plsqlpuzzle VALUES (100)
RETURNING n BULK COLLECT INTO t;
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
100
100
DELETE FROM plsqlpuzzle
/
CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
INSERT INTO plsqlpuzzle VALUES (100)
RETURNING n INTO t (1).n;
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
100
100
</code></pre>
In the first version, RETURNING BULK COLLECT INTO populates the collection with all the rows inserted (just happens to be one). In the second iteration, the non-bulk RETURNING-INTO populates a specific element in the collection at index value 1.<br />
<br />
Either way, the code executed to display the value of t(1).n no longer throws a NO_DATA_FOUND exception and so they are both excellent solutions.<br />
<br />
The RETURNING clause is a really lovely reminder of the tight integration between SQL and PL/SQL. Read lots more about it <a href="https://stevenfeuersteinonplsql.blogspot.com/2019/04/use-returning-clause-to-avoid.html" target="_blank">here</a>.<br />
<h2>
Another Sort of Return</h2>
That use of RETURNING was very clever - I hadn't thought of it when I published the puzzle!<br />
<br />
There is another way to achieve the desired effect (add just one statement and no unhandled exception): insert a RETURN statement.<br />
<br />
Now, you might be saying: a RETURN statement? But this is a procedure, not a function! You would be absolutely right to say so. But did you know that you can also execute a RETURN inside a procedure? You just don't return anything but control to the outer block or host environment!<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
DBMS_OUTPUT.put_line ('Running plsqlpuzzle_proc');
RETURN;
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
Running plsqlpuzzle_proc
</code></pre>
<h2>
Two Other "Solutions"</h2>
I put that word in quotes because sell they are maybe valid solutions, maybe not, but worth mentioning.<br />
<br />
One person proposed adding a one line exception section as you see below.
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
DBMS_OUTPUT.put_line ('Running plsqlpuzzle_proc');
RETURN;
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
plsqlpuzzle_proc;
END;
/
Running plsqlpuzzle_proc
</code></pre>
That avoids there procedure terminating with an unhandled exception. But as you will see on Twitter, I had to reject the solution. That's because while this is a single statement:<br />
<br />
EXCEPTION WHEN OTHERS THEN is not a statement in PL/SQL. They are reserved words or key words that define the exception section and the WHEN clause.<br />
<br />
Otherwise, a fine idea. Except of course this exception section <a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=2ahUKEwii66X-yrflAhXSqlkKHdq9AR8QwqsBMAB6BAgJEAQ&url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DDw0qRw8P0cQ&usg=AOvVaw3CQZfImmZ9MSyfP2KsEuCE" target="_blank">should never be used</a> in production code.<br />
<br />
Finally, Philipp Salvisberg surprised absolutely no one by coming up with something else entirely - and testing the boundary edges of the quiz. Here's his code:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc
IS
r plsqlpuzzle%ROWTYPE;
TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE
INDEX BY PLS_INTEGER;
t r_t;
BEGIN
<<burn_cpu>> goto burn_cpu;
SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (t (1).n);
END;
/
</code></pre>
Notice the use of GOTO. He is essentially setting up a very tight infinite loop.<br />
<br />
When you run this on LiveSQL, it will eventually terminate with the following error:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code>ORA-00040: active time limit exceeded - call aborted
</code></pre>
When you run it in your own database, it will likely run for a much longer time. You will <i>eventually </i> see that error or (more likely) you will terminate the session. :-)<br />
<br />
Does this satisfy the conditions of the quiz? Weeelllll.....it doesn't terminate with an unhandled exception (a PL/SQL exception). It will eventually fail with an Oracle Database error. I will leave it to you to decide.<br />
<br />
But at least now you know that there is a <a href="https://stevenfeuersteinonplsql.blogspot.com/2017/09/surgical-strike-on-spaghetti-code-with.html" target="_blank">GOTO in PL/SQL</a> and as with every other language out there, you should avoid it whenever possible.<br />
<br />
You can run all of the above code in LiveSQL by running this script. Feel free, of course, to download and run it in your own database as well.<br />
<h2>
The Twitter Thread</h2>
This puzzle was originally posted on Twitter. <a href="https://twitter.com/sfonplsql/status/1187396677629558785" target="_blank">Here</a> is the thread, discussion and solutions as they appeared.Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com1tag:blogger.com,1999:blog-7849367040589270673.post-24942038079736503052019-10-23T12:14:00.005-07:002019-10-24T06:00:30.019-07:00Why isn't my exception section catching my error?<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuSvlqql83AjTu205QHRdtNv-hnIrYczTKkYfVwWNzuh4AHHn8c-fzobPWZEmat8HL19U2iaY1X6Wpqk_-KhIbgpMCJuPITvtRK_JMZv_3QIq9gpIRn5ukZpUTLXbCDjdMktvaIIHIER0/s1600/a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="182" data-original-width="554" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuSvlqql83AjTu205QHRdtNv-hnIrYczTKkYfVwWNzuh4AHHn8c-fzobPWZEmat8HL19U2iaY1X6Wpqk_-KhIbgpMCJuPITvtRK_JMZv_3QIq9gpIRn5ukZpUTLXbCDjdMktvaIIHIER0/s1600/a.png" /></a></div>
I got an interesting email today from a reader of one of my PL/SQL 101" articles for Oracle Magazine, <a href="https://blogs.oracle.com/oraclemagazine/building-with-blocks" target="_blank">Building with Blocks</a>.<br />
<br />
Q. had taken the code from the article, made some changes, tried to run them, and got very confused. He wrote:<br />
<br />
<span style="color: blue;">When I run this code, I see "Hello World".</span><br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code><span style="color: blue;">DECLARE
l_message VARCHAR2(100) := 'Hello World!';
BEGIN
DBMS_OUTPUT.put_line (l_message);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error='||SQLERRM);
END;
/
Hello World!
</span></code></pre>
<span style="color: blue;">When I change the block to make the l_message variable too small for its string, I see the VALUE_ERROR error message.</span><br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code><span style="color: blue;">DECLARE
l_message VARCHAR2(10);
BEGIN
l_message := 'Hello World!';
DBMS_OUTPUT.put_line (l_message);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error='||SQLERRM);
END;
/
Error=ORA-06502: PL/SQL: numeric or value error: character string buffer too small
</span></code></pre>
<span style="color: blue;">But when I change the name of the variables inside the call to DBMS_OUTPUT.PUT_LINE to "l_message1", the exception handler is not displaying the error message, instead I see the following:</span><br />
<span style="color: blue;">When I change the block to make the l_message variable too small for its string, I see the VALUE_ERROR error message.</span><br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 16px; line-height: 16px; overflow: auto; padding: 5px; width: 100%;"><code><span style="color: blue;">DECLARE
l_message VARCHAR2(10) := 'Hello World!';
BEGIN
DBMS_OUTPUT.put_line (l_message1);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error='||SQLERRM);
END;
/
ORA-06550: line 5, column 25:
PLS-00201: identifier 'L_MESSAGE1' must be declared </span><span style="color: blue; font-family: -webkit-standard;"><span style="caret-color: rgb(0, 0, 255); white-space: normal;">
</span></span></code></pre>
<span style="color: blue;">Why am I seeing this inconsistent behavior? </span><br />
<br />
I was confused about the source of his confusion, but after a couple of back-and-forth emails (what you read above is the <i>cleaned-up</i> version of that back-and-forth), the light bulb lit in my brain. Which enabled me to clear up his confusion, and inspire me to write a blog post, in case the same confusion was confusing anyone else.<br />
<br />
Here's the most important thing to remember:<br />
<blockquote class="tr_bq">
Exception sections handle exceptions raised when the block is executed. But first that block must be compiled!</blockquote>
When you are writing code within stored program units, like procedures and functions, this is clearly a two stage process:<br />
<ol>
<li>Compile</li>
<li>Execute</li>
</ol>
But when you are working with an anonymous block, all you do is execute the block. So you can certainly be forgiven for thinking that anything that goes wrong will be caught in the exception handler.<br />
<br />
The reality is different however. When you run an anonymous block, the PL/SQL engine will first of all parse and compile the block. If all goes well, <i>then</i> the PL/SQL runtime engine will execute the compiled code.<br />
<br />
If, however, your code fails to compile, well....you will get a compile <i>error</i>, rather than an runtime <i>exception</i>.<br />
<br />
How do you know you've gotten a compile error? Check the prefix. If it is "PLS", something went kablooey at compile time. Your code cannot be executed. If the prefix is "PLW", that's a compile-time <i>warning</i>, which means that the code compiled, but the PL/SQL engine has suggestions for improving it. And if the prefix is "ORA", ah, then you've got a runtime error - an exception.<br />
<br />
Only in that last case with the exception handler, if present, be able to trap the exception.<br />
<br />
I hope that makes thing nice and clear. Any comments or questions? :-)<br />
<br />
<br />Steven Feuersteinhttp://www.blogger.com/profile/18405765731886460622noreply@blogger.com4