Thread: Converting PL/SQL to PL/PGSQL

Converting PL/SQL to PL/PGSQL

From
Klaus Reger
Date:
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


Re: Converting PL/SQL to PL/PGSQL

From
Roberto Mello
Date:
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


Re: Converting PL/SQL to PL/PGSQL

From
Klaus Reger
Date:
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
outputshould be directed to.            If <file> is a directory, one source-file will be generated            for
everyprocedure. 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
(Defaultis 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
thegenerated 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-definitionsand 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
NUMBERIS   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



Re: Converting PL/SQL to PL/PGSQL

From
Bruce Momjian
Date:
> > > - 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,
Pennsylvania19026
 


Re: Converting PL/SQL to PL/PGSQL

From
Tom Lane
Date:
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


Re: Converting PL/SQL to PL/PGSQL

From
Bruce Momjian
Date:
> 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,
Pennsylvania19026