Horton

Thread

  1. Converting PL/SQL to PL/PGSQL

    Klaus Reger <k.reger@gmx.de> — 2001-05-10T13:33:27Z

    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

  2. Re: Converting PL/SQL to PL/PGSQL

    Roberto Mello <rmello@cc.usu.edu> — 2001-05-10T17:23:11Z

    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

  3. Re: Converting PL/SQL to PL/PGSQL

    Klaus Reger <k.reger@gmx.de> — 2001-05-11T08:48:38Z

    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

  4. Re: Converting PL/SQL to PL/PGSQL

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-11T11:03:21Z

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

  5. Re: Converting PL/SQL to PL/PGSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T13:44:02Z

    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

  6. Re: Converting PL/SQL to PL/PGSQL

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-12T21:25:48Z

    > 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