Oracle compare dates with timestamp

Dates with timestamp can be compared by using

to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS')

Date from : '07/11/13 01:01:01'
Date To :  '07/11/13 23:59:59'

Note that it's 24Hr format.


to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS') between to_date('07/11/13 01:01:01','DD/MM/YY HH24:MI:SS') and to_date('07/11/13 23:59:59','DD/MM/YY HH24:MI:SS')

Leading zero's XML Publisher

When chosen Excel as XML publisher output, text with prefix zeros will be converted to number in excel output. To maintain the format

Option1
Change xml tag  in template from
<?your_field?> to ="<?your_field?>"


Option2
Add NONBREAKING SPACE before the column.

Place cursor in front of the column you need press CTRL + SHIFT + SPACE. You can also choose this from menu.

Insert -  Symbol - Special Characters.

java.lang.NullPointerException - XML Bursting Error

Perform the following check to resolve the error java.lang.NullPointerException in XML Bursting.

1. Check if your bursting XML definition and concurrent program belongs to same application.
2. Bursting XML definition name (CODE) should match the concurrent program short name for which it's deing called. No exception.
3. Follow other checks from here.

Dynamic Images in XML Publisher

Image path used in XML publisher can be constructed dynamically from values rather than hard coding. Below image provides the example.

CF_IMAGE_NAME in the report generates image name value for each row of data. And the images with those names reside in the path "$OA_MEDIA/custom/". Let's say a value of CF_IMAGE_NAME is image1.jpg during run time the follow value will be rendered to "$OA_MEDIA/custom/image1.jpg" and the corresponding image will be displayed on the output.

Format picture is a property of a dummy image used in the report to print the images in specific dimension. Due to white background its not visible. You can check more from other post.

There are times when a custom application server path has to be used instead of $OA_MEDIA.
In order to use a custom application path, derive the full path to image in the XML element along with
image name or different element for image name and use it in the XML template.

And use the elements in the image properties ALT TEXT. Below word properties are from Office 2013.
Note that data structure has  elements in root hence the construction of the concat is

url:{concat(IMAGE_PATH,'/',IMAGE_NAME)}






To bring up and use the XML elements at a different level use ../ or // in the concat

url:{concat(../IMAGE_PATH,'/',../IMAGE_NAME)}

Images in XML Publisher

1. Insert a dummy image in your template.
     In Microsoft Word's Format Picture dialog box select the Web tab. Enter the
     following syntax in the Alternative text region to reference the OA_MEDIA directory:
   url:{'${OA_MEDIA}/image name'}
   For example, enter:
   url:{'${OA_MEDIA}/ORACLE_LOGO.gif'} 



Picture properties from Word 2003.

2. Ensure that the image file is in OA_MEDIA
3. Then, from the XML Publisher Administrator resp, upload the edited template into the Template record.
   
   Also see Dynamic Images in XML Publisher

Some useful Unix commands for shell scripting

IF-ELSE

if [ "$value" -eq "0" ] || [ "$value" -ge "2" ]; then
    echo "condition met"
else
    echo "condition not met"

fi

Comparisons:

-eq	equal to
-ne	not equal to
-lt	less than
-le	less than or equal to
-gt	greater than
-ge	greater than or equal to

Exit Host concurrent program with Error

exit 2;

 Linux Find and Move Archive files older than N days

find $frompath -type f -mtime +$age -exec mv {} $archive \;

Obtain value from string

echo $value | cut -f1 -d"."

Command will return the value starting to until “.” is found.

Test.txt will return Test

Using SUBSTR in Unix

echo $name | awk '{ print substr( $0, 0, length($0)-4 ) }'

Command will return name starting from 0 to (lengthofstring)-4

Test.txt will return Test

DEFAULT WHERE Forms Personalization

At times custom query has to be executed at run time to limit the data visible to users. It can be achieved  by forms personalization.

In forms personalization

Under the condition tab

Trigger-Event : WHEN-NEW-BLOCK-INSTANCE
Trigger-Object: Block name where condition has to be executed.
Processing Mode: Not in Enter-Query Mode

You can limit personalization to site/resp/user.

Under Actions

Type: Propery
Object Type: Block
Target Object: Block you selected for  Trigger-Object
Property Name: DEFAULT_WHERE
Value: Enter your custom query.
Sample Value: customer_trx_id IN  (select customer_trx_id FROM temp_table WHERE customer_trx_id=:GLOBAL.XX_CUST_TRX_ID).

No need for quotes. Probably you might have to enter a value in double quotes if you have a string value to compare like --> valid_flag = ''Y''