Oracle
New tips for Oracle SQL
Find the name of all functions and procedures
If you want to find all the functions or procedures in Oracle using SQL syntax:SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE');
Find the Source Code of a Function or Procedure in Oracle SQL
In Oracle if you need to find the source code for a function or procedure in the database you can query the all_source view to get a list of all of the source code of the function or the procedure.SELECT text
FROM all_source
WHERE name = 'Your_Function_Name'
ORDER BY line;
FROM all_source
WHERE name = 'Your_Function_Name'
ORDER BY line;
Replace Your_Function_Name with the name of your function.
COALESCE Function
The function COALESCE looks out like this.COALESCE(exp1, exp2, ..., expN);
The COALESCE() function returns the first non-null expression in the list. It requires at least two expressions. In case all expressions evaluate to null, the function returns null.
For example:
SELECT
COALESCE(NULL, NULL, 3) -- return 3
FROM
dual;
COALESCE(NULL, NULL, 3) -- return 3
FROM
dual;
If all arguments are NULL then the function returns NULL. For instance,
SELECT
COALESCE(NULL, NULL, NULL, NULL) -- return NULL
FROM
dual;
COALESCE(NULL, NULL, NULL, NULL) -- return NULL
FROM
dual;
Alte resure
| PL SQL Procedures and function s | * |
