SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Debugging Oracle PL / SQL Stored Procedures

    Wondering if anyone knows of an Oracle PL / SQL debugger? Guy I work with is writing stored procedures for use with DB triggers and if I hear him moan again...

    Many thanks

  2. #2
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't speak for Oracle but in Postgres i use RAISE NOTICE errors to find the line the error is on... and run the funcitons manually - (if it's returning opaque you'll have to change it to returning int - perhaps rename it to function_debug (bla, bla) RETURNS int ...

    Does this help?

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  3. #3
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Australia
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use TOAD to write PL/SQL procedures, functions and packages.

    There is a cost involved with getting TOAD. But you can also do DB queries, view tables, DB triggers, indexes, views, session info and a whole lot more

    http://www.quest.com/toad/
    http://www.quest.com/toad/why_toad_rocks.asp

    wabirdman
    signature

  4. #4
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're using Visual Studio (version 6, don't know about .Net), I've heard you can use the C++ debugger to debug SPs when you access them through the data environment in VS. I've used it to to write/edit SP, but since I was only using VB, compiling would only give me a thumbs up/down for successful compilation.
    Morning person by habit, not by nature.

  5. #5
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for the tips. Have looked for an Oracle equivalent to RAISE NOTICE without success and sadly no Visual Studio, so looks like someone's got to sign a purchase order. Anything called Toad sounds good to me

  6. #6
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something you may want to be aware of with Toad. In our shop (I sit over the cube wall from the DBA), we've had problems with Toad not always letting go of sessions correctly. Where he's noticed this most specifically has been when we're doing a long compile. If we close Toad before it's finished, it leaves tables in a locked state and usually the only thing that seems to fix it is restarting the instance. There have been other oddities with Toad, but other than that, it's a great tool.
    Morning person by habit, not by nature.

  7. #7
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well...

    In Oracle, if you access the stored procedure from the PL*SQL command line, you will be shown the error immediately. There isn't a need for any commands. If you access it from scripting or programming languague, this error should be returned as the result to your query and you just have to monitor it.

    You can also use an "exception" block to catch any possible errors and return better information if you want. This is done by catching the error flags.

    An example of an exception block would be:
    Code:
    declare
      pi constant NUMBER(9,7) = 3.1415926;
      radius INTEGER(5)
      area NUMBER (14,2);
      x1 NUMBER(14,2);
    begin
      radius := 3;
      loop
        x1 := 1/(radius-4);
        area := pi * power(radius,2);
         insert into AREAS values (radius, area);
        radius := radius+1;
        exit when area >100;
      end loop;
    exception
      when ZERO_DIVIDE
       then insert into AREAS values (0,0);
    end;
    I have tried TOAD in the past and it simply didn't work for me. I just couldn't get to like it. What I use is called DBArtisan. You can find that at http://www.embarcadero.com/
    ...
    exception
    Wayne Luke
    ------------


  8. #8
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Think it's time I start learning stored proceedures as well (need it for work and hey - does nice things to the pay slip). I'll point the guy I work with at all that - not sure how much he knows about error reporting - he's nervous because he's learning stored procedures as well (they sacked our db guy in recent job cuts!) and want's to have an idea of whether they'll work before he runs them and certainly more error feedback would be good. Although he's working on a development system, it's still important enough not to want to trash data.

    Despite having knocked Oracle a little in another thread, it does stand up where other dbs would probably fall over. If anyone wants to hear the full story of our system, say the word (could be either boring or fascinating, depending on your point of view). It's hacked and mishandled for about 7 years by a succession of cowboy developers (some ex airline pilots) and despite breaking every rule of common sense in application and db design, Oracle is still standing.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •