Oracle Supplied Function

Oracle supplied function:-

There are many functions which are supplied by oracle. These are listed below:-

Function to convert values in the upper case:-

SELECT UPPER ('chanchal wankhade') CONV_UPPER FROM DUAL;

Function to convert values to lower case:-

SELECT LOWER ('CHANCHAL WANKHADE') CONV_UPPER FROM DUAL;

Function to replace any value:-

SELECT REPLACE ('CHANCHAL WANKHADE','CHANCHAL','WANKHADE') REPLACE_FUNC FROM DUAL;

Function to trim a value or blank space (note there is a space between single code and chanchal and single code and wankhade):-

SELECT TRIM (' CHANCHAL WANKHADE ') TRIM_FUNC FROM DUAL;

Function to trunc values:-

SELECT TRUNC (123456789.1234) TRIM_FUNC FROM DUAL;

Function to make every words first later in capital:-

SELECT INITCAP ('CHANCHAL WANKHADE') INITCAP_FUNC FROM DUAL;

Function to find out a number by providing two dates:-

SELECT MONTHS_BETWEEN ('01-DEC-2013','01-JAN-2013') MONTHS_BET FROM DUAL;

SELECT MONTHS_BETWEEN ('01-JAN-2013','01-DEC-2013') MONTHS_BET FROM DUAL;

Function to round the values till required numbers:-

SELECT ROUND (12345.123,7) ROUND_FUNC FROM DUAL;

SELECT ROUND (12345.123) ROUND_FUNC FROM DUAL;

Function to find characters which are starting from specific character to desired character:-

SELECT SUBSTR ('CHANCHAL WANKHADE',5,5) FROM DUAL;

Function to find the positions of the specified character:-

SELECT INSTR ('CHANCHAL WANKHADE','A',1,1) INSTR_FUNC FROM DUAL;

SELECT INSTR ('CHANCHAL WANKHADE','A',1,2) INSTR_FUNC FROM DUAL;

SELECT INSTR ('CHANCHAL WANKHADE','A',1,3) INSTR_FUNC FROM DUAL;

Above functions with screen shots:-

Oracle provides a wide range of built-in functions to perform various operations on data. Here are some frequently asked questions about Oracle supplied functions:-

1. What are Oracle supplied functions?

   - Oracle supplied functions are built-in functions provided by Oracle that allow you to perform operations on data within SQL queries or PL/SQL code. These functions cover a wide range of tasks such as string manipulation, numeric operations, date and time operations, conversion functions, and more.

2. What are some common categories of Oracle supplied functions?

   - Some common categories of Oracle supplied functions include:

     - String functions: Functions to manipulate strings such as CONCAT, SUBSTR, INSTR, and TRIM.

     - Numeric functions: Functions to perform arithmetic operations such as ABS, ROUND, CEIL, and FLOOR.

     - Date functions: Functions to work with dates and times such as TO_DATE, TO_CHAR, ADD_MONTHS, and EXTRACT.

     - Conversion functions: Functions to convert data types such as TO_NUMBER, TO_DATE, and TO_CHAR.

     - Aggregate functions: Functions to perform calculations on groups of rows such as SUM, AVG, MIN, MAX, and COUNT.

3. How do I use Oracle supplied functions in SQL queries?

   - You can use Oracle supplied functions in SQL queries by including them in SELECT statements, WHERE clauses, GROUP BY clauses, and other parts of the query where expressions are allowed. For example:

     

     SELECT SUBSTR(column_name, 1, 5) AS substring_result

     FROM table_name

     WHERE TO_DATE(date_column, 'YYYY-MM-DD') > SYSDATE;

     

4. Can Oracle supplied functions be used in PL/SQL code?

   - Yes, Oracle supplied functions can be used in PL/SQL code just like in SQL queries. You can use them in assignments, conditions, loops, and other parts of PL/SQL code to perform various tasks.

5. Where can I find documentation for Oracle supplied functions?

   - Oracle provides comprehensive documentation for all its supplied functions in the Oracle Database SQL Language Reference and Oracle Database PL/SQL Language Reference manuals. You can also find information in the Oracle online documentation portal and various online resources.

These questions and answers should give you a good overview of Oracle supplied functions and how to use them in SQL queries and PL/SQL code.