This content has been marked as final. Show 4 replies
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;
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.
The above statement does not create a new schema KARTHICK but it creates two table T1 and T2 in a single transaction.
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
Edited by: Karthick_Arp on Apr 22, 2013 12:28 AM
968217 wrote: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.
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')
EXECUTE ('CREATE SCHEMA [HRMS] AUTHORIZATION [dbo];');
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.
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]
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.
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.
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.