SQl Server 2005 to Oracle 10g Proc conversion [message #352994] |
Fri, 10 October 2008 07:55  |
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 #353014 is a reply to message #352994] |
Fri, 10 October 2008 10:14   |
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   |
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   |
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?
|
|
|
|
|
|