Loading…
PL/SQL, packages, cursors, optimizer hints and Oracle-specific SQL.
You keep seeing the word "cursor" in PL/SQL code and in Oracle error messages. In plain words, what is a cursor and what is it for?
What is the difference between an implicit cursor and an explicit cursor in PL/SQL? Give an example of code that uses each.
A colleague writes this:
FOR r IN (SELECT id, name FROM customers WHERE country = 'NL') LOOP
In this loop, what do %NOTFOUND, %FOUND, %ROWCOUNT and %ISOPEN
tell you about c_emp, and what value would c_emp%ROWCOUNT have
just before CLOSE if the query returned 7 rows?
Your procedure runs UPDATE customers SET email = p_email WHERE id = p_id. If the id does not exist you want to raise an application error.
How do you detect that no row was updated, and why won't Oracle raise
an exception for you?
Your package needs to loop over orders in status 'NEW' in one place and in status 'FAILED' in another, with the same query otherwise. What is a parameterised cursor, and how would it help here?