Skip to main content

Posts

Showing posts from January, 2020

PL/SQL Puzzle: Getting the "right" error message to appear

I posted the following puzzle on Twitter : What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) Here are the answers from the TwitterSphere: Change line 5's assignment to dbms_sql.number_table(1=>1,2=>1) 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. So that will do it, right? Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution: The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is unsigned . 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

Oracle Dev Gym PL/SQL Championship for 2019 Players

Throughout 2019, over 1200 Oracle Database developers participated in the Oracle Dev Gym PL/SQL Challenge weekly tournament. The top 50 ranked players shown below will compete for top honors in a championship on February 18, 2020. 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! Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! Name Rank Stelios Vlasopoulos (16) 1 mentzel.iudith (19) 2 NielsHecker (20) 3 Chad Lee (16) 4 Peterman (6) 5 siimkask (19) 6 _tiki_4_ (12) 7 Chase Mei (5) 8 Ludovic Szewczyk (4) 9 li_bao (7) 10 MarkusId (0) 11 Andrey Zaytsev (8) 12 Michal P. (3) 13 Arjun Barath (0) 14 Ivan Blanarik (13)