1 2 Previous Next 18 Replies Latest reply on Apr 28, 2014 5:50 PM by Barbara Boehmer Go to original post
      • 15. Re: External table _ adding colums
        Barbara Boehmer

        You would not even be able to create the table with that code, because Desc is not a valid column name.  So, either you are not really creating the table or you are still not posting the actual code.

        • 16. Re: External table _ adding colums
          Barbara Boehmer

          I have provided some suggested code below, followed by a run of the code on my system.  Since you have not provided any sample data, it uses some that I threw together, which assumes that your starting and ending column positions are correct.  Please note the difference between the code and a run of the code, since you still have failed to provide a run of your code, with line numbers and results.  Note that there are two alter table statements, one to add the columns and one to modify the parameters.  Note that, when replacing the parameters, you must repeat all of the parameters, not just the fields, and including all of the fields.

           

          -- code:

          CREATE TABLE scott.emp

            (empno    NUMBER   ( 8),

             ename    VARCHAR2 (30),

             sal      NUMBER   ( 8),

             dscr     VARCHAR2 (50))

          ORGANIZATION EXTERNAL

            (TYPE ORACLE_LOADER

             DEFAULT DIRECTORY suv_dir

             ACCESS PARAMETERS

                (RECORDS DELIMITED BY NEWLINE SKIP 1

                 BADFILE suv_dir:'abc.bad'

                 LOGFILE suv_dir:'abc.log'

                 DISCARDFILE suv_dir:'abc.dsc'

                 FIELDS

                   (empno POSITION ( 1:8)  CHAR( 8),

                    ename POSITION ( 9:38) CHAR(30),

                    sal   POSITION (39:47) CHAR( 8),

                    dscr  POSITION (48:97) CHAR(50)))

             LOCATION (suv_dir:'abc.txt'))

          REJECT LIMIT UNLIMITED

          /

          ALTER TABLE scott.emp ADD

            (address     VARCHAR2 (50),

             first_name  VARCHAR2 (30),

             last_name   VARCHAR2 (30))

          /

          ALTER TABLE scott.emp

             ACCESS PARAMETERS

                (RECORDS DELIMITED BY NEWLINE SKIP 1

                 BADFILE suv_dir:'abc.bad'

                 LOGFILE suv_dir:'abc.log'

                 DISCARDFILE suv_dir:'abc.dsc'

                 FIELDS

                   (empno       POSITION ( 1:8)      CHAR( 8),

                    ename       POSITION ( 9:38)     CHAR(30),

                    sal         POSITION (39:47)     CHAR( 8),

                    dscr        POSITION (48:97)     CHAR(50),

                    address     POSITION (3400:3450) char(50),

                    first_name  POSITION (3451:3479) char(30),

                    last_name   POSITION (3480:3509) char(30)))

          /

           

          -- run of code:

          SCOTT@orcl12c> CREATE TABLE scott.emp

            2    (empno     NUMBER   ( 8),

            3      ename     VARCHAR2 (30),

            4      sal     NUMBER   ( 8),

            5      dscr     VARCHAR2 (50))

            6  ORGANIZATION EXTERNAL

            7    (TYPE ORACLE_LOADER

            8      DEFAULT DIRECTORY suv_dir

            9      ACCESS PARAMETERS

          10         (RECORDS DELIMITED BY NEWLINE SKIP 1

          11          BADFILE suv_dir:'abc.bad'

          12          LOGFILE suv_dir:'abc.log'

          13          DISCARDFILE suv_dir:'abc.dsc'

          14          FIELDS

          15            (empno POSITION ( 1:8)  CHAR( 8),

          16             ename POSITION ( 9:38) CHAR(30),

          17             sal   POSITION (39:47) CHAR( 8),

          18             dscr  POSITION (48:97) CHAR(50)))

          19      LOCATION (suv_dir:'abc.txt'))

          20  REJECT LIMIT UNLIMITED

          21  /

           

          Table created.

           

          SCOTT@orcl12c> ALTER TABLE scott.emp ADD

            2    (address     VARCHAR2 (50),

            3      first_name  VARCHAR2 (30),

            4      last_name   VARCHAR2 (30))

            5  /

           

          Table altered.

           

          SCOTT@orcl12c> ALTER TABLE scott.emp

            2      ACCESS PARAMETERS

            3         (RECORDS DELIMITED BY NEWLINE SKIP 1

            4          BADFILE suv_dir:'abc.bad'

            5          LOGFILE suv_dir:'abc.log'

            6          DISCARDFILE suv_dir:'abc.dsc'

            7          FIELDS

            8            (empno       POSITION ( 1:8)    CHAR( 8),

            9             ename       POSITION ( 9:38)    CHAR(30),

          10             sal       POSITION (39:47)    CHAR( 8),

          11             dscr       POSITION (48:97)    CHAR(50),

          12             address       POSITION (3400:3450) char(50),

          13             first_name  POSITION (3451:3479) char(30),

          14             last_name   POSITION (3480:3509) char(30)))

          15  /

           

          Table altered.

           

          SCOTT@orcl12c> SELECT COUNT(*) FROM scott.emp

            2  /

           

            COUNT(*)

          ----------

                  14

           

          1 row selected.

          • 17. Re: External table _ adding colums
            969952

            Hi.. thank you very much.. it is working fine in SQLplus.. getting the above mentioned error while executing in Toad.. but it working fine without any issues in SQL plus... is there any reason behind failing in TOAD?

             

            Thank you very much.

            • 18. Re: External table _ adding colums
              Barbara Boehmer

              I don't use TOAD.  Over and over again I hear that things work in SQL*Plus, but not in TOAD.  This is another reason why it is important to post a copy and paste of a run from SQL*Plus and mention if you are using another tool.  There are TOAD forums and sub-forums if you wish to try to track down why it doesn't work in TOAD.  My recommended solution to all TOAD problems is to use SQL*Plus instead of TOAD.

              1 2 Previous Next