Re: debugging PL/SQL

From: C.J.Jardine <cj10_at_ucs.cam.ac.uk>
Date: Fri, 9 Dec 1994 12:59:11
Message-ID: <cj10.36.000CFCE6_at_ucs.cam.ac.uk>

In article <3c86lt$n3o$1_at_mhadg.production.compuserve.com> Frank Greene <74200.427_at_CompuServe.COM> writes:

>I have inherited a series of packages that were supposedly clean
>compiles. However, when I attempt to start them, I almost always
>get the &*&^%$ PPRCEDURE COMPILED WITH ERRORS message.
 

>Am I mising something fundamental? Surely there is some way to
>get the package compiler to be a little more descriptive about
>the nature of the error. If not, are there any kind of third
>party packages that can be used?

This is getting Frequently Asked.

Check out the SQL*Plus command SHOW ERRORS.

I use the following Oraperl script to display all the compilation errors I own.

#!/usr/bin/oraperl
die ("You should use oraperl, not perl\n") unless defined &ora_login;

$lda = &ora_login("","","")

        || die $ora_errstr, "\n";
$csr = &ora_open($lda,
 "SELECT s.text, TO_CHAR(s.line, '9999MI'), s.name, s.type, " .

   "LPAD('!', e.position), e.text FROM user_source s, user_errors e " .
   "WHERE s.name = e.name AND s.type = e.type AND s.line = e.line " .
   "ORDER BY s.name, s.type, e.sequence")
        || die $ora_errstr, "\n";

while (($text, $line, $name, $type, $pos, $msg) = &ora_fetch($csr)) { unless ( ($type eq $oldtype) && ($name eq $oldname) )   { print "\n$type $name:\n";

     ($oldtype, $oldname) = ($type, $name); };
  print "$line$text     $pos\n$msg\n";

};
die $ora_errstr, "\n" if $ora_errno;

do ora_close($csr) || die $ora_errstr, "\n"; do ora_logoff($lda) || die $ora_errstr, "\n"; Received on Fri Dec 09 1994 - 12:59:11 CET