6 Replies Latest reply: Mar 1, 2013 8:15 PM by user503635 RSS

    Creating an external table.

    994226
      Please help,
      I need to load a csv to an external but i kept running into error.
      see the syntax below:


      SQL> CREATE TABLE PSOPRDEFN
      2 (
      3 OPRID VARCHAR2(30 BYTE),
      4 VERSION INTEGER ,
      5 OPRDEFNDESC VARCHAR2(30 BYTE),
      6 EMPLID VARCHAR2(11 BYTE),
      7 EMAILID VARCHAR2(70 BYTE),
      8 OPRCLASS VARCHAR2(30 BYTE),
      9 ROWSECCLASS VARCHAR2(30 BYTE),
      10 OPERPSWD VARCHAR2(32 BYTE),
      11 ENCRYPTED INTEGER ,
      12 SYMBOLICID VARCHAR2(8 BYTE) ,
      13 LANGUAGE_CD VARCHAR2(3 BYTE) ,
      14 MULTILANG INTEGER ,
      15 CURRENCY_CD VARCHAR2(3 BYTE) ,
      16 LASTPSWDCHANGE DATE ,
      17 ACCTLOCK INTEGER ,
      18 PRCSPRFLCLS VARCHAR2(30 BYTE),
      19 DEFAULTNAVHP VARCHAR2(30 BYTE),
      20 FAILEDLOGINS INTEGER ,
      21 EXPENT INTEGER ,
      22 OPRTYPE INTEGER ,
      23 USERIDALIAS VARCHAR2(70 BYTE),
      24 LASTSIGNONDTTM DATE,
      25 LASTUPDDTTM DATE,
      26 LASTUPDOPRID VARCHAR2(30 BYTE),
      27 PTALLOWSWITCHUSER INTEGER
      28 )
      29 organization external
      30 ( default directory extern_table_dir
      31 access parameters
      32 ( records delimited by newline
      33 fields terminated by ','
      34 MISSING FIELD VALUES ARE NULL
      35 REJECT ROWS WITH ALL NULL FIELDS
      36 (
      37 "LASTPSWDCHANGE" date 'YYYY-MM-DD',
      38 "LASTSIGNONDTTM" date 'YYYY-MM-DD',
      39 "LASTUPDDTTM" 'to_timestamp(:LASTUPDDTTM,'YYYY-MM-DD HH:MI:SS')'
      40 )
      41 )
      42 location ('listofuser.txt')
      43 )
      44 reject limit unlimited;

      Table created.




      Error:

      SQL> select * from psoprdefn;
      select * from psoprdefn
      *
      ERROR at line 1:
      ORA-29913: error in executing ODCIEXTTABLEOPEN callout
      ORA-29400: data cartridge error
      KUP-00554: error encountered while parsing access parameters
      KUP-01005: syntax error: found "single-quoted-string": expecting one of:
      "binary_double, binary_float, comma, char, date, defaultif, decimal, double,
      float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum,
      ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
      KUP-01007: at line 8 column 19
      ORA-06512: at "SYS.ORACLE_LOADER", line 19





      Data:
      16436,676,John John,16436,john.john@gmalil,POCLANYL, ,OwciRHuJKpBBdJDyyZVo+K8MUqA=,1,FSTSD9,ENG,0, ,2013-01-28,0,POCLANYL,POCLANYL,0,0,0, ,2011-04-07 3:56:55 PM,2011-02-25 4:16:13 PM,63475,0




      Please help
        • 1. Re: Creating an external table.
          jeneesh
          There are lot of errors in the definition..
          Refer the docs http://docs.oracle.com/cd/B28359_01/server.111/b28319/et_concepts.htm
          Edited by: jeneesh on Mar 1, 2013 8:40 PM
          • 2. Re: Creating an external table.
            ShishirTekadeR
            needs to be create DIR first in the OS on which your database resides and then the file needs to be placed in this location with 777 privileges (to be on safer side).

            This will resolve your issue.

            *****************************************
            Best Regards,
            Shishir Tekade.
            My Blog: http://shishirtekade.blogspot.com
            • 3. Re: Creating an external table.
              user503635
              All the problems are related to your fields of DATE type.

              would suggest change them to VARCHAR2 type, then use TO_DATE function when retrieve these fields from the external table. I have tried them

               LASTPSWDCHANGE VARCHAR2(10),
               ...
               LASTSIGNONDTTM VARCHAR2(22),
               LASTUPDDTTM VARCHAR2(22),
              and remove below clauses
               (
               "LASTPSWDCHANGE" date 'YYYY-MM-DD',
               "LASTSIGNONDTTM" date 'YYYY-MM-DD',
               "LASTUPDDTTM" 'to_timestamp(:LASTUPDDTTM,'YYYY-MM-DD HH:MI:SS')'
               )
              I have tried it works in my db on a Windows server.

              Edited by: user503635 on Mar 1, 2013 7:45 AM
              • 4. Re: Creating an external table.
                994226
                Thank you for time.


                I have the directory created and i do have read write the directory, after running it with varchar2, i still got an error.


                SQL> CREATE TABLE PSOPRDEFN
                2 (
                3 OPRID VARCHAR2(255),
                4 VERSION INTEGER ,
                5 OPRDEFNDESC VARCHAR2(255),
                6 EMPLID VARCHAR2(255),
                7 EMAILID VARCHAR2(255),
                8 OPRCLASS VARCHAR2(255),
                9 ROWSECCLASS VARCHAR2(255),
                10 OPERPSWD VARCHAR2(255),
                11 ENCRYPTED INTEGER ,
                12 SYMBOLICID VARCHAR2(255) ,
                13 LANGUAGE_CD VARCHAR2(255) ,
                14 MULTILANG INTEGER ,
                15 CURRENCY_CD VARCHAR2(255) ,
                16 LASTPSWDCHANGE VARCHAR2(10) ,
                17 ACCTLOCK INTEGER ,
                18 PRCSPRFLCLS VARCHAR2(255),
                19 DEFAULTNAVHP VARCHAR2(255),
                20 FAILEDLOGINS INTEGER ,
                21 EXPENT INTEGER ,
                22 OPRTYPE INTEGER ,
                23 USERIDALIAS VARCHAR2(255),
                24 LASTSIGNONDTTM VARCHAR2(22),
                25 LASTUPDDTTM VARCHAR2(22),
                26 LASTUPDOPRID VARCHAR2(255),
                27 PTALLOWSWITCHUSER INTEGER
                28 )
                29 organization external
                30 ( default directory extern_table_dir
                31 access parameters
                32 ( records delimited by newline
                33 fields terminated by ','
                34 MISSING FIELD VALUES ARE NULL
                35 REJECT ROWS WITH ALL NULL FIELDS
                36 (
                37 OPRID VARCHAR2(255),
                38 VERSION INTEGER ,
                39 OPRDEFNDESC VARCHAR2(255),
                40 EMPLID VARCHAR2(255),
                41 EMAILID VARCHAR2(255),
                42 OPRCLASS VARCHAR2(255),
                43 ROWSECCLASS VARCHAR2(255),
                44 OPERPSWD VARCHAR2(255),
                45 ENCRYPTED INTEGER ,
                46 SYMBOLICID VARCHAR2(255) ,
                47 LANGUAGE_CD VARCHAR2(255) ,
                48 MULTILANG INTEGER ,
                49 CURRENCY_CD VARCHAR2(255) ,
                50 LASTPSWDCHANGE VARCHAR2(10) ,
                51 ACCTLOCK INTEGER ,
                52 PRCSPRFLCLS VARCHAR2(255),
                53 DEFAULTNAVHP VARCHAR2(255),
                54 FAILEDLOGINS INTEGER ,
                55 EXPENT INTEGER ,
                56 OPRTYPE INTEGER ,
                57 USERIDALIAS VARCHAR2(255),
                58 LASTSIGNONDTTM VARCHAR2(22),
                59 LASTUPDDTTM VARCHAR2(22),
                60 LASTUPDOPRID VARCHAR2(255),
                61 PTALLOWSWITCHUSER INTEGER
                62 )
                63 )
                64 location ('listofuser.txt')
                65 )
                66 reject limit unlimited;

                Table created.

                SQL> select * from PSOPRDEFN;
                select * from PSOPRDEFN
                *
                ERROR at line 1:
                ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                ORA-29400: data cartridge error
                KUP-00554: error encountered while parsing access parameters
                KUP-01005: syntax error: found "identifier": expecting one of: "binary_double,
                binary_float, comma, char, date, defaultif, decimal, double, float, integer, (,
                nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned,
                varrawc, varchar, varraw, varcharc, zoned"
                KUP-01008: the bad identifier was: VARCHAR2
                KUP-01007: at line 6 column 20
                ORA-06512: at "SYS.ORACLE_LOADER", line 19


                SQL>


                Thank for the your time once again.
                • 5. Re: Creating an external table.
                  994226
                  After changing the datatype for one of the columns Varchar2 to varchar, I was able to query the table but it rejects all the row with the following error on the column.

                  SQL> CREATE TABLE PSOPRDEFN
                  2 (
                  3 OPRID CHAR(255),
                  4 VERSION INTEGER ,
                  5 OPRDEFNDESC CHAR(255),
                  6 EMPLID CHAR(255),
                  7 EMAILID CHAR(255),
                  8 OPRCLASS CHAR(255),
                  9 ROWSECCLASS CHAR(255),
                  10 OPERPSWD VARCHAR(499),
                  11 ENCRYPTED INTEGER ,
                  12 SYMBOLICID CHAR(255) ,
                  13 LANGUAGE_CD CHAR(255) ,
                  14 MULTILANG INTEGER ,
                  15 CURRENCY_CD CHAR(255) ,
                  16 LASTPSWDCHANGE CHAR(22) ,
                  17 ACCTLOCK INTEGER ,
                  18 PRCSPRFLCLS CHAR(255),
                  19 DEFAULTNAVHP CHAR(255),
                  20 FAILEDLOGINS INTEGER ,
                  21 EXPENT INTEGER ,
                  22 OPRTYPE INTEGER ,
                  23 USERIDALIAS CHAR(255),
                  24 LASTSIGNONDTTM CHAR(22),
                  25 LASTUPDDTTM CHAR(40),
                  26 LASTUPDOPRID CHAR(255),
                  27 PTALLOWSWITCHUSER INTEGER
                  28 )
                  29 organization external
                  30 ( default directory extern_table_dir
                  31 access parameters
                  32 ( records delimited by newline
                  33 fields terminated by ','
                  34 LRTRIM
                  35 MISSING FIELD VALUES ARE NULL
                  36 REJECT ROWS WITH ALL NULL FIELDS
                  37 (
                  38 OPRID CHAR(255),
                  39 VERSION INTEGER ,
                  40 OPRDEFNDESC CHAR(255),
                  41 EMPLID CHAR(255),
                  42 EMAILID CHAR(255),
                  43 OPRCLASS CHAR(255),
                  44 ROWSECCLASS CHAR(255),
                  45 OPERPSWD VARCHAR(499),
                  46 ENCRYPTED INTEGER ,
                  47 SYMBOLICID CHAR(255) ,
                  48 LANGUAGE_CD CHAR(255) ,
                  49 MULTILANG INTEGER ,
                  50 CURRENCY_CD CHAR(255) ,
                  51 LASTPSWDCHANGE CHAR(22) date_format DATE mask "yyyy-mm-dd",
                  52 ACCTLOCK INTEGER ,
                  53 PRCSPRFLCLS CHAR(255),
                  54 DEFAULTNAVHP CHAR(255),
                  55 FAILEDLOGINS INTEGER ,
                  56 EXPENT INTEGER ,
                  57 OPRTYPE INTEGER ,
                  58 USERIDALIAS CHAR(255),
                  59 LASTSIGNONDTTM CHAR(22) date_format DATE mask "yyyy-mm-dd",
                  60 LASTUPDDTTM CHAR(40) date_format DATE mask "yyyy-mm-dd HH:MI:SS",
                  61 LASTUPDOPRID CHAR(255),
                  62 PTALLOWSWITCHUSER INTEGER
                  63 )
                  64 )
                  65 location ('listofuser.txt')
                  66 )
                  67 reject limit unlimited;

                  Table created.

                  SQL> select * from psoprdefn;

                  no rows selected

                  SQL>




                  Error in the bad file:

                  KUP-04021: field formatting error for field OPERPSWD
                  KUP-04026: field too long for datatype
                  KUP-04101: record 1 rejected in file /u02/external/data/listofuser.txt
                  KUP-04021: field formatting error for field OPERPSWD
                  KUP-04026: field too long for datatype
                  KUP-04101: record 2 rejected in file /u02/external/data/listofuser.txt
                  KUP-04021: field formatting error for field OPERPSWD
                  KUP-04026: field too long for datatype
                  KUP-04101: record 3 rejected in file /u02/external/data/listofuser.txt
                  KUP-04021: field formatting error for field OPERPSWD
                  KUP-04026: field too long for datatype
                  KUP-04101: record 4 rejected in file /u02/external/data/listofuser.txt
                  KUP-04021: field formatting error for field OPERPSWD
                  KUP-04026: field too long for datatype







                  Below is my data example of my data:
                  16456,676,John hortins,16436,john.hor@gmail.com,POCLANYL, ,OwciRHuJKpBBdJDyyZVo+K8MUqA=,1,FSCAT9,ENG,0, ,2013-01-28,0,POCLANYL,POC
                  LANYL,0,0,0, ,2011-04-07 3:56:55 PM,2011-02-25 4:16:13 PM,69475,0



                  Thanks
                  • 6. Re: Creating an external table.
                    user503635
                    Remove line 32 - 62 under clause '35 REJECT ROWS WITH ALL NULL FIELDS'

                    what I have written
                    CREATE TABLE PSOPRDEFN
                     (
                     OPRID VARCHAR2(30 BYTE),
                     VERSION INTEGER ,
                     OPRDEFNDESC VARCHAR2(30 BYTE),
                     EMPLID VARCHAR2(11 BYTE),
                     EMAILID VARCHAR2(70 BYTE),
                     OPRCLASS VARCHAR2(30 BYTE),
                     ROWSECCLASS VARCHAR2(30 BYTE),
                     OPERPSWD VARCHAR2(32 BYTE),
                     ENCRYPTED INTEGER ,
                     SYMBOLICID VARCHAR2(8 BYTE) ,
                     LANGUAGE_CD VARCHAR2(3 BYTE) ,
                     MULTILANG INTEGER ,
                     CURRENCY_CD VARCHAR2(3 BYTE) ,
                     LASTPSWDCHANGE VARCHAR2(10) ,
                     ACCTLOCK INTEGER ,
                     PRCSPRFLCLS VARCHAR2(30 BYTE),
                     DEFAULTNAVHP VARCHAR2(30 BYTE),
                     FAILEDLOGINS INTEGER ,
                     EXPENT INTEGER ,
                     OPRTYPE INTEGER ,
                     USERIDALIAS VARCHAR2(70 BYTE),
                     LASTSIGNONDTTM VARCHAR2(22),
                     LASTUPDDTTM VARCHAR2(22),
                     LASTUPDOPRID VARCHAR2(30 BYTE),
                     PTALLOWSWITCHUSER INTEGER
                     )
                     organization external
                     ( default directory my_dir
                     access parameters
                     ( records delimited by newline
                     fields terminated by ','
                     MISSING FIELD VALUES ARE NULL
                     REJECT ROWS WITH ALL NULL FIELDS
                     )
                     location ('listofuser.txt')
                     )
                     reject limit unlimited;
                    I used the following to retrieve info from the table:
                    SQL> select * from psoprdefn;
                    
                    OPRID                                                          VERSION OPRDEFNDESC                    EMPLID      EMAILID                                                                OPRCLASS                       ROWSECCLASS                    OPERPSWD                                                       ENCRYPTED SYMBOLICID LANGUAGE_CD                               MULTILANG CURRENCY_CD LASTPSWDCHANGE                                ACCTLOCK PRCSPRFLCLS                    DEFAULTNAVHP                                              FAILEDLOGINS                                  EXPENT                                 OPRTYPE USERIDALIAS                                                            LASTSIGNONDTTM         LASTUPDDTTM            LASTUPDOPRID                                         PTALLOWSWITCHUSER
                    ------------------------------ --------------------------------------- ------------------------------ ----------- ---------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------- --------------------------------------- ---------- ----------- --------------------------------------- ----------- -------------- --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------------------------------------- ---------------------- ---------------------- ------------------------------ ---------------------------------------
                    16436                                                              676 John John                      16436       john.john@gmalil                                                       POCLANYL                                                      OwciRHuJKpBBdJDyyZVo+K8MUqA=                                           1 FSTSD9     ENG                                               0             2013-01-28                                           0 POCLANYL                       POCLANYL                                                             0                                       0                                       0                                                                        2011-04-07 3:56:55 PM  2011-02-25 4:16:13 PM  63475                                                                0
                    
                    SQL> 
                    Edited by: user503635 on Mar 1, 2013 6:15 PM