Home » SQL & PL/SQL » SQL & PL/SQL » Help needed migrating MSSQL Function To Oracle 10g (WinXP, Oracle 10g)
Help needed migrating MSSQL Function To Oracle 10g [message #415385] Mon, 27 July 2009 09:22 Go to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Hello,

I have the following MSSQL code which I need to migrate to Oracle 10g. The problem I am having is the MSSQL code creates a temp table as the return type and I am unsure how to get the same functionality in PL/SQL.

MSSQL CODE
CREATE FUNCTION dbo.QueryCurrentWhy
(
@ColumnID INT,
@GroupID INT,
@Parents VARCHAR(8000)
)
RETURNS @R TABLE(ID1 int IDENTITY (1, 1) NOT NULL, ID2 int, ColID int, Name VARCHAR(255), Tlevel int, ParentID int, Processed BIT)
AS

BEGIN
.......
RETURN

END

The code I have currently written in PL/SQL is below. I firstly create a global temp table and then a sequence and trigger for it. I then create the Function in the hope I can specify the temp table as the return type. Problem is I get the error "PLS-00201: identifier 'TEMPR' must be declared". Im guessing this is because the temp table is declared outside the function declaration?

ORACLE CODE
CREATE GLOBAL TEMPORARY TABLE tempR
(
ID1 INT NOT NULL,
ID2 INT,
ColID INT,
Name VARCHAR2(255),
Tlevel INT,
ParentID INT,
Processed CHAR(1)
)
/

CREATE SEQUENCE gCounter
START WITH 1
INCREMENT BY 1
/

CREATE OR REPLACE TRIGGER gTrigger
BEFORE INSERT ON tempR
FOR EACH ROW
DECLARE TEMP_NO INT;
BEGIN
SELECT gCounter.NEXTVAL INTO TEMP_NO FROM DUAL;
:NEW.ID1 := TEMP_NO;
END;
/

CREATE OR REPLACE FUNCTION TSORADB.QueryCurrentWhy
(
aColumnID INT,
aGroupID INT,
aParents VARCHAR2(8000)
)
RETURN tempR
AS
BEGIN
.....
RETURN;
EXCEPTION when NO_DATA_FOUND then null;
END;
/

Basically is it possible for me to declare the temp table in the return statement, if not then what is the best way to go about this?

Thanks in advance

Toby

[Updated on: Mon, 27 July 2009 09:29]

Report message to a moderator

Re: Help needed migrating MSSQL Function To Oracle 10g [message #415391 is a reply to message #415385] Mon, 27 July 2009 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
For Global Temporary Tables, they should be manually created once per schema.
There is no need to create them on the fly.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415392 is a reply to message #415385] Mon, 27 July 2009 09:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
CREATE OR REPLACE FUNCTION TSORADB.QueryCurrentWhy
(
aColumnID INT,
aGroupID INT,
aParents VARCHAR2(8000)
)
RETURN tempR


Database Table is not a valid return type for an oracle function.
The closest equivalent is ref_cursor.
But you should bear in mind that you rarely need to use temp tables in oracle.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415399 is a reply to message #415391] Mon, 27 July 2009 09:57 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
The code is part of a Change Script which I am migrating so will only ever be run once. The Function will then be called from a Stored Procedure.

So it's not possible to have the function return a temp table to a stored procedure?
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415402 is a reply to message #415385] Mon, 27 July 2009 10:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
No. That concept doesn't exist in oracle.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415404 is a reply to message #415402] Mon, 27 July 2009 10:23 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Sad

So can I create a temp table, add stuff to it etc. Then put contents into a ref_cursor and return it from the Function?

The code in the SProc which calls the function is... (MSSQL code)

INSERT INTO #TmpR1 SELECT * FROM QueryCurrentWhy(@ColumnID, @GroupID, @Parents)

where #TmpR1 is a temp table.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415413 is a reply to message #415385] Mon, 27 July 2009 10:33 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could but it's almost certainly more hassle than it's worth.

Can't you just change this:
INSERT INTO #TmpR1 SELECT * FROM QueryCurrentWhy(@ColumnID, @GroupID, @Parents)

into this:
INSERT INTO #TmpR1 SELECT * FROM <actual select statement>;


But then if #TmpR1 is a temp table (and I'd avoid having a # at the beginning of any table name) then I'd be looking at how to avoid having to use it at all.

But without knowing what you're trying to accomplish with all this I can't really suggest an alternative.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415414 is a reply to message #415385] Mon, 27 July 2009 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In order to migrate to Oracle (or any dbms) you need to master PL/SQL and Oracle (or any associated procedural language and dbms).
It is NOT possible for a beginner.

Regards
Michel
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415418 is a reply to message #415413] Mon, 27 July 2009 10:43 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
The actual select statement is several hundred lines of code long, and is reused in several places, so hard coding it in the SProc isn't really an option.

I will have a go at changing the return type to refcursor.

Quote:
In order to migrate to Oracle (or any dbms) you need to master PL/SQL and Oracle (or any associated procedural language and dbms).
It is NOT possible for a beginner.


Thanks for the brutally honest advice. Can i give you my bosses email?? Laughing
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415474 is a reply to message #415418] Tue, 28 July 2009 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
One of the big differences between Oracle and MS SQL Server are that SQL Server is not particularly good at complicated queries. To circumvent these, intermediate results are stored in temporary tables that are created and dropped on the fly.
Now, in Oracle, creating and dropping tables on the fly is an absolute NO-Go, it is expensive and it forces you to use dynamic SQL.
Luckily, Oracle is very good in complicated joins, so the way to go in Oracle is not to store intermediate results but to bake it all in one big query.

I guess this is what both cookiemonster and Michel tried to tell you, I just felt the urge to point it out somewhat more explicitly.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415511 is a reply to message #415474] Tue, 28 July 2009 02:18 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
I do appreciate the useful advice, and it isn't falling on deaf ears.

Its not always what you want to hear, but you are all vastly more experienced in Oracle than i am Razz

Frank, are there any good articles you can point me towards showing some of the 'complicated join' techniques you mention - possible showing how they circumvent the use of temp tables?

Kind regards,
Toby
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415526 is a reply to message #415511] Tue, 28 July 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no specific join, it is just that Oracle supports the full power of SQL without forcing the developer to search for workaround with temporary tables.
Or in the reverse way, you can ember the query you use for the temporary table inside the main query.

Regards
Michel
Re: Help needed migrating MSSQL Function To Oracle 10g [message #415554 is a reply to message #415385] Tue, 28 July 2009 04:11 Go to previous messageGo to next message
t.summerfield
Messages: 39
Registered: March 2007
Location: UK
Member
Using a refcursor as the return type seems to work

open v_ref_cur for select * from tempR;
return v_ref_cur;


On a side note, I stumbled accross this article on migrating temp tables to Oracle. This seems to agree (contrary to your suggestions) that I should "replace or emulate temporary tables within Oracle to ease migration". Obviously in an ideal world I would follow your advice and make use of Oracles 'advantages' over MSSQL, but due to time contraints I have gone down the temp table route.....

Thanks all, your help and insight is much appreciated.
Re: Help needed migrating MSSQL Function To Oracle 10g [message #419214 is a reply to message #415385] Thu, 20 August 2009 08:03 Go to previous message
wonn1377
Messages: 3
Registered: August 2009
Location: ny
Junior Member
I think u should use a third party application to resolve this problem, i use dbload to solve it when i was migrated my data, i found it on google search.

Download Free : www.dbload.com
Previous Topic: view to find data growth in table
Next Topic: Call base type constructor
Goto Forum:
  


Current Time: Fri Dec 09 23:25:45 CST 2016

Total time taken to generate the page: 0.09222 seconds