Issue in Exxternal Table [message #653139] |
Tue, 28 June 2016 06:36 |
|
jeeva7311
Messages: 5 Registered: June 2016 Location: chennai
|
Junior Member |
|
|
Hi Friends,
Please help me out with codings for displaying the "System Date" under "Created Date" column automatically during the Execution of External table.
CREATE TABLE "CONTROLS_EXT"
( "ID_340B" VARCHAR2(50 BYTE),
"PRODUCT_SERVICE_ID" NUMBER,
"DRUG_NAME" VARCHAR2(100 BYTE),
"ELIGIBLE_340B" VARCHAR2(100 BYTE),
"EFFECTIVE_DATE" DATE,
"EXPIRATION_DATE" DATE,
"CREATED_DATE" DATE,
"DATE_UPDATE" DATE,
"CREATED_BY" VARCHAR2(100 BYTE),
"UPDATED_BY" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "CONTROLS"
ACCESS PARAMETERS
( records delimited by newline
badfile CONTROLS_bad:'CONTROL_EXT%a_%p.bad'
logfile CONTROLS_log:'CONTROL_EXT%a_%p.log'
fields terminated by ','
missing field values are null
(
ID_340B,
PRODUCT_SERVICE_ID,
DRUG_NAME,
ELIGIBLE_340B,
EFFECTIVE_DATE char date_format date mask "MM/DD/YYYY",
EXPIRATION_DATE char date_format date mask "MM/DD/YYYY",
CREATED_DATE char date_format date mask "MM/DD/YYYY",
DATE_UPDATE char date_format date mask "MM/DD/YYYY",
CREATED_BY,
UPDATED_BY
)
)
LOCATION
( 'controls.csv'
)
)
REJECT LIMIT UNLIMITED
PARALLEL ;
Here the output will be,
It Displays the table of contents fetched from csv files (controls.csv) .
My Issue:
Now below the Created Date column, the System Date should be display automatically.
Any ideas?
|
|
|
Re: Issue in Exxternal Table [message #653142 is a reply to message #653139] |
Tue, 28 June 2016 07:17 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
External tables don't support built-in functions and there is no need to - just add whatever you need to select:
SELECT ID_340B,
PRODUCT_SERVICE_ID,
DRUG_NAME,
ELIGIBLE_340B,
EFFECTIVE_DATE,
EXPIRATION_DATE,
CREATED_DATE,
SYSDATE SYSTEM_DATE,
DATE_UPDATE,
CREATED_BY,
UPDATED_BY
FROM CONTROLS_EXT
/
Or create a view from the above select and use it instead selecting directly from external table.
SY.
|
|
|
|
|
|
Re: Issue in Exxternal Table [message #653188 is a reply to message #653146] |
Tue, 28 June 2016 12:21 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
External table is no different from a regular read only table. It is transparent to caller if data comes from database (in case of regular table) or from a file (in case of external table). You create view same way as usual:
CREATE OR REPLACE
VIEW CONTROLS_EXT_VW
AS
SELECT ID_340B,
PRODUCT_SERVICE_ID,
DRUG_NAME,
ELIGIBLE_340B,
EFFECTIVE_DATE,
EXPIRATION_DATE,
CREATED_DATE,
SYSDATE SYSTEM_DATE,
DATE_UPDATE,
CREATED_BY,
UPDATED_BY
FROM CONTROLS_EXT
/
SY.
|
|
|
|
|
Re: Issue in Exxternal Table [message #653264 is a reply to message #653263] |
Thu, 30 June 2016 08:41 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
For date fields I had to use the following to make it work
Hire_Date CHAR(10) date_format DATE mask "MM/DD/YYYY",
Also have you looked in your log file. Any problem with decoding will be logged in it and why it failed.
|
|
|
|