Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    ny
    Posts
    4

    Unanswered: create st. proc that drops and then creates a table

    Hello, I'm trying to create st. proc that drops and then creates a table
    each time I'm executing it, it tells me that table doesn't exist.

    how to avoid this problem and be sure that table was re created ?

    CREATE OR REPLACE procedure test_proc
    IS

    m_Statement varchar2(4000):='CREATE TABLE Test1 as select * from apadmin.test1 ';

    BEGIN

    EXECUTE IMMEDIATE 'Drop table test1';
    EXECUTE IMMEDIATE m_Statement;

    END;


    thank you

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

    what table does not exist ?
    Be sure you have select privileges on the source table granted directly to the user creating the procedure

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

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

    drop table command - error

    Originally posted by evanhattem
    hi,

    what table does not exist ?
    Be sure you have select privileges on the source table granted directly to the user creating the procedure

    Hope this helps

    Thank you for respond,
    procedure doesn't work when I'm trying to execute it for the second time.
    For the drop table command - error
    ORA-00942: table or view does not exist

    even table was not found, it doesn't want to continue
    is there an exception that I can use to skip drop , if table was not found?

    Thank you

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

    yes there is, it's called exception handling. You should use an extra begin, exception end around the drop code, like

    create procedure x is
    ...
    begin
    .....
    begin
    drop_tables;
    exception
    when others then
    null
    end;
    recreate_tables;
    exception
    ...
    end;

    hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

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

    Talking

    thank you, it did helped

Posting Permissions

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