Home » SQL & PL/SQL » SQL & PL/SQL » Issue in Exxternal Table (Oracle 11g)
Issue in Exxternal Table [message #653139] Tue, 28 June 2016 06:36 Go to next message
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 Go to previous messageGo to next message
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 #653146 is a reply to message #653142] Tue, 28 June 2016 07:45 Go to previous messageGo to next message
jeeva7311
Messages: 5
Registered: June 2016
Location: chennai
Junior Member
Hi
I am new to this pl/sql platform. So no idea about this.

External Table won't support DML commands. So can you please enlighten me on how to create a "view" to displaying our current system date

TIA

Regards
Jeeva
Re: Issue in Exxternal Table [message #653149 is a reply to message #653146] Tue, 28 June 2016 07:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
jeeva7311 wrote on Tue, 28 June 2016 05:45
Hi
I am new to this pl/sql platform. So no idea about this.

External Table won't support DML commands. So can you please enlighten me on how to create a "view" to displaying our current system date

TIA

Regards
Jeeva

Please to simply Read The Fine Manual yourself

https://docs.oracle.com/database/121/nav/portal_booklist.htm

https://docs.oracle.com/database/121/SQLRF/toc.htm

https://docs.oracle.com/database/121/SQLRF/statements_8004.htm#SQLRF01504
Re: Issue in Exxternal Table [message #653159 is a reply to message #653149] Tue, 28 June 2016 08:46 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
In solomon's query he showed how to return the system date. It is the function SYSDATE which always returns the current date and time. If you only need the date use trunc(sysdate).

[Updated on: Tue, 28 June 2016 08:46]

Report message to a moderator

Re: Issue in Exxternal Table [message #653188 is a reply to message #653146] Tue, 28 June 2016 12:21 Go to previous messageGo to next message
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 #653262 is a reply to message #653188] Thu, 30 June 2016 08:26 Go to previous messageGo to next message
jeeva7311
Messages: 5
Registered: June 2016
Location: chennai
Junior Member
Hi

Successfully created view with the help of your coding. But still am facing the same issue.
Please refer the screenshot.
/forum/fa/13184/0/


[mod-edit: imaged inserted into message body by bb]
  • Attachment: I3.png
    (Size: 11.91KB, Downloaded 1247 times)

[Updated on: Thu, 30 June 2016 16:52] by Moderator

Report message to a moderator

Re: Issue in Exxternal Table [message #653263 is a reply to message #653262] Thu, 30 June 2016 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
jeeva7311 wrote on Thu, 30 June 2016 06:26
Hi

Successfully created view with the help of your coding. But still am facing the same issue.
Please refer the screenshot.



Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

It is not possible to say what you do wrong, since you do NOT show us what you actually do.

use COPY & PASTE to post complete sqlplus session to show what you do & how Oracle responds.
Re: Issue in Exxternal Table [message #653264 is a reply to message #653263] Thu, 30 June 2016 08:41 Go to previous messageGo to next message
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.
Re: Issue in Exxternal Table [message #653265 is a reply to message #653262] Thu, 30 June 2016 08:51 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Did you test external table before testing view? What happens when you issue:

      SELECT  *
        FROM  CONTROLS_EXT
/

SY.
Previous Topic: Conversion Approach
Next Topic: How can I modify length of a column in type definition.
Goto Forum:
  


Current Time: Thu Mar 28 15:54:27 CDT 2024