Thanks for the question, pal.

Asked: April 30, 2008 - 8:15 pm UTC

Last updated: January 04, 2013 - 10:47 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I am migrating some functions from postgres to oracle. Now in POstgres there is a Set of feature which can be used to return multiple values, Oracle can not return multiple values.

I thought of using a vararray and store values in it and return the vararray in the
end.

I am not sure how to retrieve it back in my report for postgres function they are doing something like : select * from functionname(parm1, param2)

And it displays the values, what should I do in oracle function as it will be returning an array.

Should I use ref cursor instead?

Below is the postgres and oracle sql

CREATE OR REPLACE FUNCTION getdetails(date, date, text, text[])
RETURNS SETOF bigint AS
'

DECLARE

diaryNotes_t RECORD;
prevStatus varchar;
prevKey int8;
beginDate ALIAS FOR $1;
endDate ALIAS FOR $2;
initalStatus ALIAS FOR $3;
finalStatus ALIAS FOR $4;

begin
...
return next diaryNotes_t."CORECASEKEY";

end

Oracle

create or replace FUNCTION getdetails( beginDatedate IN DATE, endDate IN DATE, initalStatus IN varchar2,finalStatus IN TABLE_TYPES.tString)
RETURN TABLE_TYPES.tNumber AS

num_ref TABLE_TYPES.tNumber;
prevStatus NVARCHAR2(20);
prevKey NUMBER;
TYPE diaryNotes_t IS RECORD ("CORECASEKEY" NUMBER, "CASEDIARYKEY" NUMBER(22,0), "STATUS" NVARCHAR2(30 CHAR), "RECEIVEDDATE" DATE, "DATE" DATE);
BEGIN
...
FOR diaryNotes_t IN (select ..) loop begin ..

num_ref.extend;
num_ref(num_ref.count) := diaryNotes_t."CORECASEKEY";
end;

END;

and Tom said...

... Oracle can not return multiple values. ...

please - yes it can.

Interesting that in the year 2008 you are 'migrating' to an entirely unsupported very old release of the database. Why would you do that?

... Should I use ref cursor instead? ...

Yes, that is the best way by far to return a result set from the database. Absolutely. (and now you know at least one way to return multiple values!!!!)

Another way - way back in 8i - would be to use the table() clause on a function that returns a collection

ops$tkyte%ORA10GR2> create or replace type myScalarType as object
  2  ( x varchar2(30), y number, z date )
  3  /

Type created.

ops$tkyte%ORA10GR2> create or replace type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function foo( p_search in varchar2 ) return myTableType
  2  as
  3          l_data myTableType;
  4  begin
  5          select myScalarType(username,user_id,created)
  6            bulk collect into l_data
  7            from all_users
  8           where username like p_search;
  9
 10          return l_data;
 11  end;
 12  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from table( cast( foo('%A%') as myTableType ) );

X                                       Y Z
------------------------------ ---------- ---------
ANONYMOUS                              39 30-JUN-05
OLAPSYS                                47 30-JUN-05
SI_INFORMTN_SCHEMA                     45 30-JUN-05
SYSMAN                                 51 30-JUN-05
MDDATA                                 50 30-JUN-05
OPS$ORA10GR2                           56 14-DEC-05
BIG_TABLE                              58 14-DEC-05
A                                     306 30-APR-08
MAP                                   263 07-JAN-08
QUOTA                                  94 22-FEB-06
AQ                                    228 15-OCT-07
PERFSTAT                              148 31-MAR-06

12 rows selected.

and in 9i and above, that would be a pipelined function:

ops$tkyte%ORA10GR2> create or replace function foo( p_search in varchar2 ) return myTableType
  2  pipelined
  3  as
  4          l_data myTableType;
  5  begin
  6          for x in ( select username,user_id,created
  7                       from all_users
  8                  where username like p_search )
  9          loop
 10                  pipe row( myScalarType( x.username, x.user_id, x.created ) );
 11          end loop;
 12          return;
 13  end;
 14  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from table( cast( foo('%A%') as myTableType ) );

X                                       Y Z
------------------------------ ---------- ---------
ANONYMOUS                              39 30-JUN-05
OLAPSYS                                47 30-JUN-05
SI_INFORMTN_SCHEMA                     45 30-JUN-05
SYSMAN                                 51 30-JUN-05
MDDATA                                 50 30-JUN-05
OPS$ORA10GR2                           56 14-DEC-05
BIG_TABLE                              58 14-DEC-05
A                                     306 30-APR-08
MAP                                   263 07-JAN-08
QUOTA                                  94 22-FEB-06
AQ                                    228 15-OCT-07
PERFSTAT                              148 31-MAR-06

12 rows selected.

but, you should:

a) use a ref cursor whenever you can, user a REF CURSOR

b) use the collection 'trick' only when you absolutely have no way to do it otherwise

and most importantly, you are not MIGRATING, you are PORTING and porting code requires in many cases fundamental changes. Be accepting of that, don't be afraid to rip something apart - postgress and oracle are *different*. I would say the same to you if you were going the other way..

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Migrating Postgres to Oracle 11g

