Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Red face Unanswered: ora error - table exist

    Trying to execute proc - which drops, and creates table with startdate, enddate param

    First time its run - no prob
    Second time its run - error msg - table already exist

    How to be sure that table was dropped and re created eactime I
    run the proc

    Thank you

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: ora error - table exist

    In your procedure you can check a cursor to verify if the table exists before and after your drop.

    Cursor c_table_check (p_tablename varchar) is
    Select 'Y'
    From All_Tables
    Where Tablename = p_tablename;

    lv_exists char(1);

    begin

    -- check if table exists

    Open c_table_check('TABLENAME');
    Fetch c_table_check into lv_exists;

    If c_table_check%NotFound Then
    -- Table doesn't exist
    dbms_output.put_line('table doesn't exist');
    Close c_table_check;
    End If;

    If lv_exists != null then
    dbms_output.put_line('table exist');
    end if;

    Close c_table_check;

    Execute Immediate 'Drop Table '||TABLENAME;

    lv_exists := null;

    Open c_table_check('TABLENAME');
    Fetch c_table_check into lv_exists;

    If c_table_check%NotFound Then
    -- Table doesn't exist
    dbms_output.put_line('table doesn't exist');
    Close c_table_check;
    End If;

    If lv_exists != null then
    dbms_output.put_line('table exist');
    end if;

    Close c_table_check;


    Best way would be to put the check code into a function, and then call the function each time you needed it.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    more simple is the following code:

    create procedure X is
    ....
    e_table_not_exist execption;
    pragma exception_init(e_table_not_exist,-942);
    begin
    ....
    begin
    drop_tables;
    exception
    when e_table_not_exist
    then
    null;
    when others
    then
    dbms_output.put_line('when other exception:'||SQLCODE);
    raise;
    end;
    recreate tables;
    exception
    ....
    end;

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •