Home » SQL & PL/SQL » SQL & PL/SQL » SQl Server 2005 to Oracle 10g Proc conversion (Oracle 10g, Windows 2003)
SQl Server 2005 to Oracle 10g Proc conversion [message #352994] Fri, 10 October 2008 07:55 Go to next message
san_eins
Messages: 4
Registered: October 2008
Junior Member
Hi,

I'm involved in a SQL Server 2005 to Oracle 10g project.
I'm stuck with the following stored proc.
How do one convert the following stored proc to 10g.


CREATE PROCEDURE Proc1
@strTableName VARCHAR(20),
@intRowId BIGINT,
@strGroupList VARCHAR(7000)
AS

DECLARE @strQuery VARCHAR(200)
DECLARE @strGroup VARCHAR(50)

BEGIN

IF @@ROWCOUNT > 0
BEGIN
WHILE CHARINDEX ( '#', @strGroupList ) > 0
BEGIN
SET @strGroup = SUBSTRING ( @strGroupList , 1 , CHARINDEX ( '#', @strGroupList ) - 1 )
SET @strGroupList = SUBSTRING ( @strGroupList , CHARINDEX ( '#', @strGroupList ) + 1 , LEN(@strGroupList) - CHARINDEX ( '#', @strGroupList ))
SET @strQuery = 'SELECT * FROM [' + @strTableName + '.' + UPPER ( @strGroup ) + '] with (nolock) WHERE RowId = ' + CONVERT ( VARCHAR ( 20 ) , @intRowId )
EXEC ( @strQuery )
END
END
END


exec Proc1
@strTableName='20',@intRowId=1061,@strGroupList='ADRDEL#ADRINV#ADRWEB#AIR#AIR2#AIRADD#AIRFREE#AIRLH#AIRLOG#CAR#CARFREE#CARREMARK#COMG EN#Default#DELREMARK#DISC#GDSOBEGroupInfo#GRD#HTL#HTLREMARK#ID#INSURANCE#INTCON#INTE#INTNOT#INTNOT2#LOG#MARK#ORD#ORDREMARK#PAY#PAYADD #PAYCC#PAYCC2#PAYCC3#PAYCCADD#PAYCCADD2#PAYINFO#PAYREMARK#PAYVERK#RAIL#RAILREMARK#RIR#SHIP#TAFContract#TICKREMARK#TRAV#VISAREMARK#WEB REMARK#'

When you execute this it creates dynamic query on the runtime
and the calling application utilises those as resultset. The @strGroupList parameter is dynamic in the nature.

How can we write the same in oracle ?

Thanks
Sanjay
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353003 is a reply to message #352994] Fri, 10 October 2008 08:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum.

To start with try to spend some of your time reading the oracle reference manual (PL/Sql) . If you are stuck somewhere comeback to us. Url to the manual is

http://tahiti.oracle.com

Hope this helps

Regards

Raj
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353014 is a reply to message #352994] Fri, 10 October 2008 10:14 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Try to read some fundamentals on pl/sql.

Here's a bit of a pseudo or outline on your T-SQL to PL/SQL.


create procedure( parameters) is
   --declare some of your variables here
begin
   --this is where your block of code
   --placed here the assigments to variables you had
   --most of the functions you used is also available in oracle sql
   --look on 'native dynamic sql' to execute your query
end;
/


Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353130 is a reply to message #353014] Sat, 11 October 2008 12:05 Go to previous messageGo to next message
san_eins
Messages: 4
Registered: October 2008
Junior Member
Thanks for the reply.

I guess I wasn't clear enough with my query.

Let me clarify: Following is the oracle proc which oracle migration work bench has generated.


CREATE OR REPLACE PROCEDURE Proc1(
p_strTableName in VARCHAR2,
p_intRowId in NUMBER,
p_strGroupList in VARCHAR2,
rc_ResultSet SYS_REFCURSOR)

AS

DECLARE v_strQuery VARCHAR(200);
DECLARE v_strGroup VARCHAR(50);

BEGIN

LOOP
EXIT WHEN INSTR ( '#', p_strGroupList ) > 0
BEGIN
v_strGroup := SUBSTR( p_strGroupList , 1 , INSTR( '#', p_strGroupList ) - 1 ) ;
p_strGroupList := SUBSTR ( p_strGroupList , INSTR( '#', p_strGroupList ) + 1 , LENGTH(p_strGroupList) - INSTR( '#', p_strGroupList ));
p_strQuery := 'SELECT * FROM "' + p_strTableName + '.' + UPPER ( v_strGroup ) + '" with WHERE RowId_ = ' || p_intRowId ;
OPEN p_strQuery FOR rc_ResultSet;
END;
END LOOP;
END Proc1;


But if you execute the above one with

declare
rc_ResultSet1 SYS_REFCURSOR;
begin
exec Proc1
('20',1061,'ADRDEL#ADRINV#ADRWEB#AIR#AIR2#AIRADD#AIRFREE#AIRLH#AIRLOG#CAR#CARFREE#CARREMARK#COMG EN#Default#DELREMARK#DISC#GDSOBEGroupInfo#GRD#HTL#HTLREMARK#ID#INSURANCE#INTCON#INTE#INTNOT#INTNOT2#LOG#MARK#ORD#ORDREMARK#PAY#PAYADD #PAYCC#PAYCC2#PAYCC3#PAYCCADD#PAYCCADD2#PAYINFO#PAYREMARK#PAYVERK#RAIL#RAILREMARK#RIR#SHIP#TAFContract#TICKREMARK#TRAV#VISAREMARK#WEB REMARK#',rc_ResultSet1);
end;

The proc will loop thru the delimited p_strGroupList and will generate statements like
Select * from "20.ADRDEL" where rowid_=1061
............
............
............
till
Select * from "20.WEBREMARK" where rowid_=1061
and the calling proc will get the result of the only last table ie. "20.WEBREMARK"

my question is can we create dynamic (or array type) sys_refcusors?

FYI I cant use union all because the table strucs are different.

Thanks
Sanjay
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353203 is a reply to message #353130] Sun, 12 October 2008 15:25 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
See:

SQL> declare
  2      type t_x is ref cursor;
  3     type t_r is table of t_x;
  4  begin
  5    null;
  6  end;
  7  /
        type t_r is table of t_x;
                    *
ERROR at line 3:
ORA-06550: line 3, column 14:
PLS-00990: Index Tables of Cursor Variables are disallowed
ORA-06550: line 3, column 2:
PL/SQL: Item ignored


SQL> declare
  2      type t_x is ref cursor;
  3     type t_r is table of sys_refcursor;
  4  begin
  5    null;
  6  end;
  7  /
        type t_r is table of sys_refcursor;
                    *
ERROR at line 3:
ORA-06550: line 3, column 14:
PLS-00990: Index Tables of Cursor Variables are disallowed
ORA-06550: line 3, column 2:
PL/SQL: Item ignored


SQL>


Not sure on what you're trying to do. But can't you just execute the query passing the parameter 'per table' then process the cursor? why would you want it in an array of ref cursor?
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353212 is a reply to message #353203] Sun, 12 October 2008 22:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
san_eins why don't you first read how to write a SP in oracle.
Getting solution for your problem won't help you for long.

SQL Server and Oracle are totally different Databases.


Regards,
Rajat Ratewal
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353924 is a reply to message #353203] Wed, 15 October 2008 11:51 Go to previous messageGo to next message
san_eins
Messages: 4
Registered: October 2008
Junior Member
Thanx ehegagoka, This is exactly what I wanted to know.
If its not possible then I will ask my dev team to change the app accordingly.
Re: SQl Server 2005 to Oracle 10g Proc conversion [message #353925 is a reply to message #353212] Wed, 15 October 2008 11:52 Go to previous message
san_eins
Messages: 4
Registered: October 2008
Junior Member
Hi Rajat,

Thanx for your suggestion, but I've been working on oracle platform for the past 7 yrs, I just wanted to check whether, there is any enahancement in oracle 10g to do it.
Previous Topic: Synonyms, Execute Immediate, ***on another user for another user***
Next Topic: ORA-00936 when trying to insert
Goto Forum:
  


Current Time: Sat Dec 10 18:30:52 CST 2016

Total time taken to generate the page: 0.07397 seconds