Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Truncate tables in a fixed order from a cursor

    Hi,

    I have a requirement as:

    A cursor will call tables one by one. Then I have to truncate these tables in a particular order because of dependencies.

    In simpler words I want the cursor to call those tables in a particular order always. How can I gaurantee that?

    Thanks..

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ORDER BY clause?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because of dependencies (foreign key constraints, I presume), you won't be able to truncate those (parent) tables.
    Code:
    SQL> create table test_dept
      2  (deptno number constraint pk_dpt primary key,
      3   dname  varchar2(20));
    
    Table created.
    
    SQL> create table test_emp
      2  (empno number,
      3   deptno number constraint fk_emp_dept references test_dept(deptno),
      4   ename  varchar2(20));
    
    Table created.
    
    SQL> truncate table test_emp;
    
    Table truncated.
    
    SQL> truncate table test_dept;
    truncate table test_dept
                   *
    ERROR at line 1:
    ORA-02266: unique/primary keys in table referenced by enabled foreign keys
    
    
    SQL>
    It means that you'd first have to disable constraints, then truncate tables.

    Or, you might delete them - child first, parent next. No problem.

    As of ordering tables, manually you'd do that as following: first - no particular sort:
    Code:
    SQL> select * from tab where tname like 'TEST%';
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    TEST                           TABLE
    TEST_DEPT                      TABLE
    TEST_DEPT_ERR                  TABLE
    TEST_EMP                       TABLE
    Now, sort with a little help of the DECODE function:
    Code:
    SQL> l
      1  select * from tab
      2  where tname like 'TEST%'
      3  order by decode(tname, 'TEST_EMP', 1,
      4                         'TEST_DEPT', 2,
      5                         'TEST', 3,
      6*                        'TEST_DEPT_ERR', 4)
    SQL> /
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    TEST_EMP                       TABLE
    TEST_DEPT                      TABLE
    TEST                           TABLE
    TEST_DEPT_ERR                  TABLE
    
    SQL>

Posting Permissions

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