Here’s a fun little question for those of you who aren’t experts at plsql…<\/p>\n
Given this plsql code<\/p>\n
procedure helper_method(\r\n iChange IN boolean,\r\n oValue OUT number\r\n) IS\r\nBEGIN\r\n IF iChange THEN\r\n oValue := 2;\r\n END IF;\r\nEND helper_method;\r\n\r\nprocedure do_test(\r\n oValue OUT number\r\n) IS\r\nBEGIN\r\n oValue := 1;\r\n --Checkpoint 0\r\n helper_method(true, oValue);\r\n --Checkpoint 1\r\n helper_method(false, oValue);\r\n --Checkpoint 2\r\nEND do_test;<\/pre>\nand this java code (assuming the right frameworks for calling into plsql)<\/p>\n
public void doTest() {\r\n IntOutParam out = new IntOutParam();\r\n sqlCall(\"do_test\", out);\r\n System.out.println(out.getInt());\r\n}<\/pre>\nwhat is printed out when you replace the checkpoint comments with a RETURN;<\/tt> statement?
\nThe answer surprised me… I actually stumbled across this at work today and it took me a while to figure out.<\/p>\nReplacing checkpoint 0 with RETURN;<\/tt> prints out 1, as expected.
\nReplacing checkpoint 1 with RETURN;<\/tt> prints out 2, as expected.
\nReplacing checkpoint 2 with RETURN;<\/tt> prints out 0. Huh?<\/p>\nCan you spot why and what the fix should be?
\nHint: Swapping the order of the true<\/tt>\/false<\/tt> calls would result in outputs of 1, 0, and 2 respectively.
\nHuge Hint: At checkpoint 2, the value of oValue<\/tt> is actually null<\/tt>.
\nSee comments for the solution.<\/p>\nSo yeah, now you know something to watch out for when programming in plsql :)<\/p>\n","protected":false},"excerpt":{"rendered":"
Here’s a fun little question for those of you who aren’t experts at plsql… Given this plsql code procedure helper_method( iChange IN boolean, oValue OUT number ) IS BEGIN IF iChange THEN oValue := 2; END IF; END helper_method; procedure … Continue reading