10 Replies Latest reply on Sep 10, 2009 6:58 PM by Hoek

    SQL*Loader-704

    vpolasa
      I'm trying to execute a control file.

      Below is the error message
      SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 10 10:54:10 2009
      
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
      
      SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
      ORA-12560: TNS:protocol adapter error
      ulcase4.dat
      *7782 CLARK  MANAGER   7839 2572.50 -10     10 12-NOV-85
      *7839 KING   PRESIDENT      5500.00         20 05-APR-83
      *7934 MILLER CLERK     7782 920.00          10 08-MAY-80
      *7566 JONES  MANAGER   7839 3123.75         30 17-JUL-85
      *7499 ALLEN  SALESMAN  7698 1600.00 300.00  10 03-JUN-84
      *7654 MARTIN SALESMAN  7698 1312.50 1400.00 30 21-DEC-85
      *7658 CHAN   ANALYST   7566 3450.00         20 16-FEB-84
      ulcase4.ctl
      LOAD DATA
         INFILE 'c:\data\ulcase4.dat'
         DISCARDFILE 'ulcase4.dsc'
         DISCARDMAX 999
         REPLACE
         CONTINUEIF THIS (1) = "*"   
         INTO TABLE emp
        (empno         POSITION(1:4)         INTEGER EXTERNAL,
         ename         POSITION(6:11)        CHAR,
         job           POSITION(13:21)       CHAR,
         mgr           POSITION(23:26)       INTEGER EXTERNAL,
         sal           POSITION(28:34)       DECIMAL EXTERNAL,
         comm          POSITION(36:42)       DECIMAL EXTERNAL,
         deptno        POSITION(44:45)       INTEGER EXTERNAL,
         hiredate      POSITION(47:55)       DATE)
      What could be the reason? Thanks.
        • 1. Re: SQL*Loader-704
          Hoek
          What could be the reason?
          Is your database up?
          See: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:431775600346873836
          1 person found this helpful
          • 2. Re: SQL*Loader-704
            Solomon Yakobson
            Are you able to connect to SQL*Plus using same username, password and connect string as in SQL*Loader? If so, do you specify password in parameter file?

            SY.
            • 3. Re: SQL*Loader-704
              vpolasa
              The database is up..
              SQL Developer(v1.5.5) connected to the database.
              But when trying to connect through SQL Plus, I get.. ora-12560: TNS:Protocol Adapter error

              Solomon
              I'm trying to run the control file using
              sqlldr userid=username/password control=ulcase4.ctl log=ulcase4.log

              Thanks..
              • 4. Re: SQL*Loader-704
                Solomon Yakobson
                Is database located physically on same box you are on?

                SY.
                • 5. Re: SQL*Loader-704
                  vpolasa
                  No Solomon, I'm running on the client machine.
                  • 6. Re: SQL*Loader-704
                    Solomon Yakobson
                    polasa wrote:
                    No Solomon, I'm running on the client machine.
                    That explains it. You do not specify connect string, just username and password. Therefore, Oracle tries to connect to local database. You must sp0ecify username/password@connect_string, where connect_string is a name for the correspionding entry in your client side file TNSNAMES.ORA (same connect string you use in SQL Developer).

                    SY.
                    • 7. Re: SQL*Loader-704
                      vpolasa
                      Thanks Solomon that worked...

                      I've an other issue..

                      When I try to run the control file, only one row is inserted into the table. what could be the reason?

                      below is the data from the log file.
                         Column Name                  Position   Len  Term Encl Datatype
                      ------------------------------ ---------- ----- ---- ---- ---------------------
                      EMPNO                                 1:4     4           CHARACTER            
                      ENAME                                6:11     6           CHARACTER            
                      JOB                                 13:21     9           CHARACTER            
                      MGR                                 23:26     4           CHARACTER            
                      SAL                                 28:34     7           CHARACTER            
                      COMM                                36:42     7           CHARACTER            
                      DEPTNO                              44:45     2           CHARACTER            
                      HIREDATE                            47:55     9           DATE DD-MON-RR       
                      
                      
                      Table EMP:
                        1 Row successfully loaded.
                        0 Rows not loaded due to data errors.
                        0 Rows not loaded because all WHEN clauses were failed.
                        0 Rows not loaded because all fields were null.
                      
                      
                      Space allocated for bind array:                   4352 bytes(64 rows)
                      Read   buffer bytes: 1048576
                      
                      Total logical records skipped:          0
                      Total logical records read:             1
                      Total logical records rejected:         0
                      Total logical records discarded:        0
                      • 8. Re: SQL*Loader-704
                        Hoek
                        what could be the reason?
                        Could it be the CONTINUEIF you've got in your ctl file?

                        http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref790
                        • 9. Re: SQL*Loader-704
                          vpolasa
                          Can you please send me the possible CONTINUEIF clauses for this scenario.

                          Thanks.
                          • 10. Re: SQL*Loader-704
                            Hoek
                            Hi,

                            Are you sure you need a CONTINUEIF in your example?
                            "Use CONTINUEIF if the number of physical records to be combined varies"
                            http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref786
                            Looking at the file you want to load, it seems to me you don't have varying physical records?
                            1 person found this helpful