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.
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 |