pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrouble porting a Oracle PL/SQL procedure to PL/pgSQL
4+ messages / 3 participants
[nested] [flat]
* Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
@ 2003-06-17 01:57 Tony G. Harris <tgharris_1@juno.com>
2003-06-17 02:17 ` Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tony G. Harris @ 2003-06-17 01:57 UTC (permalink / raw)
To: pgsql-sql
Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The
text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references
Oracle in its examples, though I can usually get PostgreSQL to work
almost as well.
Well, I'm almost to the end of the book and I'm trying to port some of
the book's PL/SQL examples to PL/pgSQL, with mixed success. In this
case, I translated
BEGIN
DECLARE
UnknownPayType EXCEPTION;
CURSOR pay_cursor IS
SELECT name, pay_type, pay_rate, eff_date, sysdate,
rowid
FROM pay_table;
IndRec pay_cursor%ROWTYPE;
. . .
END;
/
to this:
CREATE OR REPLACE FUNCTION update_pay() RETURNS VOID AS '
DECLARE
pay_cursor CURSOR IS
SELECT name, pay_type, pay_rate, eff_date, current_date, oid
FROM pay_table;
IndRec pay_cursor%ROWTYPE;
cOldDate DATE;
. . .
END;
'
LANGUAGE 'plpgsql';
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
WARNING: plpgsql: ERROR during compile of update_pay near line 2
ERROR: pay_cursor: no such class
I tried several ways of defining pay_cursor, with the same result.
What am I to do?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
2003-06-17 01:57 Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Tony G. Harris <tgharris_1@juno.com>
@ 2003-06-17 02:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-06-17 04:42 ` Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2003-06-17 02:17 UTC (permalink / raw)
To: Tony G. Harris <tgharris_1@juno.com>; +Cc: pgsql-sql
On Mon, 16 Jun 2003, Tony G. Harris wrote:
> Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The
> text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references
> Oracle in its examples, though I can usually get PostgreSQL to work
> almost as well.
>
> Well, I'm almost to the end of the book and I'm trying to port some of
> the book's PL/SQL examples to PL/pgSQL, with mixed success. In this
> case, I translated
>
> BEGIN
> DECLARE
> UnknownPayType EXCEPTION;
> CURSOR pay_cursor IS
> SELECT name, pay_type, pay_rate, eff_date, sysdate,
> rowid
> FROM pay_table;
> IndRec pay_cursor%ROWTYPE;
> . . .
> END;
> /
>
> to this:
>
> CREATE OR REPLACE FUNCTION update_pay() RETURNS VOID AS '
> DECLARE
> pay_cursor CURSOR IS
> SELECT name, pay_type, pay_rate, eff_date, current_date, oid
> FROM pay_table;
> IndRec pay_cursor%ROWTYPE;
> cOldDate DATE;
> . . .
> END;
> '
> LANGUAGE 'plpgsql';
>
> 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
> WARNING: plpgsql: ERROR during compile of update_pay near line 2
> ERROR: pay_cursor: no such class
>
> I tried several ways of defining pay_cursor, with the same result.
> What am I to do?
I think it's complaining because you can't use pay_cursor%ROWTYPE. Maybe
declaring IndRec as being of type record may work (don't know for
certain).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL
2003-06-17 01:57 Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Tony G. Harris <tgharris_1@juno.com>
2003-06-17 02:17 ` Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-06-17 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-06-17 04:42 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Tony G. Harris <tgharris_1@juno.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS!
@ 2003-06-17 16:15 Tony G. Harris <tgharris_1@juno.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tony G. Harris @ 2003-06-17 16:15 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: sszabo@megazone23.bigpanda.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-06-17 16:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-17 01:57 Trouble porting a Oracle PL/SQL procedure to PL/pgSQL Tony G. Harris <tgharris_1@juno.com>
2003-06-17 02:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-06-17 04:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-17 16:15 Re: Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS! Tony G. Harris <tgharris_1@juno.com>
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