Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> CONSTANT SYSDATE IN EXTERNAL TABLE
redevil
post Mar 7 2017, 09:49 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 7-March 17
Member No.: 51,822



Hi to all ..

I want to add a column to existing external table valorized constantly with the sysdate , there is any way to do this ?

Below my script :

CREATE TABLE DBA21.ET_EXAMPLE_SYSDATE
(
COD_SISTEMA VARCHAR2(4 BYTE),
RECNUM NUMBER,
NUM_BOLLA VARCHAR2(10 BYTE),
COD_OPERAZIONE CHAR(1 BYTE),
STATO_COMM CHAR(4 BYTE),
STATO_VEICOLO CHAR(4 BYTE),
DAT_INVIO_MOV DATE,
job_id date
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY SOURCE_FILE
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
CHARACTERSET WE8ISO8859P1
LOGFILE SOURCE_FILE : 'MY_FILE.log'
BADFILE SOURCE_FILE : 'MY_FILE.bad'
DISCARDFILE SOURCE_FILE :'MY_FILE.disc'
FIELDS TERMINATED BY '|'
LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"RECNUM" integer,
NUM_BOLLA CHAR ,
COD_OPERAZIONE CHAR ,
STATO_COMM CHAR ,
STATO_VEICOLO CHAR ,
DAT_INVIO_MOV DATE 'YYYYMMDDHH24MISS',
job_id ???????????????
)
COLUMN TRANSFORMS (COD_SISTEMA FROM CONSTANT 'EAI')
)
LOCATION (SOURCE_FILE:'MOVIMENTAZIONE_EAI.TXT')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


thanks,
R
Go to the top of the page
 
+Quote Post
burleson
post Mar 8 2017, 11:20 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,378
Joined: 26-January 04
Member No.: 13



Hi,

>> I want to add a column to existing external table valorized constantly with the sysdate

I have never tried it, but Oracle does support "virtual" columns:

http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm

You have have to hard-code the constant in the table rows . . .




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th April 2017 - 07:11 PM