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

ODI other New features in OBIEE 11.1.1.7.1

Oracle is moving ahead with the ODI integration to the BI Package, in the words of Oracle,

"With the 11.1.1.7.1 release of the Oracle BI Applications, you can now use Oracle Data Integrator 11g (ODI11g) as the embedded data integration engine within the product. Along with the introduction of several new BI applications and extensions to existing ones, the introduction of ODI11g has allowed Oracle to simplify the configuration process for the BI Applications as well as leveraging ODI's high-performance "ELT" (Extraction, Load and Transform) for data loading."

Apart from this other features include:

Visualization Enhancements: Performance Tiles, 100% Stacked Bar, Freeze Headers, Waterfall  Chart, View Suggestions, Trellis Chart Grid

User Interaction Improvements: Navigation/Action from Totals, Breadcrumbs, Pass context

to ADF, Search with Endeca MDEX, Save as Analysis on Dashboard, Trellis Actions

BI Mobile HD Enhancements: Analytics available on mobile without additional development,  Data dense visualizations enabled by in memory analytics, enhanced right click interactions, better interactivity with native gestures, offline access to content with fine grained security to grant or revoke privileges

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.

Clone the bi-server in OBIEE 11G

To clone your biserver in OBIEE 11G login to your Weblogic Admin Console, from bifoundation_domain select Environments and follow the steps.

1) In the change center Click "Lock and Edit", this would make servers available for selection.

 

2) By clicking in the check box Select the server you want to clone from the provided server list and click clone.

3) Provide the server name for your all new server and assign a port for it, the administrator should notice that server name and port(Here I am Using 9705) should be unique. Click ok to make the clone.

4) Go to Change Center and Activate the changes.

5) Move to the control tab in the Summary of Servers.

6) Select the new server and click Start button. (Due to some memory limitation in my machine I am giving a Force Shutdown for my default Server.)

7) Wait till it starts up.

8) You can observe the status of the server and satus of last action. If your process is complete status of last action would show "TASK COMPLETED"

9) The new server can be accessed through the new port assigned (9705)

10) New Server is also listed in the Enterprise Manager.

Thus the cloning of biserver is completed.

 Thank you for sparing your time with my blog.

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'

Siebel Client Setup (Installation) Hangs at 92% or just after browser Opens up

This is an which usually occur during the installation of Siebel Client in XP/WIN Server 2003. While installing the client siebel Opens up your default browser and requests you to close it. Even if you close the the browser if you find the installation is showing no progress, and seems to be a installer hang when it is around 92% of the setup be sure that a windows "Process" is blocking the installation. To take a closer look you can refer the installation log but it wont be fruitful.

The solution to this issue is open the "Task Manager" move to process tab, select "regserver32" click End Process button. Now you have done it !!! Now you can see that Installer completes by itself.