Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    8

    Unanswered: Determine the right load sequence sqlloader

    Hi,

    I´m looking for a script or tool to determin the proper sequence to load *.dat files with the SQLLOADER tool. I have problems with foreign key´s ....
    There are a lot of parent child relationship´s in the database !

    Thanks all !

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try this:

    Code:
    
    /*
    	tab_fk_path.sql
    */
    undef Owner Tabname
    Select Level Lvl, Table_Name, Nm, Fk, Delete_Rule
    From (
    Select
      A.Owner
     ,A.Table_Name
     ,A.Constraint_Name     Pk
     ,B.Constraint_Name     Nm
     ,B.R_Constraint_Name   Fk
     ,B.Delete_Rule
    From All_Constraints A, All_Constraints B
    Where A.Owner='&Owner'
      --And A.Table_Name = '&&Tabname'
      And A.Constraint_Type In ('P','U')
      And B.Owner=a.Owner
      And B.Table_Name = A.Table_Name
      And B.Constraint_Type In ('R') ) C
    Start With Owner = '&&Owner'
           And Table_Name = '&Tabname'
    Connect By Prior Pk = Fk
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2003
    Posts
    8
    Originally posted by LKBrwn_DBA
    Try this:

    Code:
    
    /*
    	tab_fk_path.sql
    */
    undef Owner Tabname
    Select Level Lvl, Table_Name, Nm, Fk, Delete_Rule
    From (
    Select
      A.Owner
     ,A.Table_Name
     ,A.Constraint_Name     Pk
     ,B.Constraint_Name     Nm
     ,B.R_Constraint_Name   Fk
     ,B.Delete_Rule
    From All_Constraints A, All_Constraints B
    Where A.Owner='&Owner'
      --And A.Table_Name = '&&Tabname'
      And A.Constraint_Type In ('P','U')
      And B.Owner=a.Owner
      And B.Table_Name = A.Table_Name
      And B.Constraint_Type In ('R') ) C
    Start With Owner = '&&Owner'
           And Table_Name = '&Tabname'
    Connect By Prior Pk = Fk
    /
    
    When I run this script, I get the following error :

    Error on line 4
    Select Level Lvl, Table_Name, Nm, Fk, Delete_Rule
    From (
    Select
    A.Owner
    ,

    ORA-01437: cannot have join with CONNECT BY

    What´s wrong ?

    Thanks for the reply !

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    Your oracle version is wrong (this works for 8i+),
    the work-around is to create a temp table:

    Code:
    
    undef Owner Tabname
    drop table temp_constraints
    /
    create table temp_comstraints
    as
    Select
      A.Owner
     ,A.Table_Name
     ,A.Constraint_Name     Pk
     ,B.Constraint_Name     Nm
     ,B.R_Constraint_Name   Fk
     ,B.Delete_Rule
    From All_Constraints A, All_Constraints B
    Where A.Owner='&Owner'
      And A.Constraint_Type In ('P','U')
      And B.Owner=a.Owner
      And B.Table_Name = A.Table_Name
      And B.Constraint_Type In ('R') 
    /
    Select Level Lvl, Table_Name, Nm, Fk, Delete_Rule
    From temp_constraints C
    Start With Owner = '&&Owner'
            And Table_Name = '&Tabname'
    Connect By Prior Pk = Fk
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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