One of my current goals is to re-read the literature I have on PL/SQL and SQL. I'm going back to the documentation and my PL/SQL books to give myself a bit of a refresher course. One of the things I came across today is something I believe is not very well known.
The built in RAISE_APPLICATION_ERROR procedure has a little-known third parameter.
RAISE_APPLICATION_ERROR (
num IN BINARY_INTEGER,
msg IN VARCHAR2,
keeperrorstack IN BOOLEAN);
The documentation states that for the keeperrorstack parameter "If you specify TRUE, PL/SQL puts error_code on top of the error stack. Otherwise, PL/SQL replaces the error stack with error_code."
Sunday, January 22, 2012
Sunday, January 15, 2012
Procedure Dependencies in a Package
I was reviewing some PL/SQL development work recently and I noticed that a developer had declared some procedures in a package specification that should not have been public. When I asked why this was done the reply was that the procedures had dependencies on one another and the package body would not compile otherwise.
Here's a quick demonstration of the issue the developer was encountering.
Here's a quick demonstration of the issue the developer was encountering.
Saturday, January 7, 2012
The Greatest Word In the English Language
The most amazing word in the English language is "Why". Why do I say that? Because it forces someone to back up their claims with evidence.
Do you see what I did there? ;)
Saturday, December 31, 2011
I See Data, People
I've been working with Databases for many years now. As a result, I'm conditioned to see data everywhere I look and I always wonder how that data is stored and how it came to be presented in the format in which I saw it. Here's an example.
I like golf. Actually, I love golf. No...I'm completely obsessed with golf. I follow all of the tours, players, and news. I'll wake up at 3 o'clock in the morning on weekends to watch The Open Championship being played in Great Britain in July. It's OK, I don't expect you to understand.
As with any sport, in golf there is no end of statistics, scores, and mounds upon mounds of data to read. There is a specific type of scoring in golf called Match Play. The gist of the system is that two players playing against each other on the same course will record their score for each hole. The player with the lower (better) score on a hole will go "one up" and the player with the higher (worse) score will go "1 down". As play progresses the "up" and "down" scoring is cumulative. Once a player is "up" more points than there are holes remaining, that player has won the match as their opponent would be unable to win enough remaining holes to close the gap. If you'd like to read more about match play scoring you can do so here.
If you imagine how a golfer's scores would be recorded in a database table, a very simplified approach would be a table that looked like this.
PLAYER_SCORECARD
and so on.
Typically, when the scores for a Match Play type match are displayed for two opponents, it is done in a "horizontal" manner.
I like golf. Actually, I love golf. No...I'm completely obsessed with golf. I follow all of the tours, players, and news. I'll wake up at 3 o'clock in the morning on weekends to watch The Open Championship being played in Great Britain in July. It's OK, I don't expect you to understand.
As with any sport, in golf there is no end of statistics, scores, and mounds upon mounds of data to read. There is a specific type of scoring in golf called Match Play. The gist of the system is that two players playing against each other on the same course will record their score for each hole. The player with the lower (better) score on a hole will go "one up" and the player with the higher (worse) score will go "1 down". As play progresses the "up" and "down" scoring is cumulative. Once a player is "up" more points than there are holes remaining, that player has won the match as their opponent would be unable to win enough remaining holes to close the gap. If you'd like to read more about match play scoring you can do so here.
If you imagine how a golfer's scores would be recorded in a database table, a very simplified approach would be a table that looked like this.
PLAYER_SCORECARD
| Player | Hole | Score |
| Bob | 1 | 4 |
| Bob | 2 | 4 |
| Bob | 3 | 3 |
| Bob | 4 | 3 |
| Bob | 5 | 5 |
| Bob | 6 | 5 |
| Bob | 7 | 6 |
| Bob | 8 | 3 |
| Bob | 9 | 4 |
| ... |
and so on.
Typically, when the scores for a Match Play type match are displayed for two opponents, it is done in a "horizontal" manner.
| Hole | 1 | 2 | 3 | ... |
| 1 up | 2 up | 1 up | ... | |
| Bob | 3 | 2 | 4 | ... |
| Joe | 4 | 3 | 3 | ... |
| 1 dn | 2 dn | 1 dn | ... |
Since the scores for players are not stored horizontally in our PLAYER_SCORECARD table this is a great example of how the PIVOT function introduced in 11g can be very helpful.
Wednesday, December 14, 2011
I learned something new today (I think)
I'm not sure if I had not seen this before, or if I had and simply forgotten about it.
You can use the assignment statement ":=" to designate a parameter as having a default value as well as using the DEFAULT keyword!
For the sake of semantic clarity I much prefer the DEFAULT keyword. The extra keystrokes are worth it, in my opinion.
You can use the assignment statement ":=" to designate a parameter as having a default value as well as using the DEFAULT keyword!
DECLARE
PROCEDURE p_default_param1(in_param IN VARCHAR2 := 'Default')
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('in_param = '||in_param);
END p_default_param1;
PROCEDURE p_default_param2(in_param IN VARCHAR2 DEFAULT 'Default')
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('in_param = '||in_param);
END p_default_param2;
BEGIN
p_default_param1;
p_default_param1('A');
p_default_param2;
p_default_param2('B');
END;
/
in_param = Default
in_param = A
in_param = Default
in_param = B
PL/SQL procedure successfully completed.
For the sake of semantic clarity I much prefer the DEFAULT keyword. The extra keystrokes are worth it, in my opinion.
Monday, December 12, 2011
NVL() does not short circuit
Here's an interesting tidbit about NVL(expr1,expr2). The documentation states that " If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1." What you may not know is that both expr1 and expr2 are evaluated even if expr1 is not null. NVL does not short circuit. Fortunately alternative methods like COALESCE and the CASE statement will.
DECLARE
str VARCHAR2(1);
FUNCTION dummy_function RETURN VARCHAR2
IS
BEGIN
DBMS_OUTPUT.PUT('(Function ran)');
RETURN 'X';
END dummy_function;
BEGIN
str := 'A';
DBMS_OUTPUT.PUT_LINE('NVL: '||NVL(str,dummy_function));
DBMS_OUTPUT.PUT_LINE('COALESCE: '||COALESCE(str,dummy_function));
DBMS_OUTPUT.PUT_LINE('CASE: '||CASE WHEN str IS NOT NULL THEN str ELSE dummy_function END);
END;
/
(Function ran)NVL: A
COALESCE: A
CASE: A
PL/SQL procedure successfully completed.
This is definitely something to keep in mind if, like in the example above, you are calling a function for expr2. If that function does a significant amount of processing and/or the NVL() is inside of a loop with many iterations, the cycles it takes to execute the function every time could add up.
Saturday, December 10, 2011
Implicit Cursors are Wonderful
I was sent this script for review. Can you see what's wrong with this picture?
DECLARE
l_limit CONSTANT PLS_INTEGER := 1000;
TYPE varchar2_tab_typ IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
varchar2_tab varchar2_tab_typ;
CURSOR grants_cur IS
SELECT 'GRANT SELECT ON '||table_name||' TO some_schema'
FROM user_tables;
BEGIN
varchar2_tab.DELETE;
OPEN grants_cur;
LOOP
FETCH grants_cur BULK COLLECT INTO varchar2_tab LIMIT l_limit;
EXIT WHEN grants_cur%NOTFOUND;
END LOOP;
CLOSE grants_cur;
FOR i IN 1..varchar2_tab.COUNT LOOP
EXECUTE IMMEDIATE varchar2_tab(i);
END LOOP;
END;
/
Subscribe to:
Posts (Atom)