In the 30 December 2010 quiz on valid identifiers, we scored the following choice as correct:
DECLARE INTEGER VARCHAR2 (100) := 'Everything''s Fine!'; BEGIN DBMS_OUTPUT.put_line (INTEGER); END;
In our explanation, we wrote: "INTEGER" is a valid PL/SQL identifier and it is not a reserved word, so you can use it as the name of your variable. It is a datatype declared in the STANDARD package, one of the default packages of PL/SQL. You can, therefore, use that same identifier in your own code.
One player wrote the following:
Bash shell or shells in general hang on details. An ex-student was trying to sort something out in Learning the bash Shell, 3rd Edition, which isn’t on my short list of good shell scripting books. I concur more or less with the comment on Amazon.com that there are too few examples in the book. I [...]
The 29 December quiz tested your knowledge of how you can qualify the names of PL/SQL elements with their scope name (procedure, function, block).
Iudith wrote the following commentary regarding the kinds of errors that are raised across different versions of Oracle:
Regarding the Quiz of 29-dec, Choice 2:
The 28 December quiz on associative arrays scored the following as incorrect:
There are no upper or lower bounds on the integer values you can use as index values.
Several players wrote to complain about this scoring, from two angles:
1. "If the array is indexed by binary_integer then there is upper and lower bounds. (-2147483647 .. +2147483647) However if the table is indexed by varchar2, then there are no bounds on the 'integer values'"
It's easy to focus on the bad stuff. My father and sister died in January. Deepwater Horizon disaster. Utter failure of "Middle East Peace Process." Republicans capture control of U.S. House of Representatives. Ongoing financial crisis that sucks money from poor and middle class people and deposits these funds into the pockets of the ultra-rich. And so on....
Yet here's one really excellent development that I noticed in 2010:
Food product manufacturers are now promoting (some of) their products as follows:
"Contains no high fructose corn syrup!"
The 24 December quiz tested your knowledge of what happens when a CASE statement does not contain an ELSE clause and at runtime, none of the WHEN clauses are executed. The answer is that Oracle raises the "ORA-06592: CASE not found while executing CASE statement" error.
Several players wrote, however, to note that the local variable, l_text, is declared as VARCHAR2(20). So this assignment:
the_text := 'Santa is coming (for some, sort of, maybe)';
will raise a VALUE_ERROR exception. In other words, they chose the correct answer, but for the wrong reasons.
When you want to expose your Stored Procedure to the "outside world", should you do this using XMLType? As always, the correct answer is "it depends".
For this example I will use a Package containing two functions, the signature of the package is as follows:
create or replace package emps_pkg
function get_department_xml (p_department_no in number)
function get_departement_ot (p_department_no in number)
Sometimes it is necessary to create a Stored Procedure which returns an XMLType, like when you want to expose the Stored Procedure to the "outside world", like via a Mediator. There are several options to create an XMLType. In this blogpost I will show you two ways of doing this. First the "regular" way using XMLElement, XMLForest and XMLAgg. Second using Object Types which are being converted to XMLType.
For this example I will use the HR schema, and in it are the DEPARTMENTS and EMPLOYEES tables, which has a Master-Detail relation between them. The DEPARTMENTS table
Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type. Here’s the schema-level PL/SQL [...]
The 22 December quiz tested your knowledge of the capabilities of both EXECUTE IMMEDIATE and DBMS_SQL to parse very long strings. We scored the following statement as incorrect:
Oracle offers various mechanisms, especially in Oracle11g to bypass this limitation (the maximum size, that is, of a VARCHAR2 variable or literal).
One player emailed the following concern: