From: Tony G. Harris <tgharris_1@juno.com>
To: tgl@sss.pgh.pa.us
Cc: sszabo@megazone23.bigpanda.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS!
Date: Tue, 17 Jun 2003 11:15:55 -0500
Message-ID: <20030617.111556.-16301703.1.tgharris_1@juno.com> (raw)
On Tue, 17 Jun 2003 00:42:54 -0400 Tom Lane <tgl@sss.pgh.pa.us> writes:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Mon, 16 Jun 2003, Tony G. Harris wrote:
> >> The problem is, when I call the function, I get:
> >> sql-practice=# select update_pay();
> >> ERROR: pay_cursor: no such class
> >> WARNING: plpgsql: ERROR during compile of update_pay near line
> 2
> >> ERROR: pay_cursor: no such class
>
> > I think it's complaining because you can't use
> pay_cursor%ROWTYPE.
>
> Yeah, you're right. I didn't believe that theory at first because
> the line number reference didn't point at the line with %ROWTYPE
> ...
> but upon digging into it I find that plpgsql's code for determining
> the line number to report is flat wrong for this case. (I've
> committed
> a quick fix into CVS tip, but I wonder whether the whole mechanism
> shouldn't be rethought. Calling plpgsql_scanner_lineno() all over
> the
> place doesn't seem real clean.)
>
> > Maybe declaring IndRec as being of type record may work (don't
> know
> > for certain).
>
> That's what I'd try. We don't consider that declaring a cursor
> creates
> a named rowtype ... I'm surprised that Oracle seems to think it
> does.
>
> regards, tom lane
>
>
Stephan, Tom,
Thank you both for your insights. You were right, Stephan, declaring
IndRec as a RECORD type
worked ! That and a couple other no doubt "quick and dirty" fixes and
the function ran exactly how it was supposed to.
Again, thank you both, and everyone on the pgsql-SQL list for all the
help.
Tony
Tony G. Harris
928 Sibley Street
Hammond, IN 46320-1649
Phone: (219) 931-3546
FAX: (646) 405-8569
email: tgharris_1@juno.com
----------------------------
I returned, and saw under the sun, that the race is not to the swift, nor
the battle to the strong, neither yet bread to the wise, nor yet riches
to men of understanding, nor yet favour to men of skill; but time and
chance happeneth to them all.
-- Ecclesiastes 9:11
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgharris_1@juno.com, tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com
Subject: Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS!
In-Reply-To: <20030617.111556.-16301703.1.tgharris_1@juno.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox