5 Replies Latest reply: Jan 3, 2013 12:04 PM by user13356913 RSS

    Migrating from SQL Server to Oracle - Emulating SQL Server databases

    user13356913
      I want to offer Oracle connectivity from an ADO.NET application that was developed for SQL Server. The application accesses multiple databases, each having the same schema but each storing different data that is confidential to the users of each database. I’ve been reading up on Oracle but have not yet found a way to group tables, views, etc. in a way that would emulate the ‘databases’ of SQL Server.

      The application currently uses the SQL ‘CREATE DATABASE’ command to generate each database and then populates each with the appropriate tables. However, CREATE DATABASE appears to work differently with Oracle, creating a new instance rather than a new ‘internal database’. I’m wondering if CREATE TABLESPACE would give me the functionality I’m looking for, if it were subsequently possible to refer to a specific table within a specific tablespace.

      I guess it comes down to whether it’s possible to access specific groups of tables, views, etc. within Oracle (e.g. Groups A, B and C, each having relational tables T1, T2 and T3, and be able to access and update data within any specific table, say, B-T2), while also being able to limit access to any of the Groups.

      Would ODP.Net offer advantages over the System.Data.OracleClient for this architectural problem?

      Any thoughts on how best to proceed would be most welcome. Thanks in advance for any ideas you might have.
        • 1. Re: Migrating from SQL Server to Oracle - Emulating SQL Server databases
          636190
          Yes, "create database" is not what you are looking for. Emulate in this situation is not what you will be getting. The fundamental architectures are to dissimiliar.
          Oracle has the concept of "schema" (i.e. kind'a sorta' like the concept of 'owner'). "Schema" in conjunction with "Roles" will get you what you are looking for. Tablespaces do give you an additional level of control but they are more about the DBA side of the business. They are not much help when it comes to user/schema access.

          Yes, it is possible to "access specific groups...." IMHO, Oracle provides much more granular control over access than what SQL Server provides.

          Advantage of ODP.NET stack over Microzoft's stack for this architectural problem? Short answer: NO. Longer answer: I would not look at this a problem. It will just take some learning on your part.
          From the perspective of flexibility, features, options and support: YES.

          r,
          dennis
          • 2. Re: Migrating from SQL Server to Oracle - Emulating SQL Server databases
            user13356913
            Hello Dennis.

            Thanks for your prompt response, following which I've been reading-up on Schemas and Roles, and started
            experimenting with Oracle Database 10g Express Edition. I created a new user, 'TestUser', with Connect,
            Resource, Create Table and Create View privelages, and can open and close the connection okay. However,
            the System.Data.OracleClient throws the following exception at the command.ExecuteNonQuery statement:

                 "ORA-02420: missing schema authorization clause"

            Here's the code I used...

            Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim CString As String = "Data Source=XXXXXXX;User ID=TestUser;Password=pw;"
            Dim sql As String = ""
            sql += "CREATE SCHEMA PseudoDB AUTHORIZATION TestUser;"
            sql += "CREATE TABLE PseudoDB.Names(Name VARCHAR(30) PRIMARY KEY NOT NULL, First VARCHAR(30) NULL);"
            Try
            Using con As New OracleClient.OracleConnection(CString)
            con.Open()
            Dim command As New OracleClient.OracleCommand(sql, con)
            command.ExecuteNonQuery()
            con.Close()
            MsgBox("ok")
            End Using
            Catch ex As Exception
            MsgBox(ex.Message)
            End Try
            End Sub


            This should have created a schema called 'PseudoDB' associated with TestUser, then added to PseudoDB a simple
            table called 'Names'. The sql follows that of http://docs.oracle.com/javadb/10.4.2.1/ref/rrefsqlj31580.html.

            Clearly I've more to learn about Authorization but if there's anything obvious I'm missing, please let me know.

            Once past the exception error I think I should be able to add additional tables to the schema, apply any relational
            logic needed between the tables, and replicate this for any number of PseudoDB's associated with TestUser. Any
            further authorization changes would be handled by loging on as the DBA and changing the privelages for TestUser.
            Am I understanding this correctly?

            Thanks again for all your help, Dennis.

            Edited by: user13356913 on Nov 28, 2012 3:21 PM
            • 3. Re: Migrating from SQL Server to Oracle - Emulating SQL Server databases
              orafad
              user13356913 wrote:
              Hello Dennis.

              Thanks for your prompt response, following which I've been reading-up on Schemas and Roles, and started
              experimenting with Oracle Database 10g Express Edition. I created a new user, 'TestUser', with Connect,
              Resource, Create Table and Create View privelages, and can open and close the connection okay. However,
              Please stay away from pre-defined roles (should be documented in Security guide, I believe).
              Use create session privilege and other specific privileges (such the ones listed for table and view) instead.

              This should have created a schema called 'PseudoDB' associated with TestUser,
              No. That's another difference in concept and implementation. Oracle does not implement user - schema realtionship the same way as MS SqlServer (aka user-schema separation). Username = schema name.

              Lookup the syntax:
              http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm#SQLRF01313

              Note also that you could remove the first ; and just list object create statements in the same create schema command.

              Edited by: orafad on Nov 29, 2012 2:05 AM
              • 4. Re: Migrating from SQL Server to Oracle - Emulating SQL Server databases
                user13356913
                Hello orafad,

                Thanks for your comments and, yes, you were right, I did find the warning against using pre-defined roles in the Security Guide.

                I initially found your comment about Oracle having 'user-schema separation' confusing, as http://docs.oracle.com/javadb/10.4.2.1/ref/rrefsqlj31580.html gives examples of the syntax:

                CREATE SCHEMA { [ schemaName AUTHORIZATION user-name ] | [ schemaName ] | [ AUTHORIZATION user-name ] }

                This suggested that a single user-name could have several schemaNames associated with it, each of which I equated to being a ‘pseudo database’. The syntax you referenced omits the schemaName, hence my confusion.

                Further reading about Schema Objects in the ‘Oracle Database Concepts’ document for Oracle 10g revealed “A schema is owned by a database user and has the same name as that user. Each user owns a single schema.” So, if a user can only have one schema, then presumably the schemaName in the above syntax just gives you a way of hiding your user-name from other users to whom you granted access (presumably they access any table associated with the schema by simply using a schema.table reference).

                So for my problem of trying to create multiple clones of the same database-structure within Oracle, it appears I create a user with its single schema, populate the schema with the necessary tables etc., grant appropriate access to other users, and essentially consider the schema as being the pseudo-database. Presumably I’d then need to create a new user whenever I want to create/populate another pseudo-database for a different set of data. Is this correct?

                Thanks again… and sorry to be asking such fundamental questions. Meanwhile, it's back to the Oracle documentation for me!
                • 5. Re: Migrating from SQL Server to Oracle - Emulating SQL Server databases
                  user13356913
                  Sorry for the delay closing-out this thread but I work from a rural location and we lost our internet service for the two-weeks prior to the holidays. Now back on-line so let's wrap-up.

                  Oracle and SQLServer are now both being accessed from my ADO.Net application, so a big thanks to all who've helped me with this first use of the forum. Hopefully these final comments might help someone else in turn...

                  In migrating from SQLServer to Oracle I initially found it hard to stop thinking about accessing multiple databases on a single dataserver and start thinking more about a single instance of a database that supports muliple users, any one of which can be assigned the same schema. While I had used the SQL CREATE command to produce miltiple databases within SQLServer, it serves a completely different purpose for Oracle, creating an entirely new instance that I did not require.

                  Other differences that I ran in to included Oracle utilizing 'sequences' to autoincrement identities, whereas SQLServer lets you specify the identify while creating a table (here SQLServer may simplify declaration but Oracle's approach may be more powerful, e.g. if the same identity applies across multiple tables). Also ADO.NET command parameters use an '@' prefix in SQLServer but ':' for Oracle. But by-and-large few changes were required to my SQL to get up and running with Oracle (I found the 'Oracle Database SQL Reference' useful but no substitute for logging into Oracle and just trying each string in the 'SQL Commands' area).

                  Clearly I'm no database expert and I've barely scratched the surface with Oracle, but after logging in through the 'Database Home Page' I've found Oracle to offer a very 'clean' and logical interface that made it easy to move around the application and get a 'feel' for its organization and what it can do before digging furhter into the documentation. The 'Object Browser' is also a great way to quickly view and edit your various tables, views, sequences, triggers, etc., and ditto your data. In short, I think I'm going to like Oracle.

                  Thanks again and bye for now.