I need to convert hundreds of Oracle stored procs across several
developing databases. I'm focusing on scripting as much of this as
possible, and I'm currently stuck on converting PL/SQL's NO_DATA_FOUND
behavior. What approaches have other people used? I'm targeting
PostgreSQL 8.1.
The problem is that PL/SQL raises an exception (NO_DATA_FOUND) whenever
a SELECT ... INTO returns no rows; the target variables are not touched,
and control jumps to the exception handlers. Much of my code is built
around this behavior. I realize that I can code equivalent behavior in
PL/pgSQL, e.g. by using the FOUND special variable and temp vars as INTO
targets, but I'm sincerely hoping that I can script this proc conversion
without going crazy building a PL/SQL parser (I don't happen to have a
PL/SQL gram.y lying around).
My current approach is to use/extend the Ora2Pg Perl stuff, and I'm not
seeing a clean solution to the discrepancy between PL/SQL's exception
raising and PL/pgSQL's setting a flag (i.e. FOUND) and continuing.