Oracle PL/SQL – Calculating Weekdays between two dates

Calculating total number of Week Days between two given dates is most common requirement. This article shows few methods to do this.

 

 

Calculating total number of Week Days between two given dates

————————————————————-

CREATE OR REPLACE FUNCTION TotWeekDays( FromDate DATE ,

ToDate DATE )

RETURN NUMBER IS

ToTalSunDays NUMBER := NEXT_DAY(ToDate – 7,’SUNDAY’) -

NEXT_DAY(FromDate – 1,’SUNDAY’) ;

 

ToTalSaturDays NUMBER := NEXT_DAY(ToDate – 7,’SATURDAY’) -

NEXT_DAY(FromDate – 1,’SATURDAY’) ;

BEGIN

RETURN (ToDate – FromDate – (TotalSundays+TotalSaturdays)/7 -1) ;

END ;

Executing this function in a pl/sql block…

 

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 BusinessDays NUMBER;

3 BEGIN

4 BusinessDays := TotWeekDays(‘01-MAY-2001′,’31-MAY-2001′) ;

5 DBMS_OUTPUT.PUT_LINE(‘Total Business Days : ‘||TO_CHAR(BusinessDays) ) ;

6* END;

SQL> /

Total Business Days : 23

Very Common Questions on Oracle SQL

Q- Can two different users own tables with the same name?

A- Yes, tables are unique by username.tablename.

NOTE: Problems will occur if synonyms are used to make table names the same name; private synonyms override public synonyms.

 Q- Is it possible to update another table with 2 concatenated columns?
A- Yes, the following update statement will work:
UPDATE tablename SET colname = columnX || columnY
WHERE colname = ¤t_value_for_this_column ;
NOTE: The result is truncated to 255 characters.

Q- How can a new view be created from two old views that have common field names?
A- Use aliases in the fields. For example:
CREATE VIEW x AS
SELECT tab1.fld1 afld1, tab2.fld1 bfld1
FROM tab1,tab2
WHERE tab1.fld1=tab2.fld1;

Q- Can columns be called TO and FROM?
A- These are reserved words and cannot be used to name database objects.

Q- Is it allowed to have % in the field name?
A- Allowed, Yes! Recommended, No! It can be confused with the wildcard symbol ‘%’ used in the LIKE clause.

 

Q- Can you index columns defined in views?

A- No, view columns themselves cannot be indexed.

 

Q- Is it possible to prompt a user for data when running a SQL script?

A- Yes, prefix the column name with & or &&.

The following method can be used :

SELECT fld1,fld2

FROM tab1,tab2

WHERE key1=’&key1′;

Prompts for Enter value from key1: when data is entered this produces an old/new information listing which may be turned off with

SET VERIFY OFF

If && is used, the value is prompted for once and then used automatically if that value is used again during that SQL*Plus session.

 Q- How do you select the first 10 rows of a table?
A- Use system variable rownum in where clause. For example:
SELECT *
FROM table1
WHERE ROWNUM < 11;

Q- How do I return the first 10 values that occur most frequently?
A- CREATE VIEW v1 as:
SELECT name, count(*) num
FROM table
GROUP BY name;

sELECT name,num
FROM v1 a
WHERE 10 > (SELECT COUNT(*)
FROM v1
WHERE a.num < num)
ORDER BY num;

How to generate a delimiter in spool output (Excel .csv files)

In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma:

Example:
select empno|| ‘,’||ename||’&’||mgr from X;

Other option is using:
SQL> set colsep ‘,’
SQL> spool c:\testexcel.csv
SQL> select * from emp;

Change some of the default SQL*Plus parameters, that will be garbage for Excel:
feedback=off
newpage=none
termout=off
header=off

