Oracle Database Discussions

Hi,

We're using OMWB to to convert a Sybase database 11.9.2 to Oracle.

We are having some issues with the migration script (Create.sql) generated from OMWB ...


I) Handling of Temporary Tables in SPs

Sybase SPs allow for creating and dropping Temporary tables within the SP.

Situation:
2 Sybase SPs create temporary tables with the same name #temp1 ... different defns ... SP1 creates #temp1 with 2 columns whereas SP2's #temp1 table has 3 columns.

Oracle passes the DDL for creation of Temp tables in SP1 and SP2 to a different section in the migration script AND handles the table name conflict situation well by creating TT_TEMP1 (2 cols) as the temp table in SP1 and TT_TEMP1_1 (3 cols) as the temp table in SP2.

While transforming the Sybase SP code to Oracle SPs, procedure SP1 gets transformed fine whereas the procedure SP2 is not transformed correctly ...
it continues to use the temporary table TT_TEMP1 (instead of TT_TEMP1_1).

<SP2 code on Oracle: indicative>

...
/* CHANGING SELECT INTO TT_TEMP_1 */
/* WITH SELECT INTO TT_TEMP1 */
/* SPCONV-ERR PASSED CREATE TABLE to DDL file */

DELETE FROM TT_TEMP1;
INSERT INTO TT_TEMP1 -- Incorrect as TT_TEMP1: 2 cols
SELECT Col1, Col2, Col3
FROM ...
;
...


Is there any way to fix this problem cleanly within OMWB i.e. without writing a
dirty search and replace function after the migration script has been generated?


II) Sometimes the generated sql contains blank lines in between a regular sql statement.

e.g.

1) In the ddl for creation of Temporary Tables encountered in Sybase SPs.

...


CREATE GLOBAL TEMPORARY TABLE TT_TEMP
AS
SELECT
>
Col1, DECODE(Col2,...)
....
;

...


2) In the definition of a PK addition (say)

...

ALTER TABLE T_ABCD ADD PRIMARY KEY
>
(Col1);

...


This seems to happen all the time in the generated migration scripts. Is there any alternative to using some scripts to clean up the generated code ?


Thanks
Shankar S.