Thread: PL/SQL to PLpg/SQL - NO_DATA_FOUND

PL/SQL to PLpg/SQL - NO_DATA_FOUND

From
Matt Miller
Date:
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.