3

I got a stack trace from Oracle saying there was a problem on Line 299. No problem, I looked at that line in the package in question, and say that it's a line in an initialization block that sets a variable to 1, and does not call the procedure that raised the error.

It looks like there's a mismatch between what the server is executing and what I am seeing when I view the package source, based on the line number in the stack trace.

How I looked at the package: I used PL/SQL Developer and clicked "View Pacakge" on the package name in the object browser. When I looked at the same package in source control, an error on Line 299 makes sense (it's a call to the procedure that failed).

So my question is: WTF is going on? Why do I get source code that doesn't seem to match the stack trace line numbers when I ask Oracle for the pacakge source?

(oracle 10g)

FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
  • @Mike Atlas: This is the first time it's happened to me and it's making me (and my fellow devs and DBA) question my sanity. I imagine there may be much face-palming when a solution is finally revealed! – FrustratedWithFormsDesigner Jan 11 '11 at 16:39

2 Answers2

1

I think you already got the source of that package opened in PL/SQL developer. When you choose View Source again, it activates the previously opened editor, without reloading the source.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • Hmm I closed all editors for that package (there was only one, which was opened the first time I tried to View Package). The I right-clicked the package in the Object Browse, and chose "Refresh". Then I right-clicked and chose View. Then I see the old code again! I wouldn't be surprised *at all* if it was a PL/SQL Developer quirk rather than Oracle, but I can't tell at this point... – FrustratedWithFormsDesigner Jan 11 '11 at 16:21
1

So it turns out that my script was referencing API_USR.SOME_PACKAGE and I was logged in as READONLY_USR and READONLY_USR owns an older version of SOME_PACKAGE. When I viewed API_USR.SOME_PACKAGE via READONLY_USR I got the right source code.

face-palm

FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
  • I usually get the wrong line number when using Oracle SQL Developer on simple queries (10-15 lines long) so at least you're past your problem now. – Mike Atlas Jan 11 '11 at 16:57
  • 1
    @Mike Atlas: I think in Oracle SQL Developer (and also probably in Allround Automation's PL/SQL Developer which is what I'm currently using) the line number returned is *relative to the first line of the query*. so that if you have 3 queries on the worksheet it may show Query3 begining on line 30 of the text file, but when you execute *only* Query3, Oracle sees it as starting at line 1 (because it doesn't know the query's position in the file). – FrustratedWithFormsDesigner Jan 11 '11 at 17:25