Toad World® Forums

External Table Problems in 6.5

I’ve run into two annoying and possibly related problems in 6.5 around external tables that don’t seem to exist in 6.3:

I have an external table for which I want to extract the DDL. If I extract in 6.3.1, I get the full external table DDL. If I extract it in 6.5, I only get the DDL as if it were an internal table. I’ve attempted to verify the settings are identical between the two versions, they are connecting to the same DB, and extracting DDL for the same external table. Am I missing something (entirely possible), or was this inadvertently broken in 6.5?

Also, possibly related, if I expand the table category in up the full DB navigator in 6.3.1, for this schema it behaves normally. If I expand the table category in DB Navigator in 6.5, it shows no tables despite the fact that the DB explorer shows 11 tables including two external tables.

Hi Mark,

I could repeat any issue like you reported so far. May I ask following questions to get more information?

  1. firstly, did you select “Enable using DBA views” in logon window when you create database connection, both 6.3 and 6.5? If the option was not selected when you used 6.5, could you test again with this option selected?
  2. does the external table belong to current schema or other user’s schema?
  3. what exactly was missing in 6.5 extracting DDL from the external table?
  4. have you set up any filter in the DB Navigator?

Thanks,
Vincent

Did you mean to say you could repeat the issue or you could not? Either way, to answer your questions

  1. I am away from my machine with 6.3, but on 6.5 it made no difference if DBA views were enabled or not
  2. The external table belongs to the schema I am logged in as
  3. The ddl that was extracted on 6.5 was as if it were a standard table while the DDL extracted from 6.3 contains the necessary logic for the external table. I’ve chopped out most of the columns, but the basic code is below. You can see all of the logic beneath the list of columns in the table is missing.

DDL FROM SQLNavigator 6.5:
– Start of DDL Script for Table NFL.GAME_EXT
CREATE TABLE game_ext
(game_date DATE,
season NUMBER(38,0),
week NUMBER(38,0),
stadium VARCHAR2(255 BYTE),
playingsurface VARCHAR2(255 BYTE),
temperature NUMBER,

hometurnoverdifferential NUMBER(38,0))
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING/
– End of DDL Script for Table NFL.GAME_EXT

DDL From SQLNavigator 6.3:
– Start of DDL Script for Table NFL.GAME_EXT

CREATE TABLE game_ext
(game_date DATE,
season NUMBER(38,0),
week NUMBER(38,0),
stadium VARCHAR2(255 BYTE),
playingsurface VARCHAR2(255 BYTE),
temperature NUMBER,

hometurnoverdifferential NUMBER(38,0))
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY ORCL_NFL
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
SKIP 1
CHARACTERSET WE8MSWIN1252
STRING SIZES ARE IN BYTES
NOBADFILE
NODISCARDFILE
LOGFILE ‘game.log’
FIELDS
TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘"’ AND ‘"’
NOTRIM
(
“GAME_DATE” CHAR DATE_FORMAT DATE MASK “MM/DD/YYYY”,
“SEASON” CHAR,
“WEEK” CHAR,
“STADIUM” CHAR,
“PLAYINGSURFACE” CHAR,
“TEMPERATURE” CHAR,

“HOMETURNOVERDIFFERENTIAL” CHAR
)
)
LOCATION (
ORCL_NFL:‘Game_2008.csv’
)
)
REJECT LIMIT UNLIMITED
NOPARALLEL
/
Message was edited by: MarkM in PA

Thanks for reporting this.
I could reproduce in oracle 11g R2.
It happens for external tables in oracle 11g R2.
I has raised a CR for this issue.

Thanks a lot.
Regars
Lyla Su

Perfect Lyla. This is indeed a 11gR2 database. Glad to hear about the CR. Thank you.

I haven’t had a chance to try the latest beta. Did this CR get addressed in the 6.6 beta?

Hi Mark

CR 13550 was raised for this issue. It should be in the latest beta.

regards
Lidia

Appears to be fixed in the 6.6 general release. Thank you all!