Solved

External Tables

Posted on 2003-10-21
4
1,411 Views
Last Modified: 2013-12-12
Hi Experts,

I have this problem.

SQL> INSERT INTO TMP_P_PAGOME SELECT * FROM ADMIN_EXT_P_PAGOME;
INSERT INTO TMP_P_PAGOME SELECT * FROM ADMIN_EXT_P_PAGOME
*
ERROR en línea 1:
ORA-29913: error al ejecutar la llamada de ODCIEXTTABLEOPEN
ORA-29400: error de cartucho de datos
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "(": expecting one of: "badfile, byteordermark,
characterset, data, delimited, discardfile, exit, fields, fixed, load, logfile,
nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string,
skip, variable"
KUP-01007: at line 4 column 10
ORA-06512: en "SYS.ORACLE_LOADER", línea 14
ORA-06512: en línea 1

The code is:



CREATE TABLE admin_ext_p_pagome
                 (
                 MES            NUMBER(2),
                 AÑO            NUMBER(4),
                 PAGOMXIDENTIF  NUMBER(9),
                 PAGOMXEXENTO   NUMBER(1),
                 PAGOMXTOTPEN   NUMBER(7),
                 PAGOMXASIFAM   NUMBER(5),
                 PAGOMXDESLEG   NUMBER(6),
                 PAGOMXIMPTOS   NUMBER(6),
                 PAGOMXRETJUD   NUMBER(6),
                 PAGOMXDESVAR   NUMBER(6),
                 PAGOMXAGUINA   NUMBER(5),
                 PAGOMXZONAPR   NUMBER(7)
                 )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'e_pagome.bad'
         logfile admin_log_dir:'e_pagome.log'
         (MES           POSITION(1:2)     INTEGER EXTERNAL,
          AÑO           POSITION(3:6)     INTEGER EXTERNAL,
          PAGOMXIDENTIF POSITION(7:15)    INTEGER EXTERNAL,
          PAGOMXEXENTO  POSITION(16:16)   INTEGER EXTERNAL,
          PAGOMXTOTPEN  POSITION(17:23)   INTEGER EXTERNAL,
          PAGOMXASIFAM  POSITION(24:28)   INTEGER EXTERNAL,
          PAGOMXDESLEG  POSITION(29:34)   INTEGER EXTERNAL,
          PAGOMXIMPTOS  POSITION(35:39)   INTEGER EXTERNAL,
          PAGOMXRETJUD  POSITION(40:45)   INTEGER EXTERNAL,
          PAGOMXDESVAR  POSITION(46:51)   INTEGER EXTERNAL,
          PAGOMXAGUINA  POSITION(52:56)   INTEGER EXTERNAL,
          PAGOMXZONAPR  POSITION(57:63)   INTEGER EXTERNAL
          )
       )
       LOCATION ('e_pagome1.dat','e_pagome2.dat')
     )
     PARALLEL 5
     REJECT LIMIT UNLIMITED
/
0
Comment
Question by:jamigo
  • 2
4 Comments
 
LVL 5

Expert Comment

by:Pontis
ID: 9595944
See documentation -  you can only query the external table. You cannot do insert/update/delete on them.

You can load the data from external table to Oracle table (Create table ora_tab as select * from ext_tab) and then you can do whatever you want...

From docs:

Managing External Tables

Oracle allows you read-only access to data in external tables. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing Oracle with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

0
 
LVL 11

Expert Comment

