PL/SQL to PLpg/SQL - NO_DATA_FOUND

From: Matt Miller <mattm(at)epx(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: PL/SQL to PLpg/SQL - NO_DATA_FOUND
Date: 2005-07-12 16:53:38
Message-ID: 1121187218.4186.21.camel@dbamm01-linux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2005-07-12 16:54:25 Re: Windows version of PostgreSQL 8.x?
Previous Message Tom Lane 2005-07-12 16:51:42 Re: Japanese words not distinguished