How to find all packaged procedures that have argument(s) of a specific data type?

This task is quite trivial if you know the right data dictionary to use.
Let’s say we need to see all procedures that has DATE arguments:

SELECT package_name, object_name AS procedure_name, argument_name, data_type
FROM user_arguments a
WHERE data_type='DATE'
  AND package_name IS NOT NULL
ORDER BY 1,2,3
Advertisements

How to Simulate a Global Constant in Oracle PL/SQL?

Oracle PL/SQL does not support global constants, i.e. constants defined on the schema level.
You can define one in a package, which is typically used when you need to refer to a constant. However, such constant definitions look bulky as you always need to reference package name as a prefix to the constant name.

Deterministic functions allow you to mimic the constant use without sacrificing the performance.

Let’s see an example where we will define a “CONSTANT” that carried the date formatting string (for TO_DATE conversions):

CREATE OR REPLACE FUNCTION date_format 
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN 'yyyy-mm-dd hh24:mi:ss';
END date_format;
/

Now, you can use this function as if it were a globally defined constant:

SET SERVEROUTPUT ON

DECLARE
   v_date DATE;
BEGIN
   v_date:=TO_DATE('2015-11-14', date_format);
   DBMS_OUTPUT.PUT_LINE(v_date);
END;
/
14-NOV-15

PL/SQL procedure successfully completed.

According to Oracle documentation, DETERMINISTIC is a “hint that helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result.”

Since our function does not have any parameters, the optimizer will always return the previous (i.e. CONSTANT) result.

Alternatively, you can use RESULT_CACHE option which was first introduced in Oracle 11g:

CREATE OR REPLACE FUNCTION date_format 
RETURN VARCHAR2 RESULT_CACHE
AS
BEGIN
 RETURN 'yyyy-mm-dd hh24:mi:ss';
END date_format;
/

The advantage of this method is the cached information can be reused by any session and dependencies are managed automatically.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.