Unit 1 - Chapter 2 Oracle Built In Functions
Unit 1 - Chapter 2Oracle Built in FunctionsThere are two types of functions in Oracle.1) Single Row Functions: Single row or Scalar functions return a value for every row that isprocessed in a query.2) Group Functions: These functions group the rows of data based on the values returnedby the query. This is discussed in SQL GROUP Functions. The group functions are used tocalculate aggregate values like total or average, which return just one total or one averagevalue after processing a group of rows.There are four types of single row functions. They are:1) Numeric Functions: These are functions that accept numeric input and return numericvalues.2) Character or Text Functions: These are functions that accept character input and canreturn both character and number values.3) Date Functions: These are functions that take values that are of datatype DATE as inputand return values of datatype DATE, except for the MONTHS BETWEEN function, whichreturns a number.4) Conversion Functions: These are functions that help us to convert a value in one form toanother form. For Example: a null value into an actual value, or a value from one datatypeto another datatype like NVL, TO CHAR, TO NUMBER, TO DATE etc.You can combine more than one function together in an expression. This is known asnesting of functions.What is a DUAL Table in Oracle?This is a single row and single column dummy table provided by oracle. This is used toperform mathematical calculations without using a table.Select * from DUALOutput:
DUMMY------XSelect 777 * 888 from DualOutput:777 * 888--------6899761) Numeric Functions:Numeric functions are used to perform operations on numbers. They accept numericvalues as input and return numeric values as output. Few of the Numeric functions are:FunctionNameABS (x)CEIL (x)FLOOR (x)TRUNC (x, y)ROUND (x, y)Return ValueAbsolute value of the number 'x'Integer value that is Greater than or equal to the number 'x'Integer value that is Less than or equal to the number 'x'Truncates value of number 'x' up to 'y' decimal placesRounded off value of the number 'x' up to the number 'y' decimal placesThe following examples explains the usage of the above numeric functionsFunctionNameABS (x)CEIL (x)FLOOR (x)TRUNC (x, y)ROUND (x, y)ExamplesReturnValueABS (1)1ABS (-1)-1CEIL (2.83)3CEIL (2.49)3CEIL (-1.6)-1FLOOR (2.83)2FLOOR (2.49)2FLOOR (-1.6)-2ROUND (125.456, 1) 125.4ROUND (125.456, 0) 125ROUND (124.456, -1) 120TRUNC (140.234, 2) 140.23TRUNC (-54, 1)54
TRUNC (5.7)TRUNC (142, -1)5140These functions can be used on database columns.For Example: Let's consider the product table used in sql joins. We can use ROUND toround off the unit price to the nearest integer, if any product has prices in fraction.SELECT ROUND (unit price) FROM product;2) Character or Text Functions:Character or text functions are used to manipulate text strings. They accept strings orcharacters as input and can return both character and number values as output.Few of the character or text functions are as given below:Function NameLOWER (string value)UPPER (string value)INITCAP (string value)LTRIM (string value,trim text)RTRIM (string value,trim text)TRIM (trim text FROMstring value)SUBSTR (string value,m, n)LENGTH (string value)LPAD (string value, n,pad value)RPAD (string value, n,pad value)Return ValueAll the letters in 'string value'is converted to lowercase.All the letters in 'string value'is converted to uppercase.All the letters in 'string value'is converted to mixed case.All occurrences of 'trim text' is removed from the leftof'string value'.All occurrences of 'trim text' is removed from the rightof'string value' .All occurrences of 'trim text'from the left and rightof'string value' , 'trim text' can also be only one character long .Returns 'n' number of characters from 'string value'starting fromthe 'm' position.Number of characters in'string value' in returned.Returns 'string value' left-padded with 'pad value' . The length ofthe whole string will be of 'n' characters.Returns 'string value' right-padded with 'pad value' . The length ofthe whole string will be of 'n' characters.For Example, we can use the above UPPER() text function with the column value as follows.SELECT UPPER (product name) FROM product;The following examples explains the usage of the above character or text functionsFunction NameLOWER(string value)UPPER(string value)ExamplesLOWER('Good Morning')UPPER('Good Morning')Return Valuegood morningGOOD MORNING
INITCAP(string value)LTRIM(string value, trim text)RTRIM (string value, trim text)TRIM (trim text FROMstring value)SUBSTR (string value, m, n)LENGTH (string value)LPAD (string value, n, pad value)RPAD (string value, n, pad value)INITCAP('GOOD MORNING')LTRIM ('Good Morning', 'Good)RTRIM ('Good Morning', 'Morning')TRIM ('o' FROM 'Good Morning')Good MorningMorningGoodSUBSTR ('Good Morning', 6, 7)LENGTH ('Good Morning')LPAD ('Good', 6, '*')RPAD ('Good', 6, '*')Morning12**GoodGood**Gd Mrning3) Date Functions:These are functions that take values that are of datatype DATE as input and return valuesof datatypes DATE, except for the MONTHS BETWEEN function, which returns a number asoutput.Few date functions are as given below.Function NameADD MONTHS (date,n)MONTHS BETWEEN(x1, x2)ROUND (x,date format)TRUNC (x,date format)NEXT DAY (x,week day)LAST DAY (x)SYSDATENEW TIME (x, zone1,zone2)Return ValueReturns a date value after adding 'n' months to the date'x'.Returns the number of months between dates x1 and x2.Returns the date 'x' rounded off to the nearest century, year,month, date, hour, minute, or second as specified bythe'date format'.Returns the date 'x' lesser than or equal to the nearest century,year, month, date, hour, minute, or second as specified by the'date format'.Returns the next date of the'week day' on or after thedate'x' occurs.It is used to determine the number of days remaining in a monthfrom the date 'x'specified.Returns the systems current date and time.Returns the date and time in zone2 if date 'x' represents the time inzone1.The below table provides the examples for the above functionsFunction NameExamplesReturnValueADD MONTHS ( )ADD MONTHS ('16-Sep-81', 3)16-Dec-81MONTHS BETWEEN( ) MONTHS BETWEEN ('16-Sep-81', '16-Dec-81') 3
NEXT DAY( )LAST DAY( )NEW TIME( )NEXT DAY ('01-Jun-08', 'Wednesday')LAST DAY ('01-Jun-08')NEW TIME ('01-Jun-08', 'IST', 'EST')04-JUN-0830-Jun-0831-May-084) Conversion Functions:These are functions that help us to convert a value in one form to another form. For Ex: anull value into an actual value, or a value from one datatype to another datatype like NVL,TO CHAR, TO NUMBER, TO DATE.Few of the conversion functions available in oracle are:Function NameTO CHAR (x [,y])TO DATE (x [,date format])NVL (x, y)DECODE (a, b, c, d, e,default value)Return ValueConverts Numeric and Date values to a character string value. Itcannot be used for calculations since it is a string value.Converts a valid Numeric and Character values to a Date value.Date is formatted to the format specified by 'date format'.If 'x' is NULL, replace it with 'y'. 'x'and 'y' must be of the samedatatype.Checks the value of 'a', if a b, then returns 'c'. If a d, thenreturns 'e'. Else, returns default value.The below table provides the examples for the above functionsFunction NameTO CHAR ()TO DATE ()NVL ()ExamplesTO CHAR (3000, ' 9999')TO CHAR (SYSDATE, 'Day, Month YYYY')TO DATE ('01-Jun-08')NVL (null, 1)Return Value 3000Monday, June 200801-Jun-081Numeric Functions:These are functions that accept numeric input and return numeric values. Below are few of theexamplesABS: Absolute value of the numberSELECT ABS(12) FROM DUAL;ABS(12)--------12
CEIL: Integer value that is Greater than or equal to the numberSQL SELECT CEIL(48.99) FROM DUAL;CEIL(48.99)----------49SQL SELECT CEIL(48.11) FROM DUAL;CEIL(48.11)----------49FLOOR: Integer value that is Less than or equal to the numberSQL SELECT FLOOR(49.99) FROM DUAL;FLOOR(49.99)-----------49SQL SELECT FLOOR(49.11) FROM DUAL;FLOOR(49.11)-----------49ROUND: Rounded off value of the number 'x' up to the number 'y' decimal placesSQL SELECT ROUND(49.11321,2) FROM DUAL;ROUND(49.11321,2)----------------49.11SQL SELECT ROUND(49.11321,3) FROM DUAL;ROUND(49.11321,3)----------------49.113SQL SELECT ROUND(49.11321,4) FROM DUAL;ROUND(49.11321,4)----------------49.1132Few other functions,POWERSQL SELECT POWER(4,2) FROM DUAL;POWER(4,2)---------16
MODSQL SELECT MOD(4,2) FROM DUAL;MOD(4,2)--------0SQL SELECT SIGN(-98) FROM DUAL;SIGN(-98)---------1SQL SELECT SIGN(98) FROM DUAL;SIGN(98)--------1Character String:Function 1: UPPERPurpose : Returns the string in uppercaseSyntax : UPPER(‘str’)Example : SELECT UPPER(‘karuvachi’) from ��————Function 2: lowerPurpose : Returns the string in lowercaseSyntax : lower(‘str’)Example : SELECT LOWER(‘KaRuVaChi’) FROM ��————Function 3: InitcapPurpose : Returns the string with first letter in uppercase and rest of the letters in lowercaseSyntax : Initcap(‘str’)Example : SELECT Initcap(‘KaRuVaChi’) FROM ��————-
Function 4: ConcatPurpose : Concatenate two stringsSyntax : concat(‘str1′,’str2’)Example : SELECT CONCAT(‘Karu’,’Nand’) FROM �————Function 5: LpadPurpose : Pad in the left side of the string for given times – length of the stringSyntax : Lpad(‘str1′,n,’str2’)Example : SELECT Lpad(‘Karu’,6,’?’) FROM �———Function 6: RpadPurpose : Pad in the right side of the string for given times – length of the stringSyntax : Rpad(‘str1′,n,’str2’)Example : SELECT Rpad(‘Karu’,6,’?’) FROM �———Function 7: trimPurpose : Trim the whitespaces in both the sides of the stringSyntax : trim(‘str’)Example : SELECT TRIM(‘ karu‘) FROM ———Function 8: LtrimPurpose : Trim the whitespaces in left the side of the stringSyntax : Ltrim(‘str’)Example : SELECT LTRIM(‘ karu‘) FROM DUAL;Output:karu .(. dot are Function 9: Rtrim
Purpose : Trim the whitespaces in right the side of the stringSyntax : Rtrim(‘str’)Example : SELECT RTRIM(‘ karu‘) FROM DUAL;Output: .karu(. dot are Function 10: LengthPurpose : length of the stringSyntax : length(‘str’)Example : SELECT LENGTH(‘karuvachi’) FROM ——Function 11: InstrPurpose : Find the position of the string in another stringSyntax : Instr(‘str1′,’str2’)Example : SELECT INSTR(‘karuvachi’,’ka’) FROM ——Function 12: substrPurpose : get a sub string from stringSyntax : substr(‘str’,start pos,number of chars)Example : SELECT substr(‘karuvachi’,2,4) FROM DUAL;Output: aruvDate Functions and Operators.To see the system date and time use the following functions :CURRENT DATE :returns the current date in the session time zone, in a value in the Gregoriancalendar of datatypeDATESYSDATE:Returns the current date and time.SYSTIMESTAMP :The SYSTIMESTAMP function returns the system date, including fractional
seconds and time zoneof the database. The return type is TIMESTAMP WITH TIME ZONE.FORMATMEANINGDDay of the weekDDDay of the monthDDDDay of the yearDAYFull day for ex. ‘Monday’, ’Tuesday’, ’Wednesday’DYDay in three letters for ex. ‘MON’, ‘TUE’,’FRI’WWeek of the monthWWWeek of the yearMMMonth in two digits (1-Jan, 2-Feb, 12-Dec)MONMonth in three characters like “Jan”, ”Feb”, ”Apr”MONTHFull Month like “January”, ”February”, ”April”RMMonth in Roman Characters (I-XII, I-Jan, II-Feb, XII-Dec)QQuarter of the MonthYYLast two digits of the year.YYYYFull yearYEARYear in words like “Nineteen Ninety Nine”HHHours in 12 hour formatHH12Hours in 12 hour formatHH24Hours in 24 hour formatMIMinutesSSSecondsFFFractional SecondsSSSSSMillisecondsJJulian Day i.e Days since 1st-Jan-4712BC to till-dateRRIf the year is less than 50 Assumes the year as 21ST Century. If the yearis greater than 50 then assumes the year in 20th Century.Date and time functions and formats are quite different in various databases. In this article, let’sreview the most common functions that manipulates dates in an Oracle database.
The function SYSDATE() returns a 7 byte binary data element whose bytes represents: century, year, month, day, hour, minute, secondSelect sysdate from dual;Oracle enables you to extract the day, month, and year from a date using an extract function:select extract(day from sysdate) as only day from dualselect extract(month from sysdate) as only month from dualselect extract(year from sysdate) as only year from dualADD MONTHS(date, n) – Adds the specific number of months (n) to a date. The ‘n’ can be bothnegative and positive:Select add months(sysdate, -1) as prev month , sysdate, add months (sysdate, 1) as next monthfrom dualLAST DAY(date) – Returns the last day in the month of the specified date d.select sysdate, last day(sysdate) as last day curr month,last day(sysdate) 1 as first day next month from dual
The number of days until the end of the month.select last day(sysdate) - sysdate as days leftfrom dualMONTHS BETWEEN(date, date) – Calculates the number of months between two dates.Example:select MONTHS BETWEEN ('31-JAN-2014', '28-FEB-2014')from dualselect MONTHS BETWEEN ('31-MAR-2013', '28-FEB-2013')from dualLet’s select the number of months an employee has worked for the company.Select months between (sysdate, date of hire)from employeesNEXT DAY(date, day of week) – Returns the date of the first weekday specified that is laterthan the date.select next day(sysdate, 'monday') as next Monday from dualROUND(date [, format mask VARCHAR2]) – Returns the date with time rounded to midnight(12 A.M.) in the default. The format mask is optional. The following example rounds a date tothe first day of the following year:SELECT ROUND (TO DATE ('10-SEP-14'),'YEAR') as new yearFROM DUAL;
TRUNC(date, [format]) – Truncates the specified date of its time portion according to theformat provided. If the ‘format’ is omitted, the hours, minutes or seconds will be truncated.SELECT TRUNC(TO DATE('27-OCT-92'), 'year')as new year FROM DUAL;Arithmetic Operations With Dates Date numberselect sysdate 1 as tomorrowfrom dualselect sysdate (5/1440) as five mintues from nowfrom dual Date – numberselect sysdate - 1 as yesterdayfrom dual Date – dateYou can subtract a date from a date in Oracle. The result will be in days. You can also multiplyby 24 to get hours and so on.select 24 * (to date('2014-10-10 22:00', 'YYYY-MM-DD hh24:mi') - to date('2014-10- 9 21:00', 'YYYY-MM-DD hh24:mi'))difference in hours from dual;Besides the SQL utility functions, Oracle inbuilt function library contains type conversionfunctions. There may be scenarios where the query expects input in a specific data type, but itreceives it in a different data type. In such cases, Oracle implicitly tries to convert theunexpected value to a compatible data type which can be substituted in place and application
continuity is not compromised. Type conversion can be either implicitly done by Oracle orexplicitly done by the programmer.Implicit data type conversion works based on a matrix which showcases the Oracle's support forinternal type casting. Besides these rules, Oracle offers type conversion functions which can beused in the queries for explicit conversion and formatting. As a matter of fact, it is recommendedto perform explicit conversion instead of relying on software intelligence. Though implicitconversion works well, but to eliminate the skew chances where bad inputs could be difficult totypecast internally.Implicit Data Type ConversionA VARCHAR2 or CHAR value can be implicitly converted to NUMBER or DATE type value byOracle. Similarly, a NUMBER or DATA type value can be automatically converted to characterdata by Oracle server. Note that the impicit interconversion happens only when the characterrepresents the a valid number or date type value respectively.For example, examine the below SELECT queries. Both the queries will give the same resultbecause Oracle internally treats 15000 and '15000' as same.Query-1SELECT employee id,first name,salaryFROM employeesWHERE salary 15000;Query-2SELECT employee id,first name,salaryFROM employeesWHERE salary '15000';Explicit Data Type ConversionSQL Conversion functions are single row functions which are capable of typecasting columnvalue, literal or an expression . TO CHAR, TO NUMBER and TO DATE are the three functionswhich perform cross modification of data types.
TO CHAR functionTO CHAR function is used to typecast a numeric or date input to character type with a formatmodel (optional).SyntaxTO CHAR(number1, [format], [nls parameter])For number to character conversion, nls parameters can be used to specify decimal characters,group separator, local currency model, or international currency model. It is an optionalspecification - if not available, session level nls settings will be used. For date to characterconversion, the nls parameter can be used to specify the day and month names, as applicable.Dates can be formatted in multiple formats after converting to character types using TO CHARfunction. The TO CHAR function is used to have Oracle 11g display dates in a particular format.Format models are case sensitive and must be enclosed within single quotes.Consider the below SELECT query. The query format the HIRE DATE and SALARY columns ofEMPLOYEES table using TO CHAR function.SELECT first name,TO CHAR (hire date, 'MONTH DD, YYYY') HIRE DATE,TO CHAR (salary, ' 99999.99') SalaryFROM employeesWHERE rownum 5;FIRST NAMEHIRE DATESALARY-------------------- ------------------ ---------StevenJUNE 17, 2003 24000.00NeenaSEPTEMBER 21, 2005 17000.00LexJANUARY 13, 2001 17000.00AlexanderJANUARY 03, 2006 9000.00The first TO CHAR is used to convert the hire date to the date format MONTH DD, YYYY i.e.month spelled out and padded with spaces, followed by the two-digit day of the month, andthen the four-digit year. If you prefer displaying the month name in mixed case (that is,"December"), simply use this case in the format argument: ('Month DD, YYYY').The second TO CHAR function in Figure 10-39 is used to format the SALARY to display thecurrency sign and two decimal positions.
TO NUMBER functionThe TO NUMBER function converts a character value to a numeric datatype. If the string beingconverted contains nonnumeric characters, the function returns an error.SyntaxTO NUMBER (string1, [format], [nls parameter])TO DATE functionThe function takes character values as input and returns formatted date equivalent of the same.The TO DATE function allows users to enter a date in any format, and then it converts the entryinto the default format used by Oracle 11g.Syntax:TO DATE( string1, [ format mask ], [ nls language ] )
Unit 1 - Chapter 2 Oracle Built in Functions There are two types of functions in Oracle. 1) Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query. 2) Group Functions: These functions group the rows of
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Part One: Heir of Ash Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18 Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 Chapter 25 Chapter 26 Chapter 27 Chapter 28 Chapter 29 Chapter 30 .
TO KILL A MOCKINGBIRD. Contents Dedication Epigraph Part One Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Part Two Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18. Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 Chapter 25 Chapter 26
7 Messaging Server Oracle Oracle Communications suite Oracle 8 Mail Server Oracle Oracle Communications suite Oracle 9 IDAM Oracle Oracle Access Management Suite Plus / Oracle Identity Manager Connectors Pack / Oracle Identity Governance Suite Oracle 10 Business Intelligence
Advanced Replication Option, Database Server, Enabling the Information Age, Oracle Call Interface, Oracle EDI Gateway, Oracle Enterprise Manager, Oracle Expert, Oracle Expert Option, Oracle Forms, Oracle Parallel Server [or, Oracle7 Parallel Server], Oracle Procedural Gateway, Oracle Replication Services, Oracle Reports, Oracle
PeopleSoft Oracle JD Edwards Oracle Siebel Oracle Xtra Large Model Payroll E-Business Suite Oracle Middleware Performance Oracle Database JDE Enterprise One 9.1 Oracle VM 2.2 2,000 Users TPC-C Oracle 11g C240 M3 TPC-C Oracle DB 11g & OEL 1,244,550 OPTS/Sec C250 M2 Oracle E-Business Suite M
Oracle Database using Oracle Real Application Clusters (Oracle RAC) and Oracle Resource Management provided the first consolidation platform optimized for Oracle Database and is the MAA best practice for Oracle Database 11g. Oracle RAC enables multiple Oracle databases to be easily consolidated onto a single Oracle RAC cluster.
