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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: external table with single column [message #657386 is a reply to message #657383] |
Mon, 07 November 2016 11:01 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|
|
|
|