Solved

SQLLDR question

Posted on 2004-08-11
8
2,923 Views
Last Modified: 2012-06-22
I have a flat file with fixed length fields where there is no character that terminates the field.
I'm trying to set up an external table and it wants a 'field terminated by'.
What can I use here?
0
Comment
Question by:xoxomos
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
There's no visible printable character, but there's either a terminator or one long run-on line.
Most likely either CHR(10) for line-feed, CHR(13) for carriage-return.
0
 

Author Comment

by:xoxomos
Comment Utility
I believe that would be more for the record(row) I'm looking more for the field(column).
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
Sorry about that. How are you currently doing it? (Which tool is giving you the error message)
0
 

Author Comment

by:xoxomos
Comment Utility
Right now I'm not doing it.  I left the field delimited by out so it thinks everything is 255.



Field Definitions for table TEMP_STUDENT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform

  Fields in Data Source:

    STU_KEY                         CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    NAME                            CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    STU_ID                          CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    BIRTH_DATE                      CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    SEX                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
KUP-04076: file name cannot contain a path specification: d:\oracle\student_database\dwstubio
0
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.

 

Author Comment

by:xoxomos
Comment Utility
With sqlldr, i can use
INTO TABLE student
(
STU_KEY                    POSITION(1:10) CHAR,
NAME                       POSITION(11:42) CHAR,
STU_ID                     POSITION(43:51) CHAR,
BIRTH_DATE                 POSITION(52:59) DATE "YYYYMMDD",
SEX                        POSITION(60:60) CHAR,
and not supply field terminated by.  Trying to use external table , i don't see what the option is.
0
 
LVL 6

Accepted Solution

by:
morphman earned 250 total points
Comment Utility
Here is an example of hwo to do it with fixed length fields:-

create table ext_table (
   field_1 char(4),
   field_2 char(30)
 )
 organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
      field_1 position(1:4) char(4),
      field_2 position(5:30) char(30)
     )
   )
   location ('file')
 )
 reject limit unlimited;

All the best,
Dan
0
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
goto http://www.softtreetech.com and download DB Tools for Oracle - and this can easily upload text files using File Loader.

LeoAhmad
0
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
another option do it manually:

 create directory md as 'e:\q'
SQL> /

Directory created.

SQL> create table mytab(a number,b number, c varchar2(2000))
  2  Organization external
  3  (type oracle_loader
  4  default directory md
  5  access parameters
  6  (fields terminated by '')
  7  location ('1.txt')
  8  )
  9  /

Table created.

SQL> select * from mytab;
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

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

762 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

10 Experts available now in Live!

Get 1:1 Help Now