mfz, January 23, 2010 - 8:39 pm UTC

Tom -

I am working on a requirement of migrating Postgres on Linux to Oracle 11g on Windows . The databases are of less than 7 GB .

What is the best way to move the data , data with other objects ( stored procedures & functions) ?

I understand not all stored procedures can be used directly.
I would need to refactor the code to adapt to Oracle's locking / multiversioing features .

Jon, May 05, 2010 - 11:34 am UTC

I have the same issue on the following code but migrating from Postgres to 11gR2. Im trying to adjust code to Oracle but have NO idea at all:

CREATE OR REPLACE FUNCTION "f_TISS"(parm1 in number, parm2 in number)
RETURN SETOF "TISS" IS
declare
ret "TISS";
i int;
ordem int;
declare reg record;
begin
ret."TISS_cip" := parm1;
ret."TISS_visita" := parm2;

i := 0;
ordem := 0;
for reg in
<big_select>

loop
i := i + 1;
if (i > 5) then
ordem := ordem + 1;
ret."TISS_ordem" := ordem;
return next ret;
i := 1;
end if;
raise notice '%) - [%]', i, reg."Exa_nome";
if (i > 1) then
ret."TISS_det_1" := ret."TISS_det_1" || '# ';
ret."TISS_det_2" := ret."TISS_det_2" || '# ';
else
ret."TISS_det_1" := '';
ret."TISS_det_2" := '';
end if;

ret."TISS_det_1" := ret."TISS_det_1" || reg."Exa_nome";
ret."TISS_det_2" :=
ret."TISS_det_2"
|| reg."MPacExa_dataInclusao"
|| '; ' || reg."Qtdchcv_codigoAmb"
|| '; ' || reg."Exa_nome"
|| '; ' || cast((reg.valorch * reg.qtdch) as varchar)
|| '; ' || cast((reg.valorch * reg.qtdch) as varchar);
end loop;

if (i < 5) then
while (i < 5) loop
i := i + 1;
ret."TISS_det_1" := ret."TISS_det_1" || '# ';
ret."TISS_det_2" := ret."TISS_det_2" || '# ';
end loop;
raise notice 'Preenchido det_1 [%]', ret."TISS_det_1";
raise notice 'Preenchido det_2 [%]', ret."TISS_det_2";
ordem := ordem + 1;
ret."TISS_ordem" = ordem;
end if;
return next ret;

return;
end;

I know the basics of PL/SQL. So im really confused about this conversion. Dont now even how to start.

I appreciate some help from you guys.

Thanks in advance.

May 06, 2010 - 2:06 pm UTC

so, you give me a piece of code that doesn't work and expect me to be able to convert it into something that can?

And you don't even tell us what the goal of the procedure is? what the constraints are? what the inputs are? what the outputs are? What is trying to be done?

You know, the specification???

I have no clue what this code is supposed to do - that would be mandatory as step one for anyone to port anything.

Regarding porting functions to oracle

Jeetu, September 13, 2010 - 7:05 am UTC

Hi Tom,

I am in quest of porting db2 functions to Oracle.In our current db2 functions repository there are several functions which are returning "Tables" as such, following is a pseudo code

CREATE FUNCTION Some_Name ( some_input VARCHAR(128) )
returns table ( Some_int integer ) language sql reads sql data
return SOME_SQL_QUERY

And interestingly these returning tables from two or three functions are used in other function with left and right outer joins between them.

So I had two options of replicating the same functionality in oracle
1. Use table functions with object types.But the "joins" would not work.
2. Use ref cursors to fetch and use temporary tables to store data and replace joined functions with these tables. But I am not happy with this solution since creating GTT dynamically for multiple times looks semantically not good and I fear there would be DB performance issue.

Could you please share your opinion in this case.

Thanks

Jeetu

September 13, 2010 - 2:34 pm UTC

1) why wouldn't joins work?

and when porting, you should be aware of the fact that sometimes you have to RE-ENGINEER, re-implement, do things differently.

If you move from Oracle to DB2 - you would.
If you move from DB2 to Oracle - you would.

This looks like a "view" to me - why would you not use a view?

migrating from postgresql to oracle

Bharathi, December 25, 2012 - 11:55 pm UTC

Hi Tom,
I am migrating some functions from postgres to oracle. I've some problem while migration. The problem are "schema change between either other" and "some data type mis-match". Thanks in advance.

Regards
Bharathi

January 04, 2013 - 10:47 am UTC

sorry, there is so little information here that I actually have no idea what you are talking about.

"schema change between either other" and "some data
type mis-match".

they sound like partial error messages from some tool - but you don't really say what they are.

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Migrating Postgres to Oracle 11g

Regarding porting functions to oracle

migrating from postgresql to oracle

More to Explore

PL/SQL demos

PL/SQL docs

Migrating from POstgres to Oracle no set of

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Migrating Postgres to Oracle 11g

Regarding porting functions to oracle

migrating from postgresql to oracle

More to Explore

PL/SQL demos

PL/SQL docs