MySQL Forums
Forum List  »  Oracle

Re: How to convert Oracle bulk collection, table of records into mysql
Posted by: Roland Bouman
Date: February 12, 2006 07:29AM

Sultana Jahan Rini wrote:
> convert into mysql. Please advise me and treat it
> as urgent...

Advise, yes. Urgent...well, this is not a helpdesk. It's weekend for me too you know. I'll do my best, yes?

Some general points:

1) mysql does not have the %type syntax. I just used mockup datatypes, substitute where appropriate
2) the bulk collect thingie is not supported in mysql (also see next point). However, a plain cursor should do the job equally well
3) MySQL does not have collections or arrays (PLSQL 'Tables') like oracle. This means you should crack open the ep_calllimit(); procedure and see if you can work around how things are now (you are passing it a collection now)
4) You have some unreferenced variables: p_lcr and p_doroutefailure. Something I missed?
5) Look at the comments inside what I made of you proc


CREATE PROCEDURE SP_test (
IN p_routedefid int unsigned
, IN p_rateareacode varchar(10)
, IN p_lcr varchar(10)
, IN p_doroutefailure varchar(10)
, IN p_sessionid int unsigned
, OUT p_returnrouteid integer unsigned
)
BEGIN
DECLARE v_routeid int unsigned;
DECLARE v_first_routeid int unsigned;
DECLARE v_epid_type varchar(10);
DECLARE v_areacode varchar(10);
DECLARE v_calllimit int unsigned;

DECLARE v_done bit default FALSE;

DECLARE CURSOR csr_route FOR
SELECT route_id, ep_id
FROM route
WHERE route_def_id = p_routedefid
AND area_code = p_rateareacode
ORDER BY (delta_t/delta_r) ASC
;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_done := TRUE
;
--
-- not really sure what to use
-- instead of value error.
-- in fact, I think MySQL would just
-- coerce or covert a value that would
-- raise value_error in oracle.
-- so, here, we just mark the return. Always.
--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET p_returnrouteid := -1
;


OPEN csr_route;
FETCH csr_route
INTO v_route_id
, v_epid_type
;
IF NOT v_done THEN
v_first_route_id := v_route_id;
REPEAT
--
-- todo:
-- Not sure what to do here
-- what is this proc supposed to do?
--
CALL ep_calllimit();
--
-- end of todo
--
IF v_calllimit = 1 THEN
--
-- no nextval in mysql
-- just use ddl to make this
-- an auto-increment column
--
INSERT INTO table_2 (
route_id
, epid_type
, sessionid
)
VALUES (
v_route_id
, v_epid_type
, v_sessionid
);
COMMIT;
SET p_returnrouteid := v_first_routeid;
END IF;

FETCH csr_route
INTO v_route_id
, v_epid_type;
UNTIL v_done END REPEAT;
END IF;
CLOSE csr_route;

END;


Let me know if this got you started.

kind regards

Roland.

Options: ReplyQuote


Subject
Views
Written By
Posted
43866
January 03, 2005 10:56PM
25653
February 10, 2005 05:20PM
12648
August 26, 2005 03:28AM
11297
March 09, 2006 06:57AM
10595
March 09, 2006 02:09PM
7853
June 20, 2006 06:42PM
6309
January 23, 2006 10:37AM
5199
January 23, 2006 02:26PM
Re: How to convert Oracle bulk collection, table of records into mysql
17882
February 12, 2006 07:29AM
4692
September 30, 2006 01:18AM
5146
February 06, 2007 12:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.