OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Loading Problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loading Problem [message #138156]

Tue, 20 September 2005 08:45

tvssbjagdish
Messages: 32
Registered: March 2005
Location: Hyderabad

Member

Hi All,

I am using sql*loader to pump the data into database table. My table is having self referential integrity constraint,say mgrid is the foreign key of empid. I used to take the data in a pipe delimited format.

select empno||'|'||ename||'|'||mgr from emp where empno=mgr;

then i paste that in .in file

after that

select empno||'|'||ename||'|'||mgr from emp where empno!=mgr;

the above output i append to the .in file.

The data gets loaded into db successfully. But the problem is with recursive dependencies. If i use the above 2 queries , obviously the problem will not get solved.

How to pump such data into database by using sql*loader.

Plz help me out.

Jagdish

Report message to a moderator

Re: Loading Problem [message #138157 is a reply to message #138156]

Tue, 20 September 2005 08:48

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Disable the self referencing constraint.
Load the data as is.
Enable the constraint back.

Report message to a moderator

Re: Loading Problem [message #138159 is a reply to message #138157]

Tue, 20 September 2005 08:57

tvssbjagdish
Messages: 32
Registered: March 2005
Location: Hyderabad

Member

That looks nice. But the problem is, i need to give pipedelimited format (.in files) to the client. That should be done from my side so that they can load the data without any hindrances. Any thoughts?

Report message to a moderator

Re: Loading Problem [message #138162 is a reply to message #138159]

Tue, 20 September 2005 09:10

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Or
extract the records based on manager tree (level).
first extract all managers and then the regular employees.
something like this.
Load in the same order.
Now, managers are loaded first. Regular employees are loaded later.

scott@9i > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

scott@9i > get level
  1  select  LEVEL, EMPNO, ENAME, MGR,JOB
  2            from  EMP
  3          connect by prior EMPNO = MGR
  4            start with MGR is NULL
  5*      order by level
scott@9i > @level

     LEVEL      EMPNO ENAME             MGR JOB
---------- ---------- ---------- ---------- ---------
         1       7839 KING                  PRESIDENT
         2       7566 JONES            7839 MANAGER
         2       7782 CLARK            7839 MANAGER
         2       7698 BLAKE            7839 MANAGER
         3       7788 SCOTT            7566 ANALYST
         3       7499 ALLEN            7698 SALESMAN
         3       7654 MARTIN           7698 SALESMAN
         3       7934 MILLER           7782 CLERK
         3       7900 JAMES            7698 CLERK
         3       7844 TURNER           7698 SALESMAN
         3       7521 WARD             7698 SALESMAN
         3       7902 FORD             7566 ANALYST
         4       7876 ADAMS            7788 CLERK
         4       7369 SMITH            7902 CLERK

14 rows selected.

Report message to a moderator

Re: Loading Problem [message #138429 is a reply to message #138156]

Wed, 21 September 2005 23:35

tvssbjagdish
Messages: 32
Registered: March 2005
Location: Hyderabad

Member

Sorry for troubling you. If there is a null in the parent column, start with null works. But if i dont have nulls in the parent what happens. Its something like my empid has the mgr the same empid. In that case what is the best option to derive at.

Regards
Jagdish

Report message to a moderator

Re: Loading Problem [message #138575 is a reply to message #138429]

Thu, 22 September 2005 09:11

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I dont understand.
Please Post sample data.
You mean all 'managers' have empno=mgr?
THen it means NO manager is reporting to another senior manager or president instead reporting to self?
So there are only 2 levels here. Manager and employees.
In that case

scott@9i > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

scott@9i > delete from emp where ename in ('KING','ADAMS','SMITH');

3 rows deleted.

scott@9i > update emp set mgr=empno where job='MANAGER';

3 rows updated.

scott@9i > commit;

Commit complete.

scott@9i > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7566 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7698 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7782 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

11 rows selected.

scott@9i > get result
  1  set colsep '||'
  2  select * from emp where mgr=empno order by empno;
  3* select * from emp where mgr<>empno and mgr in (select mgr from emp where empno=mgr) order by empno;
scott@9i > @result

     EMPNO||ENAME     ||JOB      ||       MGR||HIREDATE ||       SAL||      COMM||    DEPTNO
----------||----------||---------||----------||---------||----------||----------||----------
      7566||JONES     ||MANAGER  ||      7566||02-APR-81||      2975||          ||        20
      7698||BLAKE     ||MANAGER  ||      7698||01-MAY-81||      2850||          ||        30
      7782||CLARK     ||MANAGER  ||      7782||09-JUN-81||      2450||          ||        10


     EMPNO||ENAME     ||JOB      ||       MGR||HIREDATE ||       SAL||      COMM||    DEPTNO
----------||----------||---------||----------||---------||----------||----------||----------
      7499||ALLEN     ||SALESMAN ||      7698||20-FEB-81||      1600||       300||        30
      7521||WARD      ||SALESMAN ||      7698||22-FEB-81||      1250||       500||        30
      7654||MARTIN    ||SALESMAN ||      7698||28-SEP-81||      1250||      1400||        30
      7788||SCOTT     ||ANALYST  ||      7566||09-DEC-82||      3000||          ||        20
      7844||TURNER    ||SALESMAN ||      7698||08-SEP-81||      1500||         0||        30
      7900||JAMES     ||CLERK    ||      7698||03-DEC-81||       950||          ||        30
      7902||FORD      ||ANALYST  ||      7566||03-DEC-81||      3000||          ||        20
      7934||MILLER    ||CLERK    ||      7782||23-JAN-82||      1300||          ||        10

--
--or
--
scott@9i > get result2
  1  select EMPNO,ENAME,MGR,JOB from emp where job='MANAGER' AND empno=mgr;
  2  select  EMPNO, ENAME, MGR,JOB
  3                  from  EMP
  4                connect by prior empno=mgr
  5*               START WITH job <> 'MANAGER';
scott@9i > @result2

     EMPNO||ENAME     ||       MGR||JOB
----------||----------||----------||---------
      7566||JONES     ||      7566||MANAGER
      7698||BLAKE     ||      7698||MANAGER
      7782||CLARK     ||      7782||MANAGER


     EMPNO||ENAME     ||       MGR||JOB
----------||----------||----------||---------
      7788||SCOTT     ||      7566||ANALYST
      7902||FORD      ||      7566||ANALYST
      7499||ALLEN     ||      7698||SALESMAN
      7521||WARD      ||      7698||SALESMAN
      7654||MARTIN    ||      7698||SALESMAN
      7844||TURNER    ||      7698||SALESMAN
      7900||JAMES     ||      7698||CLERK
      7934||MILLER    ||      7782||CLERK

8 rows selected.

Report message to a moderator

Re: Loading Problem [message #138942 is a reply to message #138156]

Sun, 25 September 2005 22:59

tvssbjagdish
Messages: 32
Registered: March 2005
Location: Hyderabad

Member

Thank you so much Mahesh. It works fine...

Report message to a moderator

Previous Topic:	marrying 2 export.dmp files - help
Next Topic:	problems in IMPORT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 23:21:15 CDT 2024