view counter
Home

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Is "INTEGER" a reserved word? 30 December quiz (1824)

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:

Jan 03, 2011 4:39am

Handling Bash Parameters

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 [...]

Jan 01, 2011 11:40pm

Qualified identifiers and error messages - 29 December quiz (1823)

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:

Dec 30, 2010 10:58am

"Bounds" for associative arrays - correcting an ambiguity - 28 December (239)

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'"

Dec 29, 2010 4:34am

One really great thing about 2010

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!"

Dec 26, 2010 10:36am

So which error is raised in 24 December quiz? (1805)

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.

Dec 25, 2010 11:21am

Should you expose a Stored Procedure via XMLType?

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
is
   function get_department_xml (p_department_no in number)
      return xmltype;

   function get_departement_ot (p_department_no in number)
      return all_departments_ot;
  
end emps_pkg;

Dec 23, 2010 5:15am

Creating an XMLType based on Object Types

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

Dec 23, 2010 4:44am

PHP leveraging PL/SQL

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 [...]

Dec 22, 2010 9:53pm

Is a CLOB a string? A question raised about the 22 December quiz (1803)

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:

"You cannot execute a string of more than 32,767 bytes as a dynamic SQL statement."

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:

Dec 22, 2010 6:44pm
view counter