by:pennnn
ID: 9598887
You're missing the FIELDS keyword.
It should be:
....
ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'e_pagome.bad'
         logfile admin_log_dir:'e_pagome.log'
    FIELDS
         (MES           POSITION(1:2)     INTEGER EXTERNAL,
          AÑO           POSITION(3:6)     INTEGER EXTERNAL,
      .....

There might be some other problems, but I guess we'll find out after we fix this one :-) But it looks good to me - can't see any other issues.
Hope that helps!
0
 

Author Comment

by:jamigo
ID: 9599009
Thanks Guys,

Now the sentences are working.
Here de code.

CREATE OR REPLACE DIRECTORY admin_dat_dir as '\\dic00037\comparte\';
CREATE OR REPLACE DIRECTORY admin_log_dir as '\\dic00037\comparte\';
CREATE OR REPLACE DIRECTORY admin_bad_dir as '\\dic00037\comparte\';

GRANT READ  ON DIRECTORY admin_dat_dir TO PUBLIC;
GRANT READ  ON DIRECTORY admin_log_dir TO PUBLIC;
GRANT READ  ON DIRECTORY admin_bad_dir TO PUBLIC;

GRANT WRITE ON DIRECTORY admin_dat_dir TO PUBLIC;
GRANT WRITE ON DIRECTORY admin_log_dir TO PUBLIC;
GRANT WRITE ON DIRECTORY admin_bad_dir TO PUBLIC;

DROP TABLE admin_ext_p_pagome;

CREATE TABLE admin_ext_p_pagome
                 (
                 MES            NUMBER(2),
                 AÑO            NUMBER(4),
                 PAGOMXIDENTIF  NUMBER(9),
                 PAGOMXEXENTO   NUMBER(1),
                 PAGOMXTOTPEN   NUMBER(7),
                 PAGOMXASIFAM   NUMBER(5),
                 PAGOMXDESLEG   NUMBER(6),
                 PAGOMXIMPTOS   NUMBER(6),
                 PAGOMXRETJUD   NUMBER(6),
                 PAGOMXDESVAR   NUMBER(6),
                 PAGOMXAGUINA   NUMBER(5),
                 PAGOMXZONAPR   NUMBER(7)
                 )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY ADMIN_DAT_DIR
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'e_pagome.bad'
         logfile admin_log_dir:'e_pagome.log'
         fields terminated by ''
         (MES           POSITION(1:2)     INTEGER EXTERNAL,
          AÑO           POSITION(3:6)     INTEGER EXTERNAL,
          PAGOMXIDENTIF POSITION(7:15)    INTEGER EXTERNAL,
          PAGOMXEXENTO  POSITION(16:16)   INTEGER EXTERNAL,
          PAGOMXTOTPEN  POSITION(17:23)   INTEGER EXTERNAL,
          PAGOMXASIFAM  POSITION(24:28)   INTEGER EXTERNAL,
          PAGOMXDESLEG  POSITION(29:34)   INTEGER EXTERNAL,
          PAGOMXIMPTOS  POSITION(35:39)   INTEGER EXTERNAL,
          PAGOMXRETJUD  POSITION(40:45)   INTEGER EXTERNAL,
          PAGOMXDESVAR  POSITION(46:51)   INTEGER EXTERNAL,
          PAGOMXAGUINA  POSITION(52:56)   INTEGER EXTERNAL,
          PAGOMXZONAPR  POSITION(57:63)   INTEGER EXTERNAL
          )
       )
       LOCATION (admin_dat_dir:'e_pagome.txt')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

DROP TABLE admin_ext_p_pagom2;

CREATE TABLE admin_ext_p_pagom2
                 (
             MES           NUMBER(2),
             ANO           NUMBER(4),
             PAGOMXIDENTIF NUMBER(9),
             PAGOMXTOTUVZ  NUMBER(7),
             PAGOMXAFMUVZ  NUMBER(5),
             PAGOMXLEGUVZ  NUMBER(6),
             PAGOMXIMPUVZ  NUMBER(6),
             PAGOMXJUDUVZ  NUMBER(6),
             PAGOMXDESDE   NUMBER(8),
             PAGOMXHASTA   NUMBER(8),
             PAGOMXZPRUVZ  NUMBER(7)
                 )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY ADMIN_DAT_DIR
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'e_pagom2.bad'
         logfile admin_log_dir:'e_pagom2.log'
         fields terminated by ''
                (MES           POSITION(1:2)   INTEGER EXTERNAL,
                 ANO           POSITION(3:6)   INTEGER EXTERNAL,
                 PAGOMXIDENTIF POSITION(7:15)  INTEGER EXTERNAL,
                 PAGOMXTOTUVZ  POSITION(16:22) INTEGER EXTERNAL,
                 PAGOMXAFMUVZ  POSITION(23:27) INTEGER EXTERNAL,
                 PAGOMXLEGUVZ  POSITION(28:33) INTEGER EXTERNAL,
                 PAGOMXIMPUVZ  POSITION(34:39) INTEGER EXTERNAL,
                 PAGOMXJUDUVZ  POSITION(40:45) INTEGER EXTERNAL,
                 PAGOMXDESDE   POSITION(46:53) INTEGER EXTERNAL,
                 PAGOMXHASTA   POSITION(54:61) INTEGER EXTERNAL,
                 PAGOMXZPRUVZ  POSITION(62:68) INTEGER EXTERNAL
                 )
       )
       LOCATION (admin_dat_dir:'e_pagom2.txt')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;
 
ALTER SESSION ENABLE PARALLEL DML;

TRUNCATE TABLE interfaz.TMP_P_PAGOME;

INSERT INTO interfaz.TMP_P_PAGOME SELECT * FROM admin_ext_p_pagome;

TRUNCATE TABLE interfaz.TMP_P_PAGOM2;

INSERT INTO interfaz.TMP_P_PAGOM2 SELECT * FROM admin_ext_p_pagom2;

COMMIT;

Regards
Jaime Amigo
CHILE
0
 
LVL 11

Accepted Solution

by:
pennnn earned 250 total points
ID: 9599459
Since you use the positions of the fields in the field specification you don't need to use the "fields terminated by" syntax. You can only specify "FIELDS" and that would be enough. Anyway it works this way, so you can leave it the way it is.
I see this is your first question here. It's a good idea to close the question after the problem has been solved by accepting the comment(s) that helped you solve it as an answer.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now