OraFAQ Forum: SQL & PL/SQL » external table with single column

Home » SQL & PL/SQL » SQL & PL/SQL » external table with single column (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

external table with single column [message #657364]

Mon, 07 November 2016 09:30

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear gurus

I want to create a external table with single column which can accomdate all the column from my file in single column. My file is variable length column so i want to create external table with just one column and accept all column in a rows to single column

<PROJECT>
VLU;"002.005.002.000";"MITRAC TDS";AEEF;03FE;"EN";""
<DEV_ADDRESS>
ODBS_CFG;128;""
ODBS_CFG;129;""
<PCL>
2E;"HCA";"COP Car A";"ODBS_CFG"
7C;"DD";"Doors Car D";"ODBS_CFG"
3D;"XA";"Auxiliary Car A";"ODBS_CFG"
3E;"XB";"Auxiliary Master";"ODBS_CFG"
29;"HA";"Line Voltage Car A";"ODBS_CFG"
2A;"HB";"Battery Car B";"ODBS_CFG"
3F;"XC";"Auxiliary Car C";"ODBS_CFG"
4C;"BrPA";"Brakes Car A";"ODBS_CFG"
31;"HCD";"COP Car D";"ODBS_CFG"
40;"XD";"Auxiliary Master";"ODBS_CFG"
4D;"BrPB";"Brakes Car B";"ODBS_CFG"
5A;"CMs";"Control Master";"ODBS_CFG"
83;"KA";"PV&HVAC Car A";"ODBS_CFG"
2C;"HD";"Line Voltage Car D";"ODBS_CFG"

How can i achieve it

Report message to a moderator

Re: external table with single column [message #657365 is a reply to message #657364]

Mon, 07 November 2016 09:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What is posted above?

Report message to a moderator

Re: external table with single column [message #657367 is a reply to message #657365]

Mon, 07 November 2016 09:40

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

sorry

above is the content of the file whre feild is terminated by ';' and there are some rows where there is single field. all i want to know how can i 1 rows which consis of many column in single column in external table

for example

my external table has 1 column col1

so from my file from the above sample i sent the output should be

col1
2C;"HD";"Line Voltage Car D";"ODBS_CFG"
83;"KA";"PV&HVAC Car A";"ODBS_CFG"
<PCL>

hope it is clear

Report message to a moderator

Re: external table with single column [message #657368 is a reply to message #657367]

Mon, 07 November 2016 09:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

We don't know what is the input?

IMO, this is total & complete nonsense.

Report message to a moderator

Re: external table with single column [message #657369 is a reply to message #657368]

Mon, 07 November 2016 09:48

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Input is multiple columns file , each line may have different number of columns may be 1 columns or may be 10 column and upto max 14 column but field seperation is not always same so i want to create external table with sigle column

for example below rows from file has 4 rows.
2C;"HD";"Line Voltage Car D";"ODBS_CFG"

so in external table it should appear in sigle column

Report message to a moderator

Re: external table with single column [message #657370 is a reply to message #657364]

Mon, 07 November 2016 09:51

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For exemple:

create table my_table_ext (line varchar2(2000))
organization external (
   type oracle_loader
   default directory my_dir
   access parameters (
      records delimited by newline
      nobadfile
      nologfile
      nodiscardfile
      fields 
      missing field values are null
      (line position (1:2000))
      )
   location ('myfile') 
   )
reject limit unlimited
/

And don't forget to feedback, you didn't in your previous topics, so review them and do it now.

Report message to a moderator

Re: external table with single column [message #657372 is a reply to message #657369]

Mon, 07 November 2016 10:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

guddu_12 wrote on Mon, 07 November 2016 07:48

Input is multiple columns file

File is input & file is output.
Why is Oracle DB involved at all?

Report message to a moderator

Re: external table with single column [message #657373 is a reply to message #657370]

Mon, 07 November 2016 10:07

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Michel,

You external table command is good when running with few lines in the input file but when running with original file it shows null data, what can be the reason, is there any ways i can upload the file

Report message to a moderator

Re: external table with single column [message #657375 is a reply to message #657373]

Mon, 07 November 2016 10:10

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Because there are empty lines in your original file.

Report message to a moderator

Re: external table with single column [message #657376 is a reply to message #657375]

Mon, 07 November 2016 10:12

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

No, there are not any single empty line.

I might be wrong but output to external table show all the records have null data

Report message to a moderator

Re: external table with single column [message #657377 is a reply to message #657376]

Mon, 07 November 2016 10:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you did something wrong, fix it and it will work.

Report message to a moderator

Re: external table with single column [message #657378 is a reply to message #657377]

Mon, 07 November 2016 10:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And if you can't work it out setup a logfile and a badfile and see what they say

Report message to a moderator

Re: external table with single column [message #657379 is a reply to message #657378]

Mon, 07 November 2016 10:28

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi

Log file looks good

Field Definitions for table MY_TABLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

LINE CHAR (2000)
Record position (1, 2000)
Trim whitespace same as SQL Loader

LOG file opened at 11/07/16 16:25:42

Report message to a moderator

Re: external table with single column [message #657380 is a reply to message #657379]

Mon, 07 November 2016 10:33

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

In my file there are 8055 records and in the external table it show 8055 nulls

Report message to a moderator

Re: external table with single column [message #657381 is a reply to message #657379]

Mon, 07 November 2016 10:33

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post ALL what you do and get.
Post the COMPLETE log, discard and bad files.
And do it FORMATTED.

If log is good then result is good.

[Updated on: Mon, 07 November 2016 10:35]

Report message to a moderator

Re: external table with single column [message #657382 is a reply to message #657381]

Mon, 07 November 2016 10:44

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

input file sample

"EnvGrp_VCUM1";06;8;;B;"MCV_S_TrLnFw_M1";"Train line forward";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;9;;B;"MCV_S_TrLnRv_M1";"Train line reverse";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;A;;B;"CI_2LL2L02_S_TrLnFw_M1";"Forward train lines from D car local train unit";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;B;;B;"CI_2LL2L03_S_TrLnRv_M1";"Reverse train lines from D car local train unit";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;C;;B;"CI_3LL2L02_S_TrLnFw_M1";"Forward train lines from D car remote train unit";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;D;;B;"CI_3LL2L03_S_TrLnRv_M1";"Reverse train lines from D car remote train unit";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;E;;B;"CI_4LL2L02_S_TrLnFw_M1";"Forward train lines from A car remote train unit";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;F;;B;"CI_4LL2L03_S_TrLnRv_M1";"Reverse train lines from A car remote train unit";B;0;0;"ON";0;02
"EnvGrp_VCUK3";01;;;W;"MRV_X_TrnSpd_K3";"Train Speed";A;0.01;0;"km/h";0;02
"EnvGrp_VCUK3";02;;;W;"SSV_Z_Loc_K3";"Location from ATC";A;1;0;"";0;02
"EnvGrp_VCUK3";03;;;W;"MRV_W_PWMSigInp_K3";"PWM Signal Input";A;1;0;"";0;02
"EnvGrp_VCUK3";04;0;;B;"MCS_Forward_K3";"MCS Forward";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;1;;B;"MCS_Reverse_K3";"MCS Reverse";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;2;;B;"MCS_Inter_K3";"MCS Inter";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;3;;B;"MCS_Shutdown_K3";"MCS Shutdown";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;4;;B;"TMS_Train_Secure_K3";"TMS Train Secure";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;5;;B;"MCS_Protected_Manual_K3";"MCS Protected Manual";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;6;;B;"MCS_Manned_Automatic_K3";"MCS Manned Automatic";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;7;;B;"Maintenace_mode_K3";"Maintenace mode";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;8;;B;"Train_preparation_mode_K3";"Train preparation mode";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;9;;B;"Emergeny_brake_applied_K3";"Emergeny brake applied";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;A;;B;"All_doors_closed_K3";"Train Door Interlocked";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;B;;B;"TCMS_Master_K3";"TCMS Master";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;C;;B;"Second_master_K3";"Second master";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;D;;B;"Actuate_motoring_train_line_K3";"Local Actuate motoring train wire";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;E;;B;"Actuate_braking_train_line_K3";"Local Actuate braking train wire";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;F;;B;"W4_Spare16_K3";"Spare16";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;0;;B;"XCV_S_3PhAvl_K3";"3-ph voltage is available";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;1;;B;"W5_Spare2_K3";"W5_Spare2";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;2;;B;"W5_Spare3_K3";"W5_Spare3";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;3;;B;"W5_Spare4_K3";"W5_Spare4";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;4;;B;"W5_Spare5_K3";"W5_Spare5";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;5;;B;"W5_Spare6_K3";"W5_Spare6";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;6;;B;"DX22_In10_S_PrsVnt400VOK_K3";"PV1 400V OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;7;;B;"W5_Spare8_K3";"W5_Spare8";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;8;;B;"W5_Spare9_K3";"W5_Spare9";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;9;;B;"W5_Spare10_K3";"W5_Spare10";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;A;;B;"W5_Spare11_K3";"W5_Spare11";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;B;;B;"DX32_In10_S_PrsVnt400VOK_K3";"PV2 400V OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;C;;B;"W5_Spare13_K3";"W5_Spare13";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;D;;B;"W5_Spare14_K3";"W5_Spare14";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;E;;B;"W5_Spare15_K3";"W5_Spare15";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;F;;B;"W5_Spare16_K3";"W5_Spare16";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;0;;B;"DX32_In1_S_PrsVntFn1OK_K3";"PV2 fan 1 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;1;;B;"DX32_In2_S_PrsVntFn2OK_K3";"PV2 fan 2 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;2;;B;"DX32_In3_S_PrsVntFn3OK_K3";"PV2 fan 3 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;3;;B;"DX32_In4_S_PrsVntFn4OK_K3";"PV2 fan 4 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;4;;B;"DX32_In5_S_PrsVntFn5OK_K3";"PV2 fan 5 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;5;;B;"DX32_In6_S_PrsVntFn6OK_K3";"PV2 fan 6 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;6;;B;"KCV_S_PrsVnt2Fn1PrmErr_K3";"PV2 fan 1 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;7;;B;"KCV_S_PrsVnt2Fn2PrmErr_K3";"PV2 fan 2 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;8;;B;"KCV_S_PrsVnt2Fn3PrmErr_K3";"PV2 fan 3 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;9;;B;"KCV_S_PrsVnt2Fn4PrmErr_K3";"PV2 fan 4 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;A;;B;"KCV_S_PrsVnt2Fn5PrmErr_K3";"PV2 fan 5 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;B;;B;"KCV_S_PrsVnt2Fn6PrmErr_K3";"PV2 fan 6 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;C;;B;"DX32_In7_S_PrsVntOK_K3";"PV2 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;D;;B;"DX32_In8_S_PrsVntTfOK_K3";"PV2 Transformer OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;E;;B;"DX32_In9_S_PrsVntEmIvOK_K3";"PV2 Emergency inverter OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;F;;B;"DX31_In5_S_PrsVntEmCtOK_K3";"PV2 Emergency contactor OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";07;;;W;"KEV_X_PrsVnt1Tmp_K3";"Saloon temperature A or D car";A;1;0;"°C";0;02
"EnvGrp_VCUK3";08;;;W;"KEV_X_PrsVnt2Tmp_K3";"Saloon temperature B or C car";A;1;0;"°C";0;02

external table

create table my_table_ext (line varchar2(2000))
organization external (
   type oracle_loader
   default directory PDW_SOURCE
   access parameters (
      records delimited by newline
      BADFILE PDW_BAD:'test.bad'
      LOGFILE PDW_LOG:'test.log'
      nodiscardfile
      skip 1 
      fields 
      missing field values are null
      (line position (1:2000))
      )
   location (PDW_SOURCE:'test.txt') 
   )
reject limit unlimited

select * from my_table_ext

LINE

logs

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:25:42

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:29:12

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:29:13

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:29:45

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:32:13

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:32:14

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader


 LOG file opened at 11/07/16 16:41:29

Field Definitions for table MY_TABLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    LINE                            CHAR (2000)
      Record position (1, 2000)
      Trim whitespace same as SQL Loader

no bad files generated

Report message to a moderator

Re: external table with single column [message #657383 is a reply to message #657382]

Mon, 07 November 2016 10:54

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi

I ran the below query on external table

select length(line) from my_table_ext where line is not null
LENGTH(LINE)

111
29
37
37
13
65
67
75
77
81
71
75
73
65
77
73
75
71
81

there is data but it appear as nulls not displaying the content

Report message to a moderator

Re: external table with single column [message #657384 is a reply to message #657383]

Mon, 07 November 2016 10:59

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Discard file is missing.
Where does the data file come from?
How is it put in the database server?
What are both OS?

Report message to a moderator

Re: external table with single column [message #657385 is a reply to message #657383]

Mon, 07 November 2016 11:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well that's obviously not null, so it's either spaces or unprintable characters.
what does
select dump(line) from my_table_ext;
give?

Report message to a moderator

Re: external table with single column [message #657386 is a reply to message #657383]

Mon, 07 November 2016 11:01

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It works for me:

SQL> create table my_table_ext (line varchar2(2000))
  2  organization external (
  3     type oracle_loader
  4     default directory my_dir
  5     access parameters (
  6        records delimited by newline
  7        nobadfile
  8        nologfile
  9        nodiscardfile
 10        fields
 11        missing field values are null
 12        (line position (1:2000))
 13        )
 14     location ('myfile')
 15     )
 16  reject limit unlimited
 17  /

Table created.

SQL> select count(*) from  my_table_ext;
  COUNT(*)
----------
        61

1 row selected.

SQL> select * from  my_table_ext;
LINE
------------------------------------------------------------------------------------------------------
"EnvGrp_VCUM1";06;8;;B;"MCV_S_TrLnFw_M1";"Train line forward";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;9;;B;"MCV_S_TrLnRv_M1";"Train line reverse";B;0;0;"ON";0;02
"EnvGrp_VCUM1";06;A;;B;"CI_2LL2L02_S_TrLnFw_M1";"Forward train lines from D car local train unit";B;0;
"EnvGrp_VCUM1";06;B;;B;"CI_2LL2L03_S_TrLnRv_M1";"Reverse train lines from D car local train unit";B;0;
"EnvGrp_VCUM1";06;C;;B;"CI_3LL2L02_S_TrLnFw_M1";"Forward train lines from D car remote train unit";B;0
"EnvGrp_VCUM1";06;D;;B;"CI_3LL2L03_S_TrLnRv_M1";"Reverse train lines from D car remote train unit";B;0
"EnvGrp_VCUM1";06;E;;B;"CI_4LL2L02_S_TrLnFw_M1";"Forward train lines from A car remote train unit";B;0
"EnvGrp_VCUM1";06;F;;B;"CI_4LL2L03_S_TrLnRv_M1";"Reverse train lines from A car remote train unit";B;0
"EnvGrp_VCUK3";01;;;W;"MRV_X_TrnSpd_K3";"Train Speed";A;0.01;0;"km/h";0;02
"EnvGrp_VCUK3";02;;;W;"SSV_Z_Loc_K3";"Location from ATC";A;1;0;"";0;02
"EnvGrp_VCUK3";03;;;W;"MRV_W_PWMSigInp_K3";"PWM Signal Input";A;1;0;"";0;02
"EnvGrp_VCUK3";04;0;;B;"MCS_Forward_K3";"MCS Forward";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;1;;B;"MCS_Reverse_K3";"MCS Reverse";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;2;;B;"MCS_Inter_K3";"MCS Inter";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;3;;B;"MCS_Shutdown_K3";"MCS Shutdown";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;4;;B;"TMS_Train_Secure_K3";"TMS Train Secure";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;5;;B;"MCS_Protected_Manual_K3";"MCS Protected Manual";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;6;;B;"MCS_Manned_Automatic_K3";"MCS Manned Automatic";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;7;;B;"Maintenace_mode_K3";"Maintenace mode";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;8;;B;"Train_preparation_mode_K3";"Train preparation mode";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;9;;B;"Emergeny_brake_applied_K3";"Emergeny brake applied";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;A;;B;"All_doors_closed_K3";"Train Door Interlocked";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;B;;B;"TCMS_Master_K3";"TCMS Master";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;C;;B;"Second_master_K3";"Second master";B;0;0;"ON";0;02
"EnvGrp_VCUK3";04;D;;B;"Actuate_motoring_train_line_K3";"Local Actuate motoring train wire";B;0;0;"ON"
"EnvGrp_VCUK3";04;E;;B;"Actuate_braking_train_line_K3";"Local Actuate braking train wire";B;0;0;"ON";0
"EnvGrp_VCUK3";04;F;;B;"W4_Spare16_K3";"Spare16";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;0;;B;"XCV_S_3PhAvl_K3";"3-ph voltage is available";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;1;;B;"W5_Spare2_K3";"W5_Spare2";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;2;;B;"W5_Spare3_K3";"W5_Spare3";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;3;;B;"W5_Spare4_K3";"W5_Spare4";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;4;;B;"W5_Spare5_K3";"W5_Spare5";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;5;;B;"W5_Spare6_K3";"W5_Spare6";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;6;;B;"DX22_In10_S_PrsVnt400VOK_K3";"PV1 400V OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;7;;B;"W5_Spare8_K3";"W5_Spare8";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;8;;B;"W5_Spare9_K3";"W5_Spare9";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;9;;B;"W5_Spare10_K3";"W5_Spare10";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;A;;B;"W5_Spare11_K3";"W5_Spare11";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;B;;B;"DX32_In10_S_PrsVnt400VOK_K3";"PV2 400V OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;C;;B;"W5_Spare13_K3";"W5_Spare13";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;D;;B;"W5_Spare14_K3";"W5_Spare14";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;E;;B;"W5_Spare15_K3";"W5_Spare15";B;0;0;"ON";0;02
"EnvGrp_VCUK3";05;F;;B;"W5_Spare16_K3";"W5_Spare16";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;0;;B;"DX32_In1_S_PrsVntFn1OK_K3";"PV2 fan 1 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;1;;B;"DX32_In2_S_PrsVntFn2OK_K3";"PV2 fan 2 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;2;;B;"DX32_In3_S_PrsVntFn3OK_K3";"PV2 fan 3 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;3;;B;"DX32_In4_S_PrsVntFn4OK_K3";"PV2 fan 4 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;4;;B;"DX32_In5_S_PrsVntFn5OK_K3";"PV2 fan 5 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;5;;B;"DX32_In6_S_PrsVntFn6OK_K3";"PV2 fan 6 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;6;;B;"KCV_S_PrsVnt2Fn1PrmErr_K3";"PV2 fan 1 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;7;;B;"KCV_S_PrsVnt2Fn2PrmErr_K3";"PV2 fan 2 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;8;;B;"KCV_S_PrsVnt2Fn3PrmErr_K3";"PV2 fan 3 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;9;;B;"KCV_S_PrsVnt2Fn4PrmErr_K3";"PV2 fan 4 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;A;;B;"KCV_S_PrsVnt2Fn5PrmErr_K3";"PV2 fan 5 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;B;;B;"KCV_S_PrsVnt2Fn6PrmErr_K3";"PV2 fan 6 permanent error";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;C;;B;"DX32_In7_S_PrsVntOK_K3";"PV2 OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;D;;B;"DX32_In8_S_PrsVntTfOK_K3";"PV2 Transformer OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;E;;B;"DX32_In9_S_PrsVntEmIvOK_K3";"PV2 Emergency inverter OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";06;F;;B;"DX31_In5_S_PrsVntEmCtOK_K3";"PV2 Emergency contactor OK";B;0;0;"ON";0;02
"EnvGrp_VCUK3";07;;;W;"KEV_X_PrsVnt1Tmp_K3";"Saloon temperature A or D car";A;1;0;"░C";0;02
"EnvGrp_VCUK3";08;;;W;"KEV_X_PrsVnt2Tmp_K3";"Saloon temperature B or C car";A;1;0;"░C";0;02

61 rows selected.

So problem is at your side and not Oracle's.

[Edit: typo]

[Updated on: Tue, 08 November 2016 04:30]

Report message to a moderator

Re: external table with single column [message #657417 is a reply to message #657386]

Tue, 08 November 2016 03:56

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi Everyone,

Thanks for the help

Problem was with file not it is corrected and working fine

Report message to a moderator

Re: external table with single column [message #657419 is a reply to message #657417]

Tue, 08 November 2016 04:29

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And what was the problem with the file?

Report message to a moderator

Re: external table with single column [message #657420 is a reply to message #657419]

Tue, 08 November 2016 04:33

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

I am guessing that file had hexadecimal character data in it so i copid the whole file into notepad and name abc.txt and created external table abc.txt.

Report message to a moderator

Re: external table with single column [message #657427 is a reply to message #657364]

Tue, 08 November 2016 07:06

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

And wny would you want it to appear in your external table as a single column? That goes against the most fundamental principle of database design.

Report message to a moderator

Previous Topic:	Procedure execution issue and trace
Next Topic:	Query help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 00:19:37 CDT 2024