Converting PL/SQL to PL/PGSQL

Started by Klaus Regerover 24 years ago6 messages

K.Reger@gmx.de

over 24 years ago

Hi all!

I have to convert functions and procedures from Oracle to PostgreSQL. I
looked at all the stuff of the Pg-Homepage and I ask me if there are any
tools, that support the conversion.

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?

I have implemented the following tools for my use yet:

- A WWWdb-Application for editing and testing of SQL-Procedures over a
WEB-frontend
- 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)

Who else is working in this area? Any tips?

Regards, Klaus

Roberto Mello

rmello@cc.usu.edu

over 24 years ago

In reply to: Klaus Reger (#1)

Re: Converting PL/SQL to PL/PGSQL

On Thu, May 10, 2001 at 03:33:27PM +0200, Klaus Reger wrote:

Hi all!

I have to convert functions and procedures from Oracle to PostgreSQL. I
looked at all the stuff of the Pg-Homepage and I ask me if there are any
tools, that support the conversion.

That help you in the conversion, no.
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.

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

- A WWWdb-Application for editing and testing of SQL-Procedures over a
WEB-frontend

Cool. Anywhere we can see this in action?

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

	-Roberto
-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
       http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
(D)inner not ready:  (A)bort (R)etry (P)izza

Klaus Reger

K.Reger@gmx.de

over 24 years ago

In reply to: Roberto Mello (#2)

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

Bruce Momjian

pgman@candle.pha.pa.us

over 24 years ago

In reply to: Klaus Reger (#3)

Re: Converting PL/SQL to PL/PGSQL

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

With our new /contrib policy, we could put it right in our PostgreSQL
CVS contrib.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane

tgl@sss.pgh.pa.us

over 24 years ago

In reply to: Bruce Momjian (#4)

Re: Converting PL/SQL to PL/PGSQL

Bruce Momjian <pgman@candle.pha.pa.us> writes:

With our new /contrib policy, we could put it right in our PostgreSQL
CVS contrib.

?? What "new contrib policy"? I didn't notice any discussion of policy
changes ...

regards, tom lane

Bruce Momjian

pgman@candle.pha.pa.us

over 24 years ago

In reply to: Tom Lane (#5)

Re: Converting PL/SQL to PL/PGSQL

Bruce Momjian <pgman@candle.pha.pa.us> writes:

With our new /contrib policy, we could put it right in our PostgreSQL
CVS contrib.

?? What "new contrib policy"? I didn't notice any discussion of policy
changes ...

I was unsure what to do with Dbase and Oracle code recently contributed.
Vince and others said it should be in /contrib. We already have
loadable modules and backend tools in /contrib. Seems conversion tools
are also now to be placed in /contrib.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026