Re: Converting PL/SQL to PL/PGSQL

Am Donnerstag, 10. Mai 2001 19:23 schrieb Roberto Mello:
> On Thu, May 10, 2001 at 03:33:27PM +0200, Klaus Reger wrote:
> Have you looked at the "Porting From Oracle PL/SQL" chapter of the
> PostgreSQL Programmer's Guide? I am expanding that guide to include more
> things, like queries. The goas is for it to become a "Porting From
> Oracle" guide.
Yes I did, and it was very helpful for me. Thank you for this stuff. I made a
list of the differences I found for me too. If you want it, I cam send it to
you.

> > Writing PS/PGSQL tools seems to be a bit hard, because of the existing
> > tool-infrastructure on linux. Are there are tools I have overseen?
> Heh? What do you mean by this? There are zillions of editors, both
> console and graphical, where you can do this.
Ah! That is right, I use emacs too.
> I have found pgaccess to be vey useful in testing. In the OpenACS
> project (www.openacs.org) we port thousands of lines of Oracle code to
> PostgreSQL, mostly using vim or Emacs.
> For testing, I use pgaccess because it lets me drop/recreate a
> function easily, plus it escapes quotes. One thing I don't like about it
> is that it's hard to keep things indented.
The problem for me seems, that the code is in the database. When you want to
edit it, you do this in three steps:
1. Get source from the database
2. Edit the source
3. Put it back to the database

When there are no syntax-problems in the proc-declarations, or any wrong
nested things step 3 is no problem. But often, when I ram my procedures I get
runtime-errors (without konowing, where the problem exactly is). So here some
type of compilation would be very useful.

First, I used pgacess too. because it is very helpful to develop
pl/pgsql-procedures. But as the maintainer of my own Web-database-frontend I
decided to write my own tool, which is very similar to pgaccess.

>
> > - A WWWdb-Application for editing and testing of SQL-Procedures over a
> > WEB-frontend
> Cool. Anywhere we can see this in action?
WWWdb of course. Point your browser to http://WWWdb.org. The procedure part
is very sensible (because I don't want everybody to change my procedures :-),
so it is not testable on my site. I may send you some screenshots, or you
could install WWWdb at your computer and I send you the code separately,
because it is not released as OpenSource yet.

> > - A perl-script, that does basic conversions between PL/SQL <-> XML <->
> > PL/PGSQL (The Procedure-definition is converted completely, the
> > code-block a little bit)
>
> Hmmm. *Very* interesting. Link? Source for this anywhere? We could
> probably use this at OpenACS.
I asked my boss, if he allows me to give out the sources, I will start a
project at sourceforge. Stay tuned.

In this way it is called:
------------------------------------------------------------------------------------------
work(at)pc01:SqlProc$ ConvertPlsql.pl -h

Call:
ConvertPlsql.pl [-DVw] [-o file] [file ...]

Switches:
-D Debugging-mode
-V show version
-o file
<file> is the file where the output should be directed to.
If <file> is a directory, one source-file will be generated
for every procedure. When <file> is a normal file, all output
will be generated into this single file. Default is STDOUT,
which can be passed explicitly as '-'
-s
Sort functions alphabetically at output (Default is unsorted)
-S Source-language
This is the language of the existing script-file(s).
Valid values are (Default is PL_SQL):
- pl_sql
-T Target-language
This is the language of the generated script-file(s).
Valid values are (Default is PL_PGSQL):
- xml
- pl_pgsql
-w Display warnings, that are found in conversion-process

Description:
ConvertPlsql.pl scans PL/SQL-Procedure-definitions and tries
to convert them to PL/PGSQL.

Here is an example of the conversion between Oracle, Postgres and XML:

------------------------------------------------------------------------------------------

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE SOURCE SYSTEM "./SqlProc.dtd">
<SOURCE>
<FUNCTION
NAME = "chk_ip"
TYPE = "FUNCTION"
RESULTTYPE = "NUMBER">
<PARAMETER
NAME = "IPADRESSp"
INOUT = "IN"
TYPE = "VARCHAR,"/>
<PARAMETER
NAME = "N_uid"
INOUT = "IN"
TYPE = "NUMBER,"/>
<VARIABLE
NAME = "N_tmp"
TYPE = "NUMBER"/>
<CODE>

SELECT test.NEXTVAL INTO N_uid /* FROM DUAL */ ;

N_tmp := 'That''s my quoted text!';

RETURN N_tmp;

EXCEPTION
WHEN others THEN
return -100;
</CODE>
</FUNCTION>

</SOURCE>

------------------------------------------------------------------------------------------

DROP FUNCTION chk_ip (VARCHAR, NUMBER,);
CREATE FUNCTION chk_ip (VARCHAR, NUMBER,)
RETURNS INTEGER AS '
DECLARE
IPADRESSp ALIAS FOR $1;
N_uid ALIAS FOR $2;
N_tmp INTEGER;
BEGIN

SELECT nextval(''test'') INTO N_uid /* FROM DUAL */ ;

N_tmp := ''That''''s my quoted text!'';

RETURN N_tmp;

-- ORA -- EXCEPTION
-- ORA -- WHEN others THEN
-- ORA -- return -100;
END;
' language 'plpgsql';

------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION chk_ip
(
IPADRESSp IN VARCHAR2,
N_uid IN NUMBER
)
RETURN NUMBER IS
N_tmp NUMBER;
BEGIN

SELECT test.NEXTVAL INTO N_uid FROM dual;

N_tmp := 'That''s my quoted text!';

RETURN N_tmp;

EXCEPTION
WHEN others THEN
return -100;
END;
/

------------------------------------------------------------------------------------------

Regards, Klaus