4 Replies Latest reply: Apr 22, 2013 3:26 AM by BEDE RSS

    Creation of Schema in Oracle after checking

    EV259
      All,

      My sincere apologies to post this here, but I did not find the answer anywhere.

      I have a task to convert some SQL Server Scripts to Oracle.

      The below is the sql server script to create the schema called HRMS after checking whether it is existing or not.

      ------

      IF NOT EXISTS (SELECT [schema_id] FROM [sys].[schemas] WHERE [name] = N'HRMS')
      BEGIN
      EXECUTE ('CREATE SCHEMA [HRMS] AUTHORIZATION [dbo];');
      END

      GO

      ------------------
      Can we have any similar kind of solution / statement in oracle. Please help me.

      When I read in the Oracle Docs, CREATE SCHEMA statement is just to bundle multiple sql statements to a single transaction.

      http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm


      2)
      -----------------
      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HRMS].[TBL_PMapping]') AND type in (N'U'))
      DROP TABLE [HRMS].[TBL_PMapping]
      GO
      -----------------

      This statement is to check whether the table called 'TBL_PMapping is exists or not and, if exists then drop it. Please suggest me a similar approach/ how to achieve the same with in Oracle.

      First need to check whether the table exists or not, if exists then drop it other wise create it.

      I would request for your kind help in this. Thank you very much in advance.

      Thanks,
        • 1. Re: Creation of Schema in Oracle after checking
          Manik
          Check as template only, you need to have proper execption handling unlike my example below.. :)

          All it does is checks for emp table. If exists then drops else does logging (object not found).
          set serveroutput on
          BEGIN
             EXECUTE IMMEDIATE 'drop table emp';
          EXCEPTION
             WHEN OTHERS THEN
                IF SQLCODE = -942 THEN
                   -- log this somewhere
                   DBMS_OUTPUT.put_line ('Object not found');
                ELSE
                   --proper exception handling
                   RAISE;
                END IF;
          END;
          Cheers,
          Manik.
          • 2. Re: Creation of Schema in Oracle after checking
            _Karthick_
            Why are you creating a SCHEMA in a procedure? Does not look like the correct way to do it.

            I am not an SQL Server person but i think CREATE SCHEMA statement in SQL server actually creates a new schema. But in oracle to create a new schema (or user) you need to use CREATE USER command.

            CREATE SCHEMA is used to create multiple table, views and grants in a single transaction. To do that the user( or schema) must already exist.

            Here is an example.
            Connected to:
            Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
             
            SQL> select user from dual;
             
            USER
            ------------------------------
            KARTHICK
             
            SQL> create schema authorization karthick
              2  create table t1(no integer)
              3  create table t2(no integer);
             
            Schema created.
             
            SQL> select table_name from user_tables where table_name in ('T1', 'T2');
             
            TABLE_NAME
            ------------------------------
            T1
            T2
            The above statement does not create a new schema KARTHICK but it creates two table T1 and T2 in a single transaction.

            Edited by: Karthick_Arp on Apr 22, 2013 12:28 AM
            • 3. Re: Creation of Schema in Oracle after checking
              BluShadow
              968217 wrote:
              All,

              My sincere apologies to post this here, but I did not find the answer anywhere.

              I have a task to convert some SQL Server Scripts to Oracle.

              The below is the sql server script to create the schema called HRMS after checking whether it is existing or not.

              ------

              IF NOT EXISTS (SELECT [schema_id] FROM [sys].[schemas] WHERE [name] = N'HRMS')
              BEGIN
              EXECUTE ('CREATE SCHEMA [HRMS] AUTHORIZATION [dbo];');
              END

              GO

              ------------------
              Can we have any similar kind of solution / statement in oracle. Please help me.

              When I read in the Oracle Docs, CREATE SCHEMA statement is just to bundle multiple sql statements to a single transaction.

              http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6014.htm


              2)
              -----------------
              IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HRMS].[TBL_PMapping]') AND type in (N'U'))
              DROP TABLE [HRMS].[TBL_PMapping]
              GO
              -----------------

              This statement is to check whether the table called 'TBL_PMapping is exists or not and, if exists then drop it. Please suggest me a similar approach/ how to achieve the same with in Oracle.

              First need to check whether the table exists or not, if exists then drop it other wise create it.
              Oracle is NOT SQL Server, and doesn't work like SQL Server. It may be acceptable in SQL Server to drop and create tables on-the-fly, but in Oracle it's considered poor database design. There shouldn't be any need to drop and recreate tables at run time, and it would likely drop dependencies from other database objects or triggers, constraints, indexes, grants etc.

              If you need a "temporary table" in Oracle, you create a Global Temporary Table just once and use it for your session. This is a special kind of table where the data in it is only visible to the session that inserts it (multiple sessions only see their own data), and that data is automatically removed upon commit or when the session ends, depending on how you configure the table at the time of creation.
              • 4. Re: Creation of Schema in Oracle after checking
                BEDE
                Please excuse me, but seeing the avalanche of posts on dynamically creating DB objects and not only objects, even schemas, I just can't help!
                Why not dynamically create a whole Oracle database with all the stuff one may imagine?
                Do we really have some well-defined problems we wish to address and find technical solutions to? Or is it that we don't know a thing about what is actually needed and simply wish to find a "flexible" way to do whatever one may ask at a later date?
                Flexibility like dynamic SQL usually leads to poor performance, difficult maintenance and, when going overboard with it, to having truckloads of ill-structured data, whith inconsistencies, out of which you one may be unable to make up how things really are...
                Any flexible system requires much effort from the user in order to avoid errors. So, what would be the best trade-off between flexibility, performance and data integrity? That's a design issue for any system.