If some columns are empty, be aware to include the delimiter, too:
nvl(to_char(col2),’,')

Useful Unix Utilities to Data File Management

The Unix operating system has a number of utilities that can be very useful for pre-processing data files to be loaded with SQL*Loader. Even when the same functionality can be achieved through SQL*Loader, the utilities described

here will be much faster. Data warehousing applications, in particular, can benefit greatly from these utilities.

 EXAMPLE 1
Let us assume a load with the following control file:
    LOAD DATA
    INFILE ‘example1.dat’
    INTO TABLE emp
    (empno        POSITION(01:04)    INTEGER EXTERNAL,
    ename        POSITION(06:14)    CHAR,
    job        POSITION(16:24) CHAR,
    mgr        POSITION(26:29) INTEGER EXTERNAL,
    sal        POSITION(31:37) DECIMAL EXTERNAL,
    comm        POSITION(39:42) DECIMAL EXTERNAL,
    deptno        POSITION(44:45)    INTEGER EXTERNAL)

Here are the contents of data file example1.dat:
    7782 CLARK MANAGER 7839 2572.50 0.20 10
    7839 KING PRESIDENT 5850.00 10
    7654 MARTIN SALESMAN 7698 1894.00 0.15 30    

EXAMPLE 2
Let us assume another load with the following control file:
    LOAD DATA
    INFILE ‘example2.dat’
    INTO TABLE dept
    FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
    (deptno, dname, loc)

Below are the contents of data file example2.dat:
    12,RESEARCH,”SARATOGA”
    10,”ACCOUNTING”,CLEVELAND
    13,”FINANCE”,”BOSTON”

PRESORTING THE DATA FILE
The performance of direct path loads can be significantly improved by presorting the input data on indexed columns. Pre-sorting minimizes the demand for temporary segments during the load.

The Unix command to be used for presorting is “sort”.
In Example 1, suppose you have added the SORTED INDEXES (empno) clause to the control file to indicate that fields in the data file are presorted on the EMPNO column. To do that presorting, you would enter at the Unix prompt:
    % sort +0 -1 example1.dat > example1.srt

This will sort file example1.dat by its first field (by default fields are delimited by spaces and tabs) and send the output to file example1.srt:
     7654 MARTIN SALESMAN 7698 1894.00 0.15 30
    7782 CLARK MANAGER 7839 2572.50 0.20 10
    7839 KING PRESIDENT 5850.00 10

In Example 2, if you wanted to sort file example2.dat by column DNAME, you would enter:
    % sort -t, -d +1 -2 example2.dat > example2.srt
where “-t,” indicates that commas are delimiters, “-d” causes sort to consider only letters and digits in comparisons, and example2.srt is the output file:
    10,”ACCOUNTING”,CLEVELAND
    13,”FINANCE”,”BOSTON”
    12,RESEARCH,”SARATOGA”

ELIMINATING FIELDS FROM THE DATA FILE
Often, it is necessary to remove one or more fields from all the records in the data file. The Unix command that does that is “cut”.
In Example 1, if you want to eliminate the COMM field altogether from the data file, enter at the Unix prompt:
    % cut -c1-38,44- example1.dat > example1.cut
where the “-c” option specifies the character ranges that you want to extract from each record. The output file example1.cut contains:
    7782 CLARK MANAGER 7839 2572.50 10
    7839 KING PRESIDENT 5850.00 10
    7654 MARTIN SALESMAN 7698 1894.00 30

In Example 2, to eliminate the LOC field from the data file, you would enter:
    % cut -f1-2 -d, example2.dat > example2.cut
where “-f1-2″ indicates you want to extract the first two fields of each record and “-d,” tells cut to treat comma as a delimiter. The output file example2.cut would contain:
    12,RESEARCH
    10,”ACCOUNTING”
    13,”FINANCE”

REPLACING CHARACTERS IN THE DATA FILE
Two Unix commands can be used here: “tr” or “sed”.
For instance, if you want to replace all double quotes in the data file in Example 2 by single quotes, you may enter:
    % cat example2.dat | tr \” \’ > example2.sqt
The piped “cat” is necessary because tr’s input source is the standard input. Single and double quotes are preceded by backslashes because they are special characters. The output file will be:
    12,RESEARCH,’SARATOGA’
    10,’ACCOUNTING’,CLEVELAND
    13,’FINANCE’,'BOSTON’

Similarly, to substitute colons for commas as delimiters in Example 2, you may enter:
    % sed ’s/,/:/g’ example2.dat > example2.cln
The output would be:
    12:RESEARCH:”SARATOGA”
    10:”ACCOUNTING”:CLEVELAND
    13:”FINANCE”:”BOSTON”

ELIMINATING CHARACTERS FROM THE DATA FILE
Just as for replacing characters, “tr” and “sed” can be used for eliminating them from the data file.
If you want to remove all double quotes from the data file in Example 2, you may type:
   % cat example2.dat | tr -d \” > example2.noq
The contents of file example2.dat are piped to the tr process, in which the “-d” option stands for “delete”. The output file example2.noq would look like:
    12,RESEARCH,SARATOGA
    10,ACCOUNTING,CLEVELAND
    13,FINANCE,BOSTON

An identical result would be obtained by using sed:
    % sed ’s/\”//g’ example2.dat > example2.noq
The string in single quotes indicates that double quotes should be replaced by an empty string globally in the input file. Another interesting usage of tr would be to squeeze multiple blanks between fields down to a single space character. That can be achieved by doing:

     % cat example1.dat | tr -s ‘ ‘ ‘ ‘ > example1.sqz
The output file would look like:
    7782 CLARK MANAGER 7839 2572.50 0.20 10
    7839 KING PRESIDENT 5850.00 10
    7654 MARTIN SALESMAN 7698 1894.00 0.15 30

INSERTING CHARACTERS INTO THE DATA FILE
A typical situation in which you may need to insert characters into the datafile would be to convert a fixed position data file into a delimited one. The data file in Example 1 is a fixed position one. To convert it into a file delimited by commas, you would enter
    % cat example1.dat | tr -s ‘ ‘ ‘,’ > example1.dlm
and obtain
    7782,CLARK,MANAGER,7839,2572.50,0.20,10
    7839,KING,PRESIDENT,5850.00,10
    7654,MARTIN,SALESMAN,7698,1894.00,0.15,30

MERGING DIFFERENT FILES INTO A SINGLE DATA FILE
Merging can be done by using “paste”. This command allows you to specify a list of files to be merged and the character(s) to be used as delimiter(s). For instance, to merge the data files in Examples 1 and 2, you may enter:
    % paste -d’ ‘ example1.dat example2.dat > example.mrg
where “-d’ ‘” specifies a blank character as the delimiter between records being merged and example.mrg is the merged output file:
    7782 CLARK MANAGER 7839 2572.50 0.20 10 12,RESEARCH,”SARATOGA”
    7839 KING PRESIDENT 5850.00 10 10,”ACCOUNTING”,CLEVELAND
    7654 MARTIN SALESMAN 7698 1894.00 0.15 30 13,”FINANCE”,”BOSTON”

ELIMINATING DUPLICATE RECORDS/FIELDS FROM THE DATA FILE
The Unix command for this is “uniq”. It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq.
In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
    % uniq +43 example1.dat > example1.unq
The “+43″ indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
    7782 CLARK MANAGER 7839 2572.50 0.20 10
    7654 MARTIN SALESMAN 7698 1894.00 0.15 30

COUNTING THE NUMBER OF LINES IN THE DATA FILE
Use “wc”. For example:
    % wc -l example1.dat
    3 example1.dat
which indicates that the file contains three lines.

CONVERTING BETWEEN ASCII AND EBCDIC
The “dd” Unix command can be used to convert EBCDIC data files to ASCII and vice-versa. For example:
    % dd if=example1.ebc conv=ascii > example1.asc
takes example1.ebc as EBCDIC input file, converts it into ASCII, and writes the converted output to file example1.asc.

 CASE CONVERSIONS
The “dd” and “tr” commands can also be used for converting between uppercase and lowercase characters. Because Oracle is currently case-sensitive, this can be useful in many situations.
For example, to convert all characters in file example1.dat from uppercase to lowercase, simply enter
    % dd if=example1.dat conv=lcase > example1.low
or
    % cat example1.dat | tr “[A-Z]” “[a-z]” > example1.low
The contents of output file example1.low will be:
    7782 clark manager 7839 2572.50 0.20 10
    7839 king president 5850.00 10
    7654 martin salesman 7698 1894.00 0.15 30

To convert this file back to uppercase characters, type
    % dd if=example1.low conv=ucase > example1.dat
or
    % cat example1.low | tr “[a-z]” “[A-Z]” > example1.dat

A Beginner’s Overview on Oracle SQL*Loader

This article will give you a general overview of the syntax used in SQL*Loader. The article will not go into any detailed level of the syntax and the associated arguments/options to the SQL*Loader utility.

 

The command line syntax is comprised of the command itself (sqlldr), two mandatory arguments and several optional arguments. The arguments are specified as keywords assigned a value, e.g. LOG=<filename>. Below is a short description of some of the arguments, for an extensive and full description of all the keywords please read ch. 4 in the Oracle 9i Utilities Guide and ch. 6 in the Oracle 8i Utilities Guide.

 

Mandatory arguments:

username/password

CONTROL=<filename>

 

Optional arguments:

LOG=<filename> Specifies a log file

BAD=<filename> Specifies the file where all bad records are put

DATA=<filename> Specifies the data to be loaded into the database

DISCARD=<filename> Specifies where the discarded records go

ERRORS=<integer> Specifies number of errors allowed before termination

DIRECT=<TRUE|FALSE> Specifies if loading method is DIRECT or CONVENTIONAL

PARFILE=<filename> Specifies the parameter file to be used by SQL*Loader

 

If the username/password argument is not specified, you will be prompted for it if there is a 2nd argument on the command line. If there are no arguments at all on the command line, e.g. sqlldr , you will get the help page for SQL*Loader. Here you can see what options are available to you.

 

All the arguments can be supplied on the command line, but they can also be put into a parameter file, which will be read by SQL*Loader if the PARFILE argument is used. If SQL*Loader is not supplied with a username/password or a control file, you will be prompted for the missing information. Any other argument can be left out and will take a default value. The are more arguments available and they can be listed by running the sqlldr command without any arguments. For a more detailed description of all arguments, please refer to the Utilities Guide.

 

The control file contains further parameters used by SQL*Loader to load the data. These are described in the Utilities Guide.

 

Example:

——–

sqlldr scott/tiger control=emp.ctl log=emp.txt direct=true

 

This example will load data into the database using the DIRECT method. Furthermore, the data will be loaded into the schema of SCOTT. The control file emp.ctl will hold all the other directives (arguments) needed by SQL*Loader to

load the data into the proper table(s).

Removing ”^M” from SQL*Loader datafile

You notice that you have extra ‘^M’ characters at the end of your records in your SQL*Loader datafile. You need to remove them so your load will work successfully.

————————–

You can remove the ^M by doing the following:

 

Bring the datafile into the Unix editor vi. In command mode you would type in:

 

:1,$s/^V^M//

 

Make sure you are entering ‘control V’ and ‘control M’. The ‘control V’ says to accept the next character exactly as I type it. Adding the two slashes at the end of the command says to subsitute the ^M with null.

 

The extra ^M characters are most likely due to moving the file from one platform to another. The carriage returns or end-of-line commands are sometimes interpreted differently.

 

You can see this if you move a file from a PC to a UNIX system or if you copy a file from one system through a gateway to a different system.

Variable Input file handling in Oracle SQL*Loader

SQL*Loader cannot handle variable input file names. This can be needed for periodic loads to Oracle database with file names changing each time. This article shows four workarounds to use variable input file names with SQL*Loader.

This article is helpful for SQL*Loader users need to load from input files with variable names. It covers four detailed workaround examples.

How to Pass the Input File with Variable Names to SQL*Loader Dynamically

SQL*Loader cannot handle variable input file names. The parameter INFILE specified in the control file doesn’t allow variable names – if the name of the data file is changed, then the control file must be edited and the name of the inputfile must also be changed.

This article describes 4 workarounds, how to pass the input file to SQL*Loader dynamically, without changing the control file every time the input file name is changed.

For a better understanding we will consider the following test case:

– The table to be loaded

CREATE TABLE LDR_TEST

(

ID NUMBER,

TEXT VARCHAR2(10)

);

– Input data records (file LDR.DAT)

1;Text 1

2;Text 2

3;Text 3

 

1. The simplest method to do this is to rename the input file (accordingly to the control file parameter INFILE). If the control file (LDR1.CTL) was defined as:

LOAD DATA

INFILE ‘LDR.DAT’

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

then every new input file must be renamed to LDR.DAT before starting the load process.

 

******************

* SAMPLE 1 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) renames the input file name ($1)

# to LDR.DAT that will be later used by SQL*Loader.

# Note: The shell must have at least 774 permissions to run. (chmod 774)

# This works only for UNIX platforms

# Rename (or copy) the file

mv $1 LDR.DAT

# cp $1 LDR.DAT

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR1.CTL

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

2. Pass the data file to SQL*Loader with the command line parameter DATA. In this case the control file (LDR2.CTL) doesn’t contain the parameter INFILE.

LOAD DATA

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

The data file is specified in the command line (parameter DATA).

******************

* SAMPLE 2 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) passes the inputfile ($1) to SQL*Loader through

# the parameter DATA in command line

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR2.CTL data=$1

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

3. Use a pipe to transfer the data from data file to SQL*Loader. First, the records from the data file will be sent in a pipe, and then SQL*Loader will receive the records from the pipe and insert them in the database. The command lines for different operating systems are the following:

VMS (7.2+) pipe type ldr.dat | sqlldr scott/tiger control=ldr3.ctl

UNIX cat LDR.DAT | sqlldr scott/tiger control=LDR3.CTL

NT type ldr.dat | sqlldr scott/tiger control=ldr3.ctl

 

The appropriate control files (LDR3.CTL) are these:

UNIX and NT

———–

LOAD DATA

INFILE “-”

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

VMS

—

LOAD DATA

INFILE SYS$INPUT

TRUNCATE

INTO TABLE TEST_LDR

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

 

******************

* SAMPLE 3 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) uses a pipe to pass input file ($1) to SQL*Loader.

# Start SQL*Loader with the new control file

cat $1 | sqlldr scott/tiger control=LDR3.CTL

—————————— Shell end ——————————

Change permissions for the LOAD.SH shell to -rwxrwxr–:

#> chmod 774 LOAD.SH

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new inputfile.

 

4. Use a place holder in control file and automatically change it before starting the load process. In this example the control file (LDR_TEMPLATE.CTL) looks like this:

LOAD DATA

INFILE $INPUT

TRUNCATE

INTO TABLE LDR_TEST

FIELDS TERMINATED BY “;”

(

ID,

TEXT

)

The idea is to programatically change the name of the place holder $INPUT before starting SQL*Loader. This will be done with the following UNIX shell:

******************

* SAMPLE 4 *

******************

—————————– Shell begin —————————–

#!/bin/sh

# This shell (LOAD.SH) passes the input file name ($1) to the SQL*Loader

# control file – it searches the place holder $INPUT in the control file

# LDR_TEMPLATE.CTL, changes it to the new name ($1) and save it in the new

# control file named LDR4.CTL. This file will be later used by SQL*Loader.

# Change the place holder

cat LDR_TEMPLATE.CTL | sed -e “s%\$INPUT%’$1′%” > LDR4.CTL

# Start SQL*Loader with the new control file

sqlldr scott/tiger control=LDR4.CTL

—————————— Shell end ——————————

Start the shell with:

#> ./LOAD.SH /tmp/files/LDR.DAT

where /tmp/files/LDR.DAT is the new input file.