Automatic date management in SQL

These are some DB syntax to automate date in your queries.

SYSDATE()   - Today’s date
FIRST_DAY_OF_MONTH() - First day of the current month
LAST_DAY_OF_MONTH()  - Last day of the current month
FIRST_DAY_OF_YEAR()  - First day of the current year
LAST_DAY_OF_YEAR()   - Last day of the current year

Port Setting in BIPublisher 10 G standalone

 This article is dealing only with BIP Standalone and reader should not get confused with OBIEE+BIPublisher Port setting.


1. Locate the default-web-site.xml file.

Note:  Usually the path is like oc4j home/j2ee/home/config/default-web-site.xml. OC4J reads this file when it starts up.

2. Open the file in text editor. In this file you can see the default port which had been set during the installation. Change the port value to thee desired port number.

3. Stop then Start the OC4J server.

4. Log into the application with new port number.

I came across this when I wanted to set my BIP's port to 80 but OC4J was not starting up when I tried to start after editing the port number. When I gave port number as 8080 it started up and I was able to access the application.

The possible reasons is, there are some other applications in my machine which requires a server and it might be creating a clash. Anyway my experiment continues.

Local Temporary tables

Local temporary tables are valid within Modules/Schema and are bound to active SQL sessions.
The syntax to create a Local Temporary table is:

DECLARE LOCAL TEMPORARY TABLE table_name ( fields);

Example

DECLARE LOCAL TEMPORARY TABLE employee_temp
( employee_id number(10) not null,
  employee_name varchar2(50) not null,
  employee_designation  varchar2(50)
);

The above command will create a temporary "employee_temp" table.

Substring in Oracle SQL

The basic syntax for Sub-String in oracle sql is

"SUBSTR(string/column/field,start_position,length)"

and this function is available in all versions from Oracle DB 8i till Oracle DB 12 C (Oracle 12c)

If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.

If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.

If length is a negative number, then the SUBSTR function will return a NULL value.

Examples:

SUBSTR('This is a test', 6, 2)	return 'is'
SUBSTR('This is a test', 6)	return 'is a test'
SUBSTR('IntelBusi', 1, 5)	return 'Intel'
SUBSTR('IntelBusi', -4, 4)	return 'Busi'
SUBSTR('IntelBusi', -4, 3)	return 'Bus'

BI Publisher java.lang.reflect.InvocationTargetException

When I was doing some crazy experiments with XSL variables in BI Publisher,
came across the error "java.lang.reflect.InvocationTargetException".
The error was generated by the MS Word Addin of BIP while creating a pdf preview. as shown.

The error screen was the following.

A little googling saved me!!! the reason for this error in java is mainly due to incorrect datatype.
Say a variable of type "Number" is getting assigned with a value of type "Text".

Bingo... the error might be in the variable I decalred as below..!!!

<?xdoxslt:set_variable($_XDOCTX,'V_NA',0)?>

and what generated the error should be,

<?xdoxslt:set_variable($_XDOCTX,'V_NA',NAME)?>

The error was resolved by changing the code to

<?xdoxslt:set_variable($_XDOCTX,'V_NA','xyz')?>

And tried again to get a preview.... Ta..da... It Worked.

BI Publisher Parameters in the URL

Through this post am discussing about the parameters specified in the report link / URL

Consider a URL like this

http://<ip/address>:<Port>/xmlpserver/Sales/Sales+Report/Sales+Report.xdo?_xpf=&_xpt=1&_xdo=%2FSales%2FSales+Report%2FSales+Report.xdo&Id=1-104x&_xt=Standard&_xf=html

The URL after the report name consists of:

?_xpf=&_xpt=1&_xdo=%2FSales%2FSales+Report%2FSales+Report.xdo&Id=1-104x&_xt=Standard&_xf=html

Note the following standard URL syntax:

? - The first parameter is added with a question mark in the beginning.

& - Additional parameters have to be specified like this.

The BI Publisher parameters are as follows:

_xpf - Its an application internal string. Not to be used by the developer.
_xpt - Chooses the report rendering way or style.

    0 - uses the BI Publisher window (iFrame)

    1- renders just the document

_xdo - Provides the path to the current report. To be note that it is not mandatory to give this parameter.

dept - Report specific parameter that we created in the report.

_xt - Specifies the template to be used. Usually for the reports which have more than one templates.

_xf - Controls format of the output file. Accepted values are: pdf, html, excel, rtf, or data.