InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 19:01:46 GMT20http://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspx[quote][b]Lowell (11/21/2012)[/b][hr]if you want to do it yourself, which is another big job let me tell you, this data type equivalents link can help:[b][url]http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm[/url][/b]I've done it the other direction: take a single table in SQL server for example, and generate the equivilent DDL command for Oracle, but that's based on personal assumptions and naming conventions for Sequences/triggers.here's one example of my procs results:[code]CREATE TABLE [dbo].[TBCOUNTY] ( [COUNTYTBLKEY] INT IDENTITY(1,1) NOT NULL,[INDEXTBLKEY] INT NOT NULL,[STATE] VARCHAR(2) NOT NULL,[DESCRIP] VARCHAR(30) NOT NULL,[CODE] VARCHAR(3) NULL,[STATETBLKEY] INT NULL,[REGIONTBLKEY] INT NULL,[EDREGIONTBLKEY] INT NULL,[SPECDISTTBLKEY] INT NULL,CONSTRAINT [PK__TBCOUNTY__009508B4] PRIMARY KEY CLUSTERED (COUNTYTBLKEY),CONSTRAINT [FK__TBCOUNTY__INDEXT__68C86C1B] FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),CONSTRAINT [FK__TBCOUNTY__STATET__0A5513C6] FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),CONSTRAINT [FK__TBCOUNTY__REGION__3B6D4C6D] FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),CONSTRAINT [FK__TBCOUNTY__EDREGI__3C6170A6] FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),CONSTRAINT [FK_TBSPECDIST_SPECDISTTBLKEY] FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY))[/code]--Oracle equivalent:[code]CREATE TABLE CHANGE_ME.TBCOUNTY ( COUNTYTBLKEY NUMBER(10) NOT NULL,INDEXTBLKEY NUMBER(10) NOT NULL,STATE VARCHAR2(2) NOT NULL,DESCRIP VARCHAR2(30) NOT NULL,CODE VARCHAR2(3) NULL,STATETBLKEY NUMBER(10) NULL,REGIONTBLKEY NUMBER(10) NULL,EDREGIONTBLKEY NUMBER(10) NULL,SPECDISTTBLKEY NUMBER(10) NULL,CONSTRAINT PK__TBCOUNTY__009508B4 PRIMARY KEY (COUNTYTBLKEY),CONSTRAINT FK__TBCOUNTY__INDEXT__68C86C1B FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),CONSTRAINT FK__TBCOUNTY__STATET__0A5513C6 FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),CONSTRAINT FK__TBCOUNTY__REGION__3B6D4C6D FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),CONSTRAINT FK__TBCOUNTY__EDREGI__3C6170A6 FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),CONSTRAINT FK_TBSPECDIST_SPECDISTTBLKEY FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY));--CHANGE_ME.TBCOUNTY_SEQUENCECREATE SEQUENCE CHANGE_ME.TBCOUNTY_SEQUENCE START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 20 NOORDER;--CHANGE_ME.TBCOUNTY_IDENTITY--CREATE OR REPLACE TRIGGER CHANGE_ME.TBCOUNTY_IDENTITY BEFORE INSERT ON CHANGE_ME.TBCOUNTY FOR EACH ROWWHEN (NEW.COUNTYTBLKEY IS NULL )DECLARE DUMMY NUMBER; BEGIN SELECT TBCOUNTY_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL; :NEW.COUNTYTBLKEY:= DUMMY; END;/[/code][/quote]Thanks Lowell for your quick reply.SueTons.Wed, 21 Nov 2012 12:46:58 GMTSQLCrazyCertifiedhttp://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspxif you want to do it yourself, which is another big job let me tell you, this data type equivalents link can help:[b][url]http://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apa.htm[/url][/b]I've done it the other direction: take a single table in SQL server for example, and generate the equivilent DDL command for Oracle, but that's based on personal assumptions and naming conventions for Sequences/triggers.here's one example of my procs results:[code]CREATE TABLE [dbo].[TBCOUNTY] ( [COUNTYTBLKEY] INT IDENTITY(1,1) NOT NULL,[INDEXTBLKEY] INT NOT NULL,[STATE] VARCHAR(2) NOT NULL,[DESCRIP] VARCHAR(30) NOT NULL,[CODE] VARCHAR(3) NULL,[STATETBLKEY] INT NULL,[REGIONTBLKEY] INT NULL,[EDREGIONTBLKEY] INT NULL,[SPECDISTTBLKEY] INT NULL,CONSTRAINT [PK__TBCOUNTY__009508B4] PRIMARY KEY CLUSTERED (COUNTYTBLKEY),CONSTRAINT [FK__TBCOUNTY__INDEXT__68C86C1B] FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),CONSTRAINT [FK__TBCOUNTY__STATET__0A5513C6] FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),CONSTRAINT [FK__TBCOUNTY__REGION__3B6D4C6D] FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),CONSTRAINT [FK__TBCOUNTY__EDREGI__3C6170A6] FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),CONSTRAINT [FK_TBSPECDIST_SPECDISTTBLKEY] FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY))[/code]--Oracle equivalent:[code]CREATE TABLE CHANGE_ME.TBCOUNTY ( COUNTYTBLKEY NUMBER(10) NOT NULL,INDEXTBLKEY NUMBER(10) NOT NULL,STATE VARCHAR2(2) NOT NULL,DESCRIP VARCHAR2(30) NOT NULL,CODE VARCHAR2(3) NULL,STATETBLKEY NUMBER(10) NULL,REGIONTBLKEY NUMBER(10) NULL,EDREGIONTBLKEY NUMBER(10) NULL,SPECDISTTBLKEY NUMBER(10) NULL,CONSTRAINT PK__TBCOUNTY__009508B4 PRIMARY KEY (COUNTYTBLKEY),CONSTRAINT FK__TBCOUNTY__INDEXT__68C86C1B FOREIGN KEY (INDEXTBLKEY) REFERENCES GMINDEX(INDEXTBLKEY),CONSTRAINT FK__TBCOUNTY__STATET__0A5513C6 FOREIGN KEY (STATETBLKEY) REFERENCES TBSTATE(STATETBLKEY),CONSTRAINT FK__TBCOUNTY__REGION__3B6D4C6D FOREIGN KEY (REGIONTBLKEY) REFERENCES TBREGION(REGIONTBLKEY),CONSTRAINT FK__TBCOUNTY__EDREGI__3C6170A6 FOREIGN KEY (EDREGIONTBLKEY) REFERENCES TBEDREGION(EDREGIONTBLKEY),CONSTRAINT FK_TBSPECDIST_SPECDISTTBLKEY FOREIGN KEY (SPECDISTTBLKEY) REFERENCES TBSPECDIST(SPECDISTTBLKEY));--CHANGE_ME.TBCOUNTY_SEQUENCECREATE SEQUENCE CHANGE_ME.TBCOUNTY_SEQUENCE START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 20 NOORDER;--CHANGE_ME.TBCOUNTY_IDENTITY--CREATE OR REPLACE TRIGGER CHANGE_ME.TBCOUNTY_IDENTITY BEFORE INSERT ON CHANGE_ME.TBCOUNTY FOR EACH ROWWHEN (NEW.COUNTYTBLKEY IS NULL )DECLARE DUMMY NUMBER; BEGIN SELECT TBCOUNTY_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL; :NEW.COUNTYTBLKEY:= DUMMY; END;/[/code]Wed, 21 Nov 2012 12:43:15 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspx[quote][b]Lowell (11/21/2012)[/b][hr][quote][b]SQLCrazyCertified (11/21/2012)[/b][hr]I need to create a sql database which works the same way in oracle....Basically, I have to mimic a database from Oracle to SQL server.I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.Thanks,SueTons.[/quote]you'll have to be a LOT more specific. do you want to take an existing Oracle database,and create it's equivalent in SQL server?There's a tool for that: SQL Server Migration Assistant for Oracle:[b][url]www.microsoft.com/en-us/download/details.aspx?id=28766[/url][/b]That will do the tables and views for you,creating the tables with teh best-matching data type equivalents.once it's in SQL, it's trivial to script out the objects and data.for all packages, procedures and functions, you will have to convert them manually...it's a +BIG+ job. some things have no equivilents, so you have to create functionalities that mimic whatever was being done instead.[/quote]Yes, Ttis is exactly what I am looking for. Thanks. LowellWed, 21 Nov 2012 12:42:11 GMTSQLCrazyCertifiedhttp://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspx[quote][b]SQLCrazyCertified (11/21/2012)[/b][hr]I need to create a sql database which works the same way in oracle....Basically, I have to mimic a database from Oracle to SQL server.I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.Thanks,SueTons.[/quote]you'll have to be a LOT more specific. do you want to take an existing Oracle database,and create it's equivalent in SQL server?There's a tool for that: SQL Server Migration Assistant for Oracle:[b][url]www.microsoft.com/en-us/download/details.aspx?id=28766[/url][/b]That will do the tables and views for you,creating the tables with teh best-matching data type equivalents.once it's in SQL, it's trivial to script out the objects and data.for all packages, procedures and functions, you will have to convert them manually...it's a +BIG+ job. some things have no equivilents, so you have to create functionalities that mimic whatever was being done instead.Wed, 21 Nov 2012 12:37:57 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1387581-1042-1.aspxI need to create a sql database which works the same way in oracle....Basically, I have to mimic a database from Oracle to SQL server.I have the DDL commands from Oracle, anyone have any experince converting data from Oracle to SQL. Please advise, very urgent.Thanks,SueTons.Wed, 21 Nov 2012 12:28:14 GMTSQLCrazyCertified