Validation on Concurrent Program Parameter

Recently While working on one of the Finance Report , i got one requirement to Validate Concurrent Program Parameters.

Requirement was : Date Range should not allow Future date to be picked.

So to fulfill this requirement , i had to create one Value set with Type as Special.

Go to Edit Information

Select Event as Validate

Write the custom code to validate your requirements.

Add this value Set to your Program and Verify the results.

ORA-01861: literal does not match format string

Cause:  FDPSTP failed due to ORA-01861: literal does not match format string
ORA-06512: at line 1

Starting with Oracle Application Version 11.5.10.2, Oracle does not allow you to use FND_DATE4 parameters and instead recommends FND_STANDARD_DATE data type. Thus, concurrent program using date parameters would be assigned value set FND_STANDARD_DATE. 

The point to be noted here is, format mask for the FND_STANDARD_DATE data type is "YYYY/MM/DD HH24:MI:SS". However, in most of the date format in Oracle Database will have format mask "DD-MON-YYYY HH24:MI:SS". Because of difference in above two format masks, you get error "ORA-01861: literal does not match format string".

Following is the workaround to avoid such errors: 
1. Define the date parameter as VARCHAR2 in Oracle Procedure. 
2. Register Date Parameter in Concurrent Program with Value Set as "FND_STANDARD_DATE".
3. Use FND_DATE.CANONICAL_TO_DATE function to convert varchar2 value to oracle date format & then use this value in your oracle procedure. 

Example: 


CREATE PROCEDURE XX_TEMP (errbuf      OUT  VARCHAR2
                         ,retcode     OUT  VARCHAR2
                         ,i_from_date  IN  VARCHAR2
                         ,i_to_date    IN  VARCHAR2 
                         )
IS
   ld_from_date DATE := FND_DATE.CANONICAL_TO_DATE (i_from_date);
   ld_to_date   DATE := FND_DATE.CANONICAL_TO_DATE (i_to_date);

   /* Use  ld_from_date and ld_to_date instead of i_from_date and i_to_date in procedure */
END XX_TEMP;
/

Find File Header Version in Unix

My requirement was to update File Header for one of the SR with Oracle, Generally DBA's take care of this activity , but during odd hours one can also find out the File header version with "adident" Command.

Example:

SR Asked to update the file version for the file named "appvndrb.pls" from AP_TOP

Login into Putty

Go to path where file is kept in Server

cd $AP_TOP/patch115/sql

Execute below Command

$ adident Header appvndrb.pls  (file name)

It Should give you the file header version.

Sed Command in Linux/Unix with examples

SED command in UNIX is stands for stream editor and it can perform lot’s of function on file like, searching, find and replace, insertion or deletion.
Though most common use of SED command in UNIX is for substitution or for find and replace.

1.Replacing or substituting string : Sed command is mostly used to replace the text in a file. The below simple sed command replaces the word "shobhit" with “sharma” in the file.

$sed 's/shobhit/sharma/' Demofile.txt

Here the “s” specifies the substitution operation. The “/” are delimiters.

By default, the sed command replaces the first occurrence of the pattern in each line and it won’t replace the second, third…occurrence in the line by this command.


2.Replacing the nth occurrence of a pattern in a line : Use the /1, /2 etc flags to replace the first, second occurrence of a pattern in a line. The below command replaces the second occurrence of the word "shobhit" with "sharma” in each line of the file.

$sed 's/shobhit/sharma/2' Demofile.txt

3.Replacing all the occurrence of the pattern in a line : The substitute flag /g (global replacement) specifies the sed command to replace all the occurrences of the string in the line.

$sed 's/shobhit/sharma/g' Demofile.txt


4.Replacing string on a range of lines : You can specify a range of line numbers to the sed command for replacing a string.

$sed '1,3 s/shobhit/sharma/' Demofile.txt

Here the sed command replaces the lines with range from 1 to 3. Another example is

       $sed '2,$ s/shobhit/sharma/' Demofile.txt

Here $ indicates the last line in the file. So the sed command replaces the text from second line to last line in the file.


5.Deleting lines from a particular file : SED command can also be used for deleting lines from a particular file. SED command is used for performing deletion operation without even opening the file


 Examples:

1. To Delete a particular line say n in this example

$ sed 'nd' Demofile.txt
Example:
$ sed '5d' Demofile.txt


2. To Delete a last line
$ sed '$d' Demofile.txt


3. To Delete line from range x to y

$ sed 'x,yd' Demofile.txt
Example:
$ sed '3,6d' Demofile.txt

5. To Delete from nth to last line

$ sed 'nth,$d' Demofile.txt
Example:
$ sed '12,$d' Demofile.txt


6. To Delete pattern matching line

$ sed '/pattern/d' Demofile.txt
Example:
$ sed '/abc/d' Demofile.txt

grep command in Unix

grep (globally search for regular expression and print out) command in Unix/Linux

The grep filter searches a file for a particular pattern of characters, and displays all lines that contain that pattern.

grep [options] pattern [files]

Options Description
-c : This prints only a count of the lines that match a pattern
-h : Display the matched lines, but do not display the filenames.
-i : Ignores, case for matching
-l : Displays list of a filenames only.
-n : Display the matched lines and their line numbers.
-v : This prints out all the lines that do not matches the pattern
-e exp : Specifies expression with this option. Can use multiple times.
-f file : Takes patterns from file, one per line.
-E : Treats pattern as an extended regular expression (ERE)
-w : Match whole word
-o : Print only the matched parts of a matching line,
 with each such part on a separate output line.



 1. Case insensitive search : The -i option enables to search for a string case insensitively in the give file.   It Can matches the words like “SHOBHIT”, “Shobhit”, “shobhit”, "ShoBhit"
 
$grep -i "ShoBhit" Demofile.txt

2. Displaying the count of number of matches : We can find the number of lines that matches the given string/pattern

$grep -c "Shobhit" Demofile.txt

3. Display the file names that matches the pattern : We can just display the files that contains the given string/pattern.

$grep -l "Shobhit" *

or
 
$grep -l "Shobhit" Demofile1.txt Demofile2.txt Demofile3.txt 

4. Checking for the whole words in a file : By default, grep matches the given string/pattern even if it found as a substring in a file.The -w option to grep makes it match only the whole words.

$ grep -w "Shobhit" Demofile.txt

5. Show line number while displaying the output using grep -n : To show the line number of file with the line matched.

$ grep -n "Shobhit" Demofile.txt

6. Inverting (Exlude) the pattern match : You can display the lines that are not matched with the specified search sting pattern using the -v option.

$ grep -v "Shobhit" Demofile.txt

7. Matching the lines that start with a string : The ^ regular expression pattern specifies the start of a line. This can be used in grep to match the lines which start with the given string or pattern.

$ grep "^Shobhit" Demofile.txt

8.Matching the lines that end with a string : The $ regular expression pattern specifies the end of a line.This can be used in grep to match the lines which end with the given string or pattern.
 
$ grep "hit$" Demofile.txt

9.Specifies expression with -e option. Can use multiple times :

$grep –e "Shobhit" –e "Sobhit" –e "Shobit" Demofile.txt