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

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'

ODBC not showing Oracle DB Driver, Windows 7

Most of the Win 7 users go for the 64 bit OS. Here in this OS let us install Oracle DB 32 bit. After the install go to run then type "odbcad32".

While trying to create an odbc connection to our db we wont be able to see the driver of our Oracle db.
The reason is that, its a naming convention in windows that ODBC should be called by "odbcad32". Even though you type odbcad32 its pointing to the 64 bit version of it. You can see this 64bit odbc from "c:\windows\system32". Since our driver is only 32 bit it wont get listed here.

The solution to this issue is go to your "C:\Windows\SysWOW64\odbcad32.exe" (Here I assume you have installed OS in C drive itself).This is the path to the 32 bit version, in this odbc you can see your 32 bit driver.

ORA-12154: TNS:could not resolve the connect identifier specified

The cause of this error: SQL Plus cannot identify the connect string specified.
What can be the possible reasons:
1. The tnsnames.ora is not present under $ORACLE_HOME. You can check for it by typing %ORACLE_HOME% in command prompt.
2. The file is not accessible.
3. The file is invalid – because of the presence of junk characters or unmatched parenthesis.
4. The connect string that you have used – may not be present in the tnsnames.ora or present with somewhat different name – basically a mismatch of what you have entered and what is present in tnsnames.ora
5. Check the default directories for old or bad copies of tnsnames.ora
6. Please check with the database admins that the Listener is up and running for the connect string that you are trying to connect to.
7. Please check if TNSNAMES is present as one of the values for the NAMES.DIRECTORY_PATH parameter present in the sqlnet.ora file
NAMES.DIRECTORY_PATH= (TNSNAMES)
Please find below sample tnsnames and sqlnet.ora
TNSNAMES.ORA
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME=ORCL)
)
)

External Database Authentication (simplified)

This article is about creating a external database authentication in OBIEE 10G.

When the number of users is so large the best practice to go for external-database authentication.
this can be done in two small parts.

Part 1:
This part deals with the database part and the steps are:

• Create and populate a table with these columns GROUP,USERNAME,DISPLAYNAME,PASSWORD,LOGLEVEL
• Commit all changes

Part 2:
This is what to be done in OBIEE 10 G Admin tool.

• Go to Manage> Variables
• In the left pane, under session you can find "Initialization Blocks", just click it.
• In the right pane, right click and select 'New Initialization Block' from it.
• Give a name for that block (say 'Security')
• Click on Edit Data Source
• In the text field  type the query "select USERGROUP,NAME,USERNAME,LOGLEVEL,ROW_ID from LOGIN where  LOGIN.USERNAME = ':USER' and  LOGIN.PASSWORD = ':PASSWORD' "
• select the connection pool of the database with login details and click ok.
• Now  you are back at the window where you gave the name 'Security'. Here click 'Edit Data Target'.
• In the newly opened window(window to create a session variable) click 'New'. Give it the name 'GROUP' click ok.
  similarly create session variables as DISPLAYNAME, USER, LOGLEVEL. While creating these variables there will be a prompt for using system defined variables.Accept all of them.Save Initialization block.
• Save the RPD

Restart the server services(BI Server and presentation services)
Try to login with a valid username and password that you have entered in Database.


Please comment with issues while setting up External DB authentication.

Deleting duplicate data in the database

This post if for db beginners who have created duplicate records in their db by mistake.
Let your scripts be like this.
--------------------------------------------------------------------------------------
-----------------Table creation-------------------------------------------------------

 CREATE TABLE CALENDAR
   ( "ROW_ID" NUMBER,
"DAY" NUMBER,
"MONTH" NUMBER,
"YEAR" NUMBER
   ) ;

--------------------------------------------------------------------------------------
-----------------Populating data------------------------------------------------------

Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2000,8,9,2011);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2001,4,1,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2002,6,4,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2003,7,6,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2004,5,9,2012);

Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2000,8,9,2011);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2001,4,1,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2002,6,4,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2003,7,6,2012);
Insert into CALENDAR (ROW_ID,DAY,MONTH,YEAR) values (2004,5,9,2012);
--------------------------------------------------------------------------------------
Now you have two sets of redundant data.
Here, what we want to do is to remove the duplicate data that we inserted. To remove that run the following piece of script.

--------------------------------------------------------------------------------------
-----------------Removing duplicate  data--------------------------------------------



DELETE FROM CALENDAR
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM CALENDAR
                          GROUP BY ROW_ID,DAY,MONTH,YEAR);
--------------------------------------------------------------------------------------


Now you can see that all the duplicate records have gone.