Thread: Need help in porting Oracle PL/SQL's OUT paramater based procedures
Hi All,<br /><br /> I am involved in porting <a href="https://fedorahosted.org/spacewalk/">Spacewalk</a>'s backend DBschema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function porting.<br/><br />A few things have been sorted out (<a href="https://www.redhat.com/archives/spacewalk-devel/2009-February/msg00123.html">link</a>),and am now stuck with the OUTparameters! I saw the example of converting a PL/SQL function <a href="http://www.postgresql.org/docs/8.1/static/plpgsql-porting.html#PLPGSQL-PORTING-EX3">cs_parse_url</a>,and see that finallyit has been advised to use the ported version as<br /><br />SELECT * FROM cs_parse_url('<a href="http://foobar.com/query.cgi?baz'">http://foobar.com/query.cgi?baz'</a>);<br/><br />that is, not passing anything forthe OUT or INOUT parameters. This works fine for a simple SELECT usage, but does not play well when this function is tobe called from another function, (and assuming that it'd break the application code too, which uses Oracle syntax of callingfunctions)!<br /><br />I have a simple function f() which I'd like to be ported in such a way that it works when calledfrom other plpgsql code, as well as when the application uses the Oracle like syntax. Here's a sample usage of thefunction f() in Oracle:<br /><br /><snip><br />create or replace function f( a in out int, b out varchar ) returnchar as<br />begin<br /> a := 10;<br /> b := 'some string';<br /> return 'c';<br />end;<br />/<br /><br />createor replace function f_caller return int as<br /> a int;<br /> b varchar(32);<br />begin<br /> dbms_output.put_line(f( a, b ) );<br /> dbms_output.put_line( a );<br /> dbms_output.put_line( b );<br /> return0;<br />end;<br />/<br /><br />set serveroutput on<br /><br />select f_caller from dual;<br /><br /> F_CALLER<br />----------<br/> 0<br /><br />c<br />10<br />some string<br /></snip><br /><br />Has anyone attempted portingPL/SQL, and if so, please share your experince with the OUT parameters.<br /><br />Thanks and best regards,<br /><br/>PS: Postgres 8.1 is going to be the oldest supported version by Spacewalk.<br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br/>singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br /><br />EnterpriseDB <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail sent from my BlackLaptopdevice<br />
(forgot to include general list earlier)
Hi All,
I am involved in porting Spacewalk's backend DB schema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function porting.
A few things have been sorted out (link), and am now stuck with the OUT parameters! I saw the example of converting a PL/SQL function cs_parse_url, and see that finally it has been advised to use the ported version as
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
that is, not passing anything for the OUT or INOUT parameters. This works fine for a simple SELECT usage, but does not play well when this function is to be called from another function, (and assuming that it'd break the application code too, which uses Oracle syntax of calling functions)!
I have a simple function f() which I'd like to be ported in such a way that it works when called from other plpgsql code, as well as when the application uses the Oracle like syntax. Here's a sample usage of the function f() in Oracle:
<snip>
create or replace function f( a in out int, b out varchar ) return char as
begin
a := 10;
b := 'some string';
return 'c';
end;
/
create or replace function f_caller return int as
a int;
b varchar(32);
begin
dbms_output.put_line( f( a, b ) );
dbms_output.put_line( a );
dbms_output.put_line( b );
return 0;
end;
/
set serveroutput on
select f_caller from dual;
F_CALLER
----------
0
c
10
some string
</snip>
Has anyone attempted porting PL/SQL, and if so, please share your experince with the OUT parameters.
Thanks and best regards,
PS: Postgres 8.1 is going to be the oldest supported version by Spacewalk.
-- Hi All,
I am involved in porting Spacewalk's backend DB schema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function porting.
A few things have been sorted out (link), and am now stuck with the OUT parameters! I saw the example of converting a PL/SQL function cs_parse_url, and see that finally it has been advised to use the ported version as
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
that is, not passing anything for the OUT or INOUT parameters. This works fine for a simple SELECT usage, but does not play well when this function is to be called from another function, (and assuming that it'd break the application code too, which uses Oracle syntax of calling functions)!
I have a simple function f() which I'd like to be ported in such a way that it works when called from other plpgsql code, as well as when the application uses the Oracle like syntax. Here's a sample usage of the function f() in Oracle:
<snip>
create or replace function f( a in out int, b out varchar ) return char as
begin
a := 10;
b := 'some string';
return 'c';
end;
/
create or replace function f_caller return int as
a int;
b varchar(32);
begin
dbms_output.put_line( f( a, b ) );
dbms_output.put_line( a );
dbms_output.put_line( b );
return 0;
end;
/
set serveroutput on
select f_caller from dual;
F_CALLER
----------
0
c
10
some string
</snip>
Has anyone attempted porting PL/SQL, and if so, please share your experince with the OUT parameters.
Thanks and best regards,
PS: Postgres 8.1 is going to be the oldest supported version by Spacewalk.
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures
From
Richard Huxton
Date:
Gurjeet Singh wrote: > that is, not passing anything for the OUT or INOUT parameters. This works > fine for a simple SELECT usage, but does not play well when this function is > to be called from another function, (and assuming that it'd break the > application code too, which uses Oracle syntax of calling functions)! > > I have a simple function f() which I'd like to be ported in such a way that > it works when called from other plpgsql code, as well as when the > application uses the Oracle like syntax. Here's a sample usage of the > function f() in Oracle: If you really want Oracle-compatible functions I think there's a company that might sell you a solution :-) However, failing that you'll want an example of OUT parameters in PostgreSQL code - see below. The main thing to remember is that the OUT is really just a shortcut way of defining a record type that gets returned. It's nothing like passing by reference in <insert real programming language here>. BEGIN; CREATE OR REPLACE FUNCTION f1(IN a integer, INOUT b integer, OUT c integer) RETURNS RECORD AS $$ BEGIN c := a + b; b := b + 1; -- No values in RETURN RETURN; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f2() RETURNS boolean AS $$ DECLARE a integer := 1; b integer := 2; c integer := -1; r RECORD; BEGIN r := f1(a, b); -- Original variables unaffected RAISE NOTICE 'a=%, b=%, c=%', a,b,c; -- OUT params are here instead RAISE NOTICE 'r.b=%, r.c=%', r.b, r.c; -- This works, though notice we treat the function as a row-source SELECT (f1(a,b)).* INTO b,c; RAISE NOTICE 'a=%, b=%, c=%', a,b,c; RETURN true; END; $$ LANGUAGE plpgsql; SELECT f2(); ROLLBACK; -- Richard Huxton Archonet Ltd
Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures
From
Gurjeet Singh
Date:
On Thu, Feb 12, 2009 at 6:18 PM, Richard Huxton <dev@archonet.com> wrote:
:) Spacewalk is not interested you see.
You see, you did not pass the third (OUT) parameter when calling the function:
r := f1(a, b);Gurjeet Singh wrote:If you really want Oracle-compatible functions I think there's a company
> that is, not passing anything for the OUT or INOUT parameters. This works
> fine for a simple SELECT usage, but does not play well when this function is
> to be called from another function, (and assuming that it'd break the
> application code too, which uses Oracle syntax of calling functions)!
>
> I have a simple function f() which I'd like to be ported in such a way that
> it works when called from other plpgsql code, as well as when the
> application uses the Oracle like syntax. Here's a sample usage of the
> function f() in Oracle:
that might sell you a solution :-)
:) Spacewalk is not interested you see.
However, failing that you'll want an example of OUT parameters in
PostgreSQL code - see below. The main thing to remember is that the OUT
is really just a shortcut way of defining a record type that gets
returned. It's nothing like passing by reference in <insert real
programming language here>.
BEGIN;
CREATE OR REPLACE FUNCTION f1(IN a integer, INOUT b integer, OUT c
integer) RETURNS RECORD AS $$
BEGIN
c := a + b;
b := b + 1;
-- No values in RETURN
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION f2() RETURNS boolean AS $$
DECLARE
a integer := 1;
b integer := 2;
c integer := -1;
r RECORD;
BEGIN
r := f1(a, b);
-- Original variables unaffected
RAISE NOTICE 'a=%, b=%, c=%', a,b,c;
-- OUT params are here instead
RAISE NOTICE 'r.b=%, r.c=%', r.b, r.c;
-- This works, though notice we treat the function as a row-source
SELECT (f1(a,b)).* INTO b,c;
RAISE NOTICE 'a=%, b=%, c=%', a,b,c;
RETURN true;
END;
$$ LANGUAGE plpgsql;
SELECT f2();
ROLLBACK;
You see, you did not pass the third (OUT) parameter when calling the function:
This differs from Oracle syntax where you _need_ to pass the third parameter.
And what if the Oracle function actually returns a value too? How do we handle that in the application, because we can't declare RECORD vars in Java/perl/python etc.
Thanks and best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device