Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2013
    Posts
    21

    Unanswered: Truncate table in oracle?

    Hello every1,

    i want to truncate the table before insert any date into that table in a oracle stored procedure.. i know we use TRUNCATE TABLE TABLENAME directly in sql server. but how to achieve this in oracle..i want to truncate table for performance reason . any suggestions please

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Oracle has a very strange concept: they actually document their SQL syntax. It's a brand new thing - only been there for about 30 years - so you probably have never heard of it: it's called "the manual".

    Here it is: http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The SQL Language is an international standard & NOT proprietary to Micro$oft (or Oracle).

    > i know we use TRUNCATE TABLE TABLENAME directly in sql server.

    so it works the same for Oracle DB as sql server
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by anacedent View Post
    The SQL Language is an international standard & NOT proprietary to Micro$oft (or Oracle).

    > i know we use TRUNCATE TABLE TABLENAME directly in sql server.

    so it works the same for Oracle DB as sql server

    i agree with you . im not talking about SQL here .

    create or replace procedure trunemp(
    empid integer)
    begin
    truncate table emp;
    insert into emp(....)

    end trunemp;

    / this gives me an error .

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can not do DDL directly inside of an PL/SQL procedure.
    you must (ab)use EXECUTE IMMEDIATE do avoid the error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by anacedent View Post
    You can not do DDL directly inside of an PL/SQL procedure.
    you must (ab)use EXECUTE IMMEDIATE do avoid the error.


    AWE..it works....THANK YOU VERY MUCH..(:

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @u0_java
    I think what anacedent was getting at is that using that statement in this context is a bad design and you shouldn't be using it.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How would you truncate a table from PL/SQL, then?

    Besides, anacedent loves ab(using) (ab)use (other resources available as well, Google knows that).

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    truncate always does an explicit commit. You can simply do a

    delete from emp;
    Whichwill delete all the rows. Not as fast as a truncate but the same result.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by beilstwh View Post
    truncate always does an explicit commit. You can simply do a

    delete from emp;
    Whichwill delete all the rows. Not as fast as a truncate but the same result.
    yup.. delete from emp will also gives me the same result but i want to truncate the table for better performance. so truncate table will execute faster than delete..

    Thank you for sharing your knowledge with us.

Posting Permissions

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