List of Build in Functions - Oracle

 

 Oracle provides a comprehensive set of built-in functions categorized into various types. Here’s a list of the commonly used categories and functions:

1. String Functions

• LOWER(char) – Converts to lowercase
• UPPER(char) – Converts to uppercase
• INITCAP(char) – Capitalizes the first letter
• LENGTH(char) – Returns length of a string
• SUBSTR(char, start, length) – Extracts substring
• INSTR(char, substring) – Finds position of substring
• TRIM(char) – Removes leading/trailing characters
• LPAD(char, length, pad_char) – Left pad
• RPAD(char, length, pad_char) – Right pad
• REPLACE(char, search, replace) – Replaces text
• CONCAT(char1, char2) – Concatenates strings

---

2. Numeric Functions

• ABS(number) – Absolute value
• ROUND(number, decimal_places) – Rounds to n decimal places
• TRUNC(number, decimal_places) – Truncates to n decimal places
• MOD(number1, number2) – Remainder of division
• CEIL(number) – Rounds up to next whole number
• FLOOR(number) – Rounds down to previous whole number
• POWER(base, exponent) – Raises to the power
• SQRT(number) – Square root
• SIGN(number) – Returns -1, 0, or 1

---

3. Date Functions

• SYSDATE – Returns current date and time
• CURRENT_DATE – Date in current session timezone
• ADD_MONTHS(date, number) – Adds months to date
• LAST_DAY(date) – Last day of the month
• MONTHS_BETWEEN(date1, date2) – Difference in months
• NEXT_DAY(date, weekday) – Next specified weekday
• ROUND(date, format) – Rounds date to the nearest unit
• TRUNC(date, format) – Truncates date to specified unit

---

4. Conversion Functions

• TO_CHAR(date_or_number, format) – Converts to string
• TO_DATE(char, format) – Converts to date
• TO_NUMBER(char) – Converts to number

---

5. Aggregate Functions

• AVG(column) – Average
• COUNT(column) – Number of rows
• SUM(column) – Sum
• MAX(column) – Maximum
• MIN(column) – Minimum
• STDDEV(column) – Standard deviation
• VARIANCE(column) – Variance

---

6. Conditional Functions

• CASE WHEN condition THEN result ELSE result END – Conditional logic
• DECODE(expr, search, result, default) – Compares values
• NVL(expr1, expr2) – Replace NULL
• NVL2(expr1, expr2, expr3) – Replace based on NULL check
• NULLIF(expr1, expr2) – Returns NULL if equal

---

7. Miscellaneous Functions

• USER – Returns the current user
• UID – Returns user ID
• SYS_CONTEXT(namespace, parameter) – Returns environment context
• ROWNUM – Row number in result set
• ROWID – Unique row identifier

This list covers most of the built-in functions you will encounter in Oracle SQL. Each function plays a specific role in manipulating or retrieving data effectively.

AWR- Oracle performance/wokload reporting tool - really helpful

We have surfaced one database contention problem in Oracle during performance run and  got to know that
oracle has in build feature to provide system workload report which they call as AWR ( Automatic work load) tool.
Interestingly it helped a lot to find the table and query which was giving problem during performance run  and help to solve the issue.

More read @ following links, it has been introduced in oracle 10 onwards :

http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php

Cheers ,

Kapil 

Change a existing column type (varchar to clob) usig SQL query .

Problem : Change a column type (varchar to clob) usig SQL query .
 
Solution : To change column type column should be dropped and then craeted again .. then if it contains data then risk to loose that .
To prevent it we can use following way : 
1.  Add a temp column :
ALTER TABLE TEST ADD temp VARCHAR2(4000);
2. copy data to temp column :
UPDATE TEST
   SET temp = DBMS_LOB.SUBSTR (a, 4000),
       a = NULL;
3. drop the original column "A"
ALTER TABLE TEST DROP COLUMN A;

4. Either rename the column "temp" to original "A"  ((if just modifying size)
ALTER TABLE TEST RENAME COLUMN temp TO A;

Optional  step
5. create column "A" as clob and repeat step 2 to copy data from "temp" to "A" and after copy remove "temp"

cheers

Kapil

Power of case statement into SQL - Multiple result in Single Query

Query Statement : Having a table "Acct_stat" with column "Trans_type"(Dr or Cr) , "Amount" Pls write a single query which should return sum of "Debit" , "Credit" and "Total Balance" of each account.

Answer : We can use a case statement as best choice for such queries like following with group by Account:

Let's go with following SQL

Drop table Acct_stat;
create table Acct_stat (Acount_No varchar2(10),TRANS_TYPE VARCHAR2(2),AMOUNT NUMBER);

insert into Acct_stat values('1' , 'DR' , 100);
insert into Acct_stat values('1' , 'CR' , 100);
insert into Acct_stat values('1' , 'DR' , 100);
insert into Acct_stat values('2' , 'CR' , 100);
insert into Acct_stat values('2' , 'CR' , 100);
insert into Acct_stat values('2' , 'DR' , 100);
insert into Acct_stat values('3' , 'DR' , 100);
insert into Acct_stat values('3' , 'CR' , 100);

SELECT Acount_No ,
SUM (CASE WHEN Trans_type = 'DR'
THEN AMOUNT ELSE null END) DB_SUM,
SUM (CASE WHEN Trans_type = 'CR'
THEN AMOUNT ELSE null END) CR_SUM,
SUM (AMOUNT) TOT_BAL
FROM ACCT_STAT GROUP BY (Acount_No);

and Result is :
Acount_No DB_SUM CR_SUM TOT_BAL
1 200 100 300
2 100 200 300
3 100 100 200


Cheers

Kapil

How to insert "&" inside database table

Following SQL is to insert "&" into the database

Insert into table_nm (LINK_NAME) values ('Gold ' || chr(38) || ' Notes');

Value will come as "Gold & Notes" in column.

Cheers

Create database sequence in cyclic manner

I need to recycle the value in the database after reaching @ max count .. for that planned to use oracle sequences and found that following way we can recycle the sequence value from the start :

DROP SEQUENCE sms_job_seq;
CREATE SEQUENCE sms_job_seq
MINVALUE 1
MAXVALUE 100
START WITH 1
INCREMENT BY 1
CYCLE
NOCACHE;

we have to set cycle property during sequence creation .. then after reaching max limit .. (100 here) next value will start from 1. (No need of application coding to do this :) )

Cheers..