In Oracle/PLSQL, theNVLfunction lets you substitute a value when a null value is encountered. The syntax for theNVLfunction is: NVL( string1, replace_with ) string1is the string to test for a null value. replace_withis the value returned ifstring1is null. Example #1: select NVL(suppli...
The TO_CHAR function can be used in the following versions of Oracle/PLSQL: Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i Example Let's look at some Oracle TO_CHAR function examples and explore how to use the TO_CHAR function in Oracle/PLSQL. Examples with Numbers For examp...
Creates a function.If the parameters or return values of a function have precision, the precision is not checked.When creating a function, you are advised to explicitly s
The only way that I know of to get a SUBSTR from a LONG variable is towrite a PL/SQL procedurethat takes a ROWID, then converts that to a 32k variable, and returns 4000 characters to SQL, which can then be used for the SUBSTR function. This is described inmore detail here. SUBSTR ...
For example, the below code uses multiple spaces in the string. The TO_TIMESTAMP() function omits the spaces and returns the correct timestamp value. SELECT TO_TIMESTAMP('2021 Sep','FXYYYY MON'); Errors with the above example. psql:commands.sql:2: ERROR: invalid value "" for "MON" ...
The very first time you execute the body of a result-cached PL/SQL function with a set of parameter values, the function will execute. The function will re-execute under the following circumstances: When the cached result for the parameter values is invalid because an object specified in the...
Example table output after using multiple SQL DECODE() functions. Image by Author Nested DECODE() statements Implementing the DECODE() function with subqueries allows you to perform sophisticated conditional logic transformations to the data. The nested DECODE() statement in Oracle helps when we want...
A function can have no parameters. In this case, you must code an empty set of parentheses, for example: CREATE FUNCTION WOOFER() parameter-name Specifies the name of the input parameter. The name is an SQL identifier, and each name in the parameter list must not be the same as any ot...
In this example: The PARTITION BYclause divided the result set into two partitions by year, 2016 and 2017. The ORDER BY clause sorted rows in each partition by sales amount from high to low. The PERCENT_RANK() function was then applied to the value of each row in each partition. In th...
A function call in a function definition fails to parse when there's a+operator as one, or the, argument. Presumably happens with any other operators as well. The full example ishttps://github.com/Qualtagh/OracleDBUtils/blob/master/p_utils.9.sql#241. ...