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'

ORA-01922: CASCADE must be specified to drop 'username'

Hi, this is a common issue when you try to delete OBIEE11G Schema (DEV_BIPLATFORM and DEV_MDS) from Database. The best way to avoid it is by using RCU itself. But if you want to delete it manually, read further.

This error is issued if someone tries to drop a user when the user to be dropped still has objects. In order to drop the user and all his objects, use key word cascade.
Like,

drop user DEV_MDS cascade;

drop user DEV_BIPLATFORM cascade;

Make sure that you have Admin privileges. Or connect as sys user and delete the user.  

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)
)
)

Internal database error *** ERROR*** Assertion failed :201129(9.0.2.3228) File is shorter than expected -- transaction rolled back

If you find the following error while configuring odbc connection for siebel tools.

Then most probably your .dbf file is corrupt. Its better to take a new database abstract file.
Reference: http://sqlanywhere-forum.sybase.com

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.