view counter
Home

PL/SQL Feed

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

Seeing results from running SQL query (1926)

In the 21 January quiz, I asked you to select the choices that would display "Ellison". Two of the choices were PL/SQL blocks containing calls to DBMS_OUTPUT.PUT_LINE. Two were queries. One block and one query were scored as correct. I received the following objections.

1. "There were () at the end of function invocation. PL/SQL syntax would not allow that."

Jan 22, 2011 6:08am

Cursor FOR loop, automatic optimization and use of BULK COLLECT (1924)

In the 19 January 2011 quiz, you were asked to choose the block containing the most efficient looping through employee data. It was:

BEGIN
   FOR emp_rec IN (SELECT last_name FROM plch_employees)
   LOOP
      DBMS_OUTPUT.put_line (emp_rec.last_name);
   END LOOP;
END;
/

The primary lesson of the quiz is that in Oracle Database 10g and higher, with optimization set to at least level 2 (the default), the compiler optimizes cursor FOR loops so that execute at BULK COLLECT-like levels of performance.

Jan 20, 2011 5:35am

Prepared Statement Failure

One of my students asked for some help on developing a MySQL stored procedure that would conditionally drop a foreign key constraint. I put together a quick example, which compiled fine but failed at run time. With some investigation it appears that either the MySQL 5.5 Documentation in Section 12.6 is incorrect or you can’t [...]

Jan 18, 2011 11:39pm

Nuances of Nested Tables (1864)

The 13 January quiz tested your knowledge of nested tables, one of the three types of collections available in PL/SQL (two of which, nested tables and varrays, can be manipulated within SQL).

Jan 13, 2011 4:33pm

Constant Instead of Literal a Best Practice? (1862)

In the 11 January quiz, we asked:

Which of the following statements correctly describe a way to improve the performance, readability or maintainability of this block of code?

BEGIN
   FOR month_index IN 1 .. 12
   LOOP
      UPDATE monthly_sales
         SET pct_of_sales = 100
       WHERE company_id = 10006 
         AND month_number = month_index;
   END LOOP;
END;

We scored as correct the following choice:

"Replace all hard-coded literal values with named constants or function calls."

Jan 13, 2011 2:25am

Really BIG discount on O'Reilly ebooks including mine - TODAY ONLY

Just got this note from O'Reilly and thought I would pass it along:


Save 60% Ebook & Video Deal of the Day
Top 10 Database/SQL Titles

Jan 11, 2011 2:25am

Proposed Assumptions for National Language Settings (NLS)

Some players in the past have expressed concerns about the fact that the daily quiz assumptions do not currently specify English as the language. I have worked with Michael Brunstedt (well, to be honest, he did all the work), one of the quiz reviewers, to come up with the following text for assumptions regarding national language settings. I'd like to get your feedback before putting them into effect.

Tanks in advance for your comments and advice.

SF

Proposed Assumptions

Jan 08, 2011 2:40am

Exploring nuances of 11g fine-grained dependencies (1841)

The 5 January quiz tested your knowledge or (or, more likely, introduced you to) the Oracle11g fine-grained dependency feature, which maintains dependency information down to columns within tables and parameters within subprograms.

The quiz demonstrates that if I add a column to a query that is executed in a stored program unit, then that the program unit is not invalidated, since that unit couldn't possibly have depended on the new, previously non-existent, column.

Jan 07, 2011 3:58am

Many more reasons to play the PL/SQL Challenge in January!

We hope that you had a fine new year's celebration (assuming, that is, that you celebrate the new year on 1 January), took some time away from PL/SQL and the rest of your professional responsibilities, and re-charged those batteries.

Jan 06, 2011 7:10am

Q4 2010 Playoff Participants

A new year, a new quarter - and that means it's time for championship playoff for the previous quarter, Q4 of 2010. So a very special "Happy New Year!" and congratulations to the following 45 PL/SQL Challenge players, who will soon be competing for top ranking and prizes.

The first 25 names listed qualified for participation through their ranking. The twenty players after that qualified either as a wildcard entry or through the "correctness" raffle. Check out the FAQ for details.

Dennis Klemme (Germany) ranked 1 with 66 quizzes in 3942 seconds 96.6% correct

Jan 04, 2011 3:43pm
view counter