This discussion is archived
14 Replies Latest reply: Apr 23, 2013 12:35 AM by Karthick_Arp RSS

Approach for creation of a Table afer checking whether it is existing

EV259 Newbie
Currently Being Moderated
All,

Any other ways or a simple approach to achieve this below one.

declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception
when others then
vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception
when others then
dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;

Thanks,
  • 1. Re: Approach for creation of a Table afer checking whether it is existing
    nkvkashyap Explorer
    Currently Being Moderated
    Make a NOTE Group functions (count(1),........) will never throw an exception

    What is the requirement, if table exists you want to drop and create the table again? if so, we will have the data in the tbale rigth, the data will be lost?

    Please Elaborate.....

    Edited by: nkvkashyap on Apr 22, 2013 12:53 AM
  • 2. Re: Approach for creation of a Table afer checking whether it is existing
    jeneesh Guru
    Currently Being Moderated
    First of all, why are you creating tables dynamically?

    Is not your datamodel in place before you start coding?
  • 3. Re: Approach for creation of a Table afer checking whether it is existing
    BluShadow Guru Moderator
    Currently Being Moderated
    What is it you are actually trying to achieve and why?

    Dropping and creating tables at runtime is an indication of poor database design. There should be no need to do that.
    So, describe your business needs and we can suggest the best way to go about it; but certainly dropping and creating tables at runtime is not likely to be a solution.
  • 4. Re: Approach for creation of a Table afer checking whether it is existing
    BEDE Explorer
    Currently Being Moderated
    I think you forgot one thing: the table's owner...
    There may be even several tables having that name in various schemas in the database. And, at the same time, that execute immediate would create the table in the schema of the user that executes the code.
    So far as I know, it's not good practice creating database tables dynamically. Tables created dynamically may only be afterwards referred in dynamic SQL, which leads to poor performance. Moreover, how would you have foreign keys and what makes a database truly relational if you are creating tables dynamically? As to data validation and integrity you won't be better off then in case you are using a bunch of excel files (that may also be created programatically). Then, why the pain of having a relational database?
    Instead of creating tables dynamically, try a different design. I'd rather add one or, eventually more columns to a table, that would store the information which you wish to store in the names of the tables you wish to create dynamically (that's what I imagine you wish to do).

    Edited by: BEDE on Apr 22, 2013 9:58 AM
  • 5. Re: Approach for creation of a Table afer checking whether it is existing
    Karthick_Arp Guru
    Currently Being Moderated
    I will ask the same question i asked in this thread of yours (Creation of Schema in Oracle after checking

    Why do you want to do DDL in PL/SQL? Is the table that you are creating is a intermediate table in your process? If that the case its more of an SQL Server approach. In oracle you don't need such intermediate tables to process your data. SQL in oracle is powerful enough to do things in single shot.
  • 6. Re: Approach for creation of a Table afer checking whether it is existing
    EV259 Newbie
    Currently Being Moderated
    Thanks each and every one for your replies, but I beg your pardon to trouble you all.

    My requirement is, we have an erp system which is on sql server. Now all the scripts written in sql server needs to re write in Oracle. In the code base , I have couple of folders which contains creation of schemas, tables, indexes, views and stored procedures etc.

    I have given an approach for a table creation as below to my boss and he told me, this is not the correct way just check whether the table is exists or not, then only create it.

    My approach is placed the below sql statements in .sql file in the folder. I hope this approach is correct when we are going to create a new table.

    ---------------------
    alter table admin.tbl_pkg drop primary key cascade (-- this statement will give the error as the table does not exist for the first time )
    /

    drop table admin.tbl_pkg cascade constraints (-- this statement will give the error as the table does not exist for the first time )
    /

    create table admin.tbl_pkg(
    id number(18,0) not null,
         createdby varchar2(256) not null,
         createdat date not null,
         modifiedby varchar2(256) ,
         modifiedat date
         ))
    /

    alter table admin.tbl_pkg add (constraint pk_tbl_pkg primary key (id) )
    /

    drop sequence admin.pkg_seq (-- this statement will give the error as the table does not exist for the first time )
    /

    create sequence admin.pkg_seq
    start with 1
    increment by 1
    nocache
    /
    ---------------------------


    Please suggest me if i am going wrong and correct me. And in my first post I have just tried to check the existence of the table and creating it. Once again sorry to trouble you all.

    Thanks,
  • 7. Re: Approach for creation of a Table afer checking whether it is existing
    Manik Expert
    Currently Being Moderated
    I guess I showed you a template to proceed, the template is helpful in database scripts execution..

    Creation of Schema in Oracle after checking

    Checks for table emp before drop, if does not exists, it will not throw an error.

    Cheers,
    Manik.
  • 8. Re: Approach for creation of a Table afer checking whether it is existing
    BluShadow Guru Moderator
    Currently Being Moderated
    968217 wrote:
    Thanks each and every one for your replies, but I beg your pardon to trouble you all.

    My requirement is, we have an erp system which is on sql server. Now all the scripts written in sql server needs to re write in Oracle. In the code base , I have couple of folders which contains creation of schemas, tables, indexes, views and stored procedures etc.

    I have given an approach for a table creation as below to my boss and he told me, this is not the correct way just check whether the table is exists or not, then only create it.

    My approach is placed the below sql statements in .sql file in the folder. I hope this approach is correct when we are going to create a new table.

    ---------------------
    alter table admin.tbl_pkg drop primary key cascade (-- this statement will give the error as the table does not exist for the first time )
    /

    drop table admin.tbl_pkg cascade constraints (-- this statement will give the error as the table does not exist for the first time )
    /

    create table admin.tbl_pkg(
    id number(18,0) not null,
         createdby varchar2(256) not null,
         createdat date not null,
         modifiedby varchar2(256) ,
         modifiedat date
         ))
    /

    alter table admin.tbl_pkg add (constraint pk_tbl_pkg primary key (id) )
    /

    drop sequence admin.pkg_seq (-- this statement will give the error as the table does not exist for the first time )
    /

    create sequence admin.pkg_seq
    start with 1
    increment by 1
    nocache
    /
    ---------------------------


    Please suggest me if i am going wrong and correct me. And in my first post I have just tried to check the existence of the table and creating it. Once again sorry to trouble you all.

    Thanks,
    That approach looks fine to me. Yes, you expect errors from the dropping of objects if they don't exist, but that's part of database creation scripts that you may want to run more than once during testing. It's expected and not a problem. You could also split out the dropping and the creating into two seperate scripts, and then just run the dropping script if you know the tables already exist (like when you're testing) but not worry about running it if it's a new installation.
  • 9. Re: Approach for creation of a Table afer checking whether it is existing
    EV259 Newbie
    Currently Being Moderated
    Thank you Blu for your suggestion and will go and implement the same..!!

    Here one more problem i have related to the creation of schema with the sql statement. Can you please help with this.

    As said in the above sql statements the ADMIN schema need to be created.

    Thanks
  • 10. Re: Approach for creation of a Table afer checking whether it is existing
    BluShadow Guru Moderator
    Currently Being Moderated
    968217 wrote:
    Thank you Blu for your suggestion and will go and implement the same..!!

    Here one more problem i have related to the creation of schema with the sql statement. Can you please help with this.

    As said in the above sql statements the ADMIN schema need to be created.
    What's the problem?

    A schema relates to a user, so to create one you use the CREATE USER ... statement when logged in as a user with appropriate privileges to do so (e.g. the SYS user).
  • 11. Re: Approach for creation of a Table afer checking whether it is existing
    Karthick_Arp Guru
    Currently Being Moderated
    968217 wrote:
    Thank you Blu for your suggestion and will go and implement the same..!!

    Here one more problem i have related to the creation of schema with the sql statement. Can you please help with this.

    As said in the above sql statements the ADMIN schema need to be created.

    Thanks
    Your approach of using .SQL file is fine and that is how most of us do it.

    And to create a new schema you need to use [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm#SQLRF01503]CREATE USER command.
  • 12. Re: Approach for creation of a Table afer checking whether it is existing
    rp0428 Guru
    Currently Being Moderated
    >
    I have given an approach for a table creation as below to my boss and he told me, this is not the correct way just check whether the table is exists or not, then only create it.
    >
    Your boss is WRONG!

    The method you use to tell them they are wrong will likely vary depending on whether you are an employee or a contractor. If you are an employee tell them nicely. If you are a contractor, then . . . ;)

    Here is the core issue: just because a table has the correct name does NOT mean it has the same defintion as the table you are wanting to create.

    You MUST create a table of the proper structure.

    There are only two ways to ensure that the resultant table has the correct structure:

    1. drop any existing table and create the table using DDL with the required structure.

    2. examine the DDL of the exising table to make sure it matches the DDL of the table you want to create.

    You are asking for SERIOUS future potential problems if you just assume that a table of the correct name has the same, IDENTICAL IN EVERY WAY, structure of the table you want to create.

    It is MANDATORY that you have total control over the objects used in your database.

    Only a rank amateur would make a mistake like your boss is suggesting.

    Tell your boss, in an appropriate way, that you are a professional and that professionals don't make amateur mistakes like they are proposing.
  • 13. Re: Approach for creation of a Table afer checking whether it is existing
    EV259 Newbie
    Currently Being Moderated
    Finally convinced with my .sql approach... and they are in discussion with his boss.

    I am very thankful to each and every one for your kind help on this.

    Thanks
  • 14. Re: Approach for creation of a Table afer checking whether it is existing
    Karthick_Arp Guru
    Currently Being Moderated
    968217 wrote:
    Finally convinced with my .sql approach... and they are in discussion with his boss.

    I am very thankful to each and every one for your kind help on this.

    Thanks
    Most of the time developers tend to end up make wrong decision just to please there boss. Its good to see that you have taken the time to convince your boss.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points