Creating External Table and Using - Oracle Database

Step1: Create or get the name of Public Database Directory

SELECT * FROM  ALL_DIRECTORIES WHERE DIRECTORY_NAME LIKE 'DIRNAME%'

CREATE OR REPLACE directory temp_files_dir AS '/temp/sql';

Step2: Check if there are read/write permission on the given path of dir
Step3: Place the file into dir
Step4. Create Ext Table

Syntax: for Creating external tables

CREATE TABLE temp_external_table_ext
  (
     column1 VARCHAR2(500),
     column2 VARCHAR2(500)

  ) organization external 

( TYPE oracle_loader 

DEFAULT directory temp_files_dir 

ACCESS parameters 

( records delimited BY newline 

 badfile 'bad_%a_%p.bad' 

 LOGFILE 'log_%a_%p.log' 

 fields terminated BY ',' 

missing field VALUES are NULL 

( Column1 CHAR(500), 

  Column2 CHAR(500)
 ) 

)
location ('file.csv') ) 

PARALLEL 5 reject limit UNLIMITED; 

This combination of segment values already exists. (ROUTINE=fdfvcc)

Error: Getting error while adding new item in item master in oracle apps r12. This combination of segment values already exists. (ROUTINE=fdfvcc)

Answer: If you have deleted the item from backend, then see no item exists in table ENI.ENI_OLTP_ITEM_STAR. If yes then remove it from there as well.

Find the dates for the given year using query in oracle

1. --Find the dates for the given year using the query in Oracle
2. --Find dates between given date ranges, the same query will be used just tweak the cal_year
3. --Just place year in the given: CAL_YEAR and execute the query to get your result set

SELECT daterange, 
       dayname 
FROM   (SELECT ( To_date(01 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(01 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(01 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(01 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(02 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(02 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(02 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(02 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(03 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(03 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(03 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(03 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(04 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(04 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(04 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(04 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(05 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(05 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(05 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(05 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(06 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(06 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(06 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(06 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(07 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(07 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(07 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(07 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(08 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(08 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(08 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(08 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(09 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(09 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(09 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(09 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(10 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(10 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(10 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(10 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(11 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(11 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(11 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(11 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31 
        UNION 
        SELECT ( To_date(12 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) 
               DateRange, 
               To_char(To_date(12 
                               ||' ' 
                               ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL, 'DAY') 
               DAYNAME 
        FROM   dual 
        WHERE  ( To_date(12 
                         ||' ' 
                         ||:CAL_YEAR, 'MM YYYY') - 1 + LEVEL ) <= Last_day( 
                      To_date(12 
                              ||' ' 
                              ||:CAL_YEAR, 'MM YYYY')) 
        CONNECT BY LEVEL <= 31);