1 2 Previous Next 16 Replies Latest reply on Mar 14, 2013 10:16 AM by Nicolas.Gasparotto

    ERROR WITH EXTERNAL TABLE

    458726
      Hi,
      I am trying to query an external table that points it file on my WindowsXP system. The Oracle server is running on a same machine.

      -----------------------------------------
      create table external_arq(
      text1 varchar2(1),
      text2 varchar2(20)
      )
      organization external
      (type oracle_loader
      default directory external_directory
      access parameters
      (
      records delimited by newline
      fields
      missing field value are null
      (text1 position(1,1),
      text2 position(2,20)
      )
      )
      location ('c:\temp\TEST.TXT')
      )
      ------------------------------------------------------------

      When I query the external table it returns the following error.

      SQL> select * from EXT_TABLE;
      select * from EXT_TABLE
      *
      ERROR at line 1:
      ORA-29913: error in executing ODCIEXTTABLEOPEN callout
      ORA-29400: data cartridge error
      KUP-00554: error encountred while parsing access parameters
      KUP-01005: sintaxe error: found "identifier": expecting one of :"values"
      KUP-01008: the bad identifier was: value
      KUP-01007: at line 3 column 15
      ORA-06512: at "SYS.ORACLE_LOADER", line 14
      ORA-06512: at line 1
      Vendor code 29913

      Anybody have any idea ?

      Anderson
        • 1. Re: ERROR WITH EXTERNAL TABLE
          484240
          When using positional notation for the fields, I always have used : to separate the start:stop position where you are using a ,.

          Regards,
          Daniel Fink
          • 2. Re: ERROR WITH EXTERNAL TABLE
            230263
            Try changing the line
            missing field value are null
            to
            missing field values are null
            • 3. Re: ERROR WITH EXTERNAL TABLE
              458726
              Using ':' to separete the fields and not nulls in external table, same error.
              Any more idea ?
              • 4. Re: ERROR WITH EXTERNAL TABLE
                458726
                Anybody can help me ?
                • 5. Re: ERROR WITH EXTERNAL TABLE
                  230263
                  Did you try changing the
                  missing field value are null
                  to
                  missing field values are null
                  If you are still getting an error it should be a diffenernt one. Please post the new error displayed.
                  • 6. Re: ERROR WITH EXTERNAL TABLE
                    458726
                    now...

                    An error was encoutred performing the request operation:
                    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                    ORA-29400: data cartridge error
                    KUP-00554: error encountred while parsing access parameters
                    KUP-01005: sintaxe error: found "comma": expecting one of :"colon, minussng,)"
                    KUP-01007: at line 4 column 18
                    ORA-06512: at "SYS.ORACLE_LOADER", line 19
                    ORA-06512: at line 1
                    Vendor code 29913
                    • 7. Re: ERROR WITH EXTERNAL TABLE
                      230263
                      This error is different then the first. I would now change the , to : as Danial suggested. If you still get errors please post both the error and your create table statement.
                      • 8. Re: ERROR WITH EXTERNAL TABLE
                        APC
                        Are you still including this line:
                        location ('c:\temp\TEST.TXT')
                        Try removng the directory. The LOCATION should just be the file name ('test.txt') as you should have a previously created directory (which you have chosen to call EXTERNAL_DIRECTORY) that should point at 'C:\temp'.

                        Cheers, APC
                        • 9. Re: ERROR WITH EXTERNAL TABLE
                          458726
                          Thanks APC, but... same after alterations, is retorned the same error (last error posted).
                          • 10. Re: ERROR WITH EXTERNAL TABLE
                            APC
                            Maybe it doesn't like this line
                            missing field values are null
                            I think that's a 10g piece o' syntax. You tell us what OS you're using but not your version of the database. If you're using 9i remove that and see what happens.

                            Cheers, APC
                            • 11. Re: ERROR WITH EXTERNAL TABLE
                              230263
                              The errors on external tables tend to be a little cryptic, but they are there. The first error you were receiving was
                              KUP-01005: sintaxe error: found "identifier": expecting one of :"values"
                              When you changed the
                              missing field value are null
                              to
                              missing field values are null
                              That error was resolved, but
                              you then received the second error
                              KUP-01005: sintaxe error: found "comma": expecting one of :"colon, minussng,)"
                              By doing what Danial suggested it should remove this error and then we shall see what new error we get.
                              • 12. Re: ERROR WITH EXTERNAL TABLE
                                458726
                                APC, SO WinXP Professional Version 2002 Service Pack 2
                                DB Oracle 10g 01
                                • 13. Re: ERROR WITH EXTERNAL TABLE
                                  484240
                                  You have several problems.

                                  First, in your original post, you create the table external_arq, but you are querying the EXT_TABLE table. It helps when you show the actual unedited output. If I complain about data in the EMP table, but I show a query from the DEPT table, it's invalid.

                                  Second, it always helps when you post unedited examples. I'm not throwing stones about this as I have done this more times than I can count (as we all have I am sure), but sometimes we think we have made a change, when we have not really done it. Or we have made another change that we did not think would matter. Often times someone else will see a 'mistake' that we think we have corrected. (I'll include a long example of trying different things at the end...if you are interested)

                                  When I do the following commands, it all works...

                                  SQL> create directory external_directory as 'c:\temp';

                                  Directory created.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('TEST.TXT')
                                  18* )
                                  SQL> /

                                  Table created.

                                  SQL> select * from external_arq;
                                  1 2222222222222222222
                                  A BBBBBBBBBBBBBBBBBBB

                                  SQL>

                                  Cheers,
                                  Daniel Fink

                                  <Here's the long version...>
                                  SQL> create directory external_directory as 'c:\'
                                  SQL> /

                                  Directory created.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field value are null
                                  13 (text1 position(1,1),
                                  14 text2 position(2,20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18 )
                                  19 /

                                  Table created.

                                  SQL> select * from external_arq
                                  2 /
                                  select * from external_arq
                                  *
                                  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: "values"
                                  KUP-01008: the bad identifier was: value
                                  KUP-01007: at line 3 column 15
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19


                                  SQL> drop table external_arq
                                  2 /

                                  Table dropped.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field value are null
                                  13 (text1 position(1,1),
                                  14 text2 position(2,20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18 )
                                  19
                                  SQL> 12
                                  12* missing field value are null
                                  SQL> c/value/values
                                  12* missing field values are null
                                  SQL> l
                                  1 create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1,1),
                                  14 text2 position(2,20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18* )
                                  SQL> /

                                  Table created.

                                  SQL> select * from external_arq;
                                  select * from external_arq
                                  *
                                  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 "comma": expecting one of: "colon, minussign, )"
                                  KUP-01007: at line 4 column 18
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19


                                  SQL> drop table external_arq;

                                  Table dropped.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1,1),
                                  14 text2 position(2,20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18 )
                                  19
                                  SQL> 13
                                  13* (text1 position(1,1),
                                  SQL> c/,/:
                                  13* (text1 position(1:1),
                                  SQL> 14
                                  14* text2 position(2,20)
                                  SQL> c/,/:
                                  14* text2 position(2:20)
                                  SQL> l
                                  1 create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18* )
                                  SQL> /

                                  Table created.

                                  SQL> select * from external_arq;
                                  select * from external_arq
                                  *
                                  ERROR at line 1:
                                  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                                  ORA-29400: data cartridge error
                                  KUP-04076: file name cannot contain a path specification: c:\temp\TEST.TXT
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19


                                  SQL> drop table external_arq;

                                  Table dropped.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18 )
                                  19
                                  SQL> 16
                                  16* )
                                  SQL> 17
                                  17* location ('c:\temp\TEST.TXT')
                                  SQL> c/c:\//
                                  17* location ('temp\TEST.TXT')
                                  SQL> /

                                  Table created.

                                  SQL> select * from external_arq
                                  2 /
                                  select * from external_arq
                                  *
                                  ERROR at line 1:
                                  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
                                  ORA-29400: data cartridge error
                                  KUP-04076: file name cannot contain a path specification: temp\TEST.TXT
                                  ORA-06512: at "SYS.ORACLE_LOADER", line 19

                                  SQL> drop directory external_directory;

                                  Directory dropped.

                                  SQL> create directory external_directory as 'c:\temp';

                                  Directory created.

                                  SQL> create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('c:\temp\TEST.TXT')
                                  18 )
                                  19
                                  SQL> 17
                                  17* location ('c:\temp\TEST.TXT')
                                  SQL> i
                                  18i
                                  SQL> edit
                                  Wrote file afiedt.buf

                                  1 create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('TEST.TXT')
                                  18* )
                                  SQL> l
                                  1 create table external_arq(
                                  2 text1 varchar2(1),
                                  3 text2 varchar2(20)
                                  4 )
                                  5 organization external
                                  6 (type oracle_loader
                                  7 default directory external_directory
                                  8 access parameters
                                  9 (
                                  10 records delimited by newline
                                  11 fields
                                  12 missing field values are null
                                  13 (text1 position(1:1),
                                  14 text2 position(2:20)
                                  15 )
                                  16 )
                                  17 location ('TEST.TXT')
                                  18* )
                                  SQL> /
                                  create table external_arq(
                                  *
                                  ERROR at line 1:
                                  ORA-00955: name is already used by an existing object


                                  SQL> drop table external_arq
                                  2 /

                                  Table dropped.

                                  SQL> @afiedt.buf

                                  Table created.

                                  SQL> select * from external_arq;
                                  1 2222222222222222222
                                  A BBBBBBBBBBBBBBBBBBB

                                  SQL>
                                  • 14. Re: ERROR WITH EXTERNAL TABLE
                                    458726
                                    Just recreating the external directory the problem has solved.
                                    Thanks for all helps.

                                    Regards,
                                    Anderson.
                                    1 2 Previous Next