Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Unhappy Unanswered: check for tables, and if they do exist, delite them, before creating new

    Hello, I need to fix my proc, its not working:
    The proc has to check for tables, and if they do exist, delite them, before creating new

    Its throing an error
    ORA-00942: table or view does not exist

    Not sure what else to do ?

    Please help


    ( startDate in varchar2, endDate in varchar2 ) as

    ddl_str varchar2(4000);
    sdt varchar2(20);
    edt varchar2(20);

    begin

    sdt := startdate;
    edt := endDate;

    begin
    a.drop_tlb; --drop table proc

    exception
    when no_data_found then
    null;
    when others
    then
    dbms_output.put_line('when other exception:'||SQLCODE);
    raise;

    end;

    begin

    dbms_output.put_line( 'Start Date = ' || sdt || ' End Date = ' || edt);
    dbms_output.put_line( 'Start at ' || to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'));

    ddl_str := 'create table a.Art as
    select * from a.ctrblist a
    where a.cxtype = 225
    and a.EXSTRTDATE >= ''' || sdt || ''' and a.EXSTRTDATE <= ''' || edt || ''' ;
    ddl_exec(ddl_str);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table Creation Error');

    end;

    end;

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: check for tables, and if they do exist, delite them, before creating new

    you can check in USER_TABLES to know if the table exists

    hope this will help you

    Originally posted by oranewbee
    Hello, I need to fix my proc, its not working:
    The proc has to check for tables, and if they do exist, delite them, before creating new

    Its throing an error
    ORA-00942: table or view does not exist

    Not sure what else to do ?

    Please help


    ( startDate in varchar2, endDate in varchar2 ) as

    ddl_str varchar2(4000);
    sdt varchar2(20);
    edt varchar2(20);

    begin

    sdt := startdate;
    edt := endDate;

    begin
    a.drop_tlb; --drop table proc

    exception
    when no_data_found then
    null;
    when others
    then
    dbms_output.put_line('when other exception:'||SQLCODE);
    raise;

    end;

    begin

    dbms_output.put_line( 'Start Date = ' || sdt || ' End Date = ' || edt);
    dbms_output.put_line( 'Start at ' || to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'));

    ddl_str := 'create table a.Art as
    select * from a.ctrblist a
    where a.cxtype = 225
    and a.EXSTRTDATE >= ''' || sdt || ''' and a.EXSTRTDATE <= ''' || edt || ''' ;
    ddl_exec(ddl_str);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table Creation Error');

    end;

    end;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: check for tables, and if they do exist, delite them, before creating new

    Originally posted by oranewbee
    Hello, I need to fix my proc, its not working:
    The proc has to check for tables, and if they do exist, delite them, before creating new

    Its throing an error
    ORA-00942: table or view does not exist

    Not sure what else to do ?

    Please help


    ( startDate in varchar2, endDate in varchar2 ) as

    ddl_str varchar2(4000);
    sdt varchar2(20);
    edt varchar2(20);

    begin

    sdt := startdate;
    edt := endDate;

    begin
    a.drop_tlb; --drop table proc

    exception
    when no_data_found then
    null;
    when others
    then
    dbms_output.put_line('when other exception:'||SQLCODE);
    raise;

    end;

    begin

    dbms_output.put_line( 'Start Date = ' || sdt || ' End Date = ' || edt);
    dbms_output.put_line( 'Start at ' || to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'));

    ddl_str := 'create table a.Art as
    select * from a.ctrblist a
    where a.cxtype = 225
    and a.EXSTRTDATE >= ''' || sdt || ''' and a.EXSTRTDATE <= ''' || edt || ''' ;
    ddl_exec(ddl_str);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table Creation Error');

    end;

    end;
    do not DROP & CREATE.
    Just TRUNCATE the table.

  4. #4
    Join Date
    Oct 2003
    Location
    ny
    Posts
    23

    Re: check for tables, and if they do exist, delite them, before creating new

    Originally posted by anacedent
    do not DROP & CREATE.
    Just TRUNCATE the table.

    Thank you, I think it will work

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

    using pl/sql u can use the USER_TABLES view. U can also use the TRUNCATE, but then u have to be aware of any foreign keys from or to that table.
    Also u can use the exception handling. In that case u don't have to check for the existence of the table.
    Like this:

    procedure droptables ......
    is
    <any declarations>
    my_exception exception;
    pragma exception_init (-942, my_exception);
    begin
    begin
    drop table;
    exception
    when my_exception
    then
    null;
    when others
    dbms_output.put_line('Error');
    end;
    <rest of the code>
    end;

    If u need any explanation here, post a reply or leave a message (after the beep)

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

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

    one correction:
    pragma exception_init (-942, my_exception);

    should be:
    pragma exception_init (my_exception,-942);

    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
  •