Data dictionary views for Oracle Types

User defined Oracle types can be a very powerful tool for experienced developers. As with any other object type, it is critical to know if an object (i.e. TYPE here) exists before you create it during execution of the deployment script. The following Oracle data dictionaries have the required information whether you need type or type attribute details:

user_types          - lists all user owned custom types
user_type_attrs     - lists all attributes for the owned custom types
user_type_methods   - lists all methods found in the owned custom types

Below you can find a few functions that check if a required entity exists or not and return 1 (exists) or 0 (does not exist). Note that these functions can check types in other schemas given that respective privileges have been granted.

CREATE FUNCTION type_exists(p_schema_name VARCHAR2,
                            p_type_name   VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_types
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name);

    RETURN SIGN(v_cnt);
END type_exists;
CREATE FUNCTION type_attribute_exists(p_schema_name VARCHAR2,
                                      p_type_name   VARCHAR2,
                                      p_attr_name   VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_type_attrs
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name)
      AND attr_name=UPPER(p_attr_name);

    RETURN SIGN(v_cnt);
END type_attribute_exists;
CREATE FUNCTION type_method_exists(p_schema_name VARCHAR2,
                                   p_type_name   VARCHAR2,
                                   p_method_name VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_type_methods
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name)
      AND method_name=UPPER(p_method_name);

    RETURN SIGN(v_cnt);
END type_method_exists;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

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

Using SQL%ROWCOUNT with Dynamic PL/SQL

Using SQL%ROWCOUNT attribute when executing static or dynamic DML statement is very handy. Situation changes dramatically when you check this attribute after executing dynamic or static PL/SQL command:

Static PL/SQL

BEGIN
    NULL;
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=

Dynamic PL/SQL

BEGIN
    EXECUTE IMMEDIATE 'BEGIN NULL; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=1

Static PL/SQL “has” NULL as SQL%ROWCOUNT value while Dynamic PL/SQL – always “produces” 1, even if that dynamic PL/SQL does affect certain number of records in a table:

Dynamic SQL:

BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM emp WHERE ROWNUM<=2';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=2

Same command in Dynamic PL/SQL:

BEGIN
    EXECUTE IMMEDIATE 'BEGIN DELETE FROM emp WHERE ROWNUM<=2; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=1

Sometimes, we have to use dynamic PL/SQL so getting correct number of affected rows may be critical. Here is a simple but effective solution:

DECLARE
    v_cnt NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'BEGIN 
                           DELETE FROM emp WHERE ROWNUM<=2; 
                           :0:=SQL%ROWCOUNT; 
                       END;'  USING OUT v_cnt;
    dbms_output.put_line('Rowcount=' || v_cnt);
    ROLLBACK;
END;
/

Result:

Rowcount=2

We use bind variable in the OUT mode to get the result of STATIC SQL inside of

DYNAMIC PL/SQL.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

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

A few thoughts on parameterized cursors.

In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.

Let’s illustrate this point with a specific (though artificial) example.

Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:

SQL> SELECT deptno, ename, sal
  2  FROM emp e
  3  WHERE sal>(SELECT AVG(sal)
  4             FROM emp
  5             WHERE deptno=e.deptno)
  6  ORDER BY deptno, sal DESC, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN            1600

We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_deptno  NUMBER;
  v_avg_sal NUMBER;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_deptno
    AND sal>v_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_deptno:=v1.deptno;
    v_avg_sal:=v1.avg_sal;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.

Alternatively, we can you a record variable:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_dept d%ROWTYPE;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_dept.deptno
    AND sal>v_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_dept:=v1;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;

A better way to pass variables to a cursor is to use cursor parameters:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_deptno
    AND sal>c_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.

The following example will optimize the last block by using a single cursor parameter:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_dept d%ROWTYPE) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_dept.deptno
    AND sal>c_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?

  • Parameterized cursors support default values for cursor parameters
  • The cursors can be referenced more than once with different parameter values
  • A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

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

 

Get a comma separated line of Oracle table column names

When you have to manually put together an insert statement into a table with large number of columns you may feel some pain – it is tedious to concatenate all the columns in a comma delimited line. The following small procedure may save you some time and effort.

CREATE OR REPLACE PROCEDURE sp_get_column_line(p_table VARCHAR2)
AS
    v_result VARCHAR2(2000);
BEGIN
    SELECT listagg (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO v_result
    FROM user_tab_cols
    WHERE table_name=UPPER(p_table);

    DBMS_OUTPUT.PUT_LINE(LOWER(v_result));
END;
/
SQL> exec sp_get_column_line('emp')

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> exec sp_get_column_line('emp')
empno, ename, job, mgr, hiredate, sal, comm, deptno

PL/SQL procedure successfully completed.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

How to DELETE all records from all tables in a schema?

This is rather an exercise than a practical task. Anyway, the approach presented below might be helpful in some practical situations.

Problem Level: Beginner/Intermediate

Step 1: Disable all foreign key constraints.

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='ENABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' DISABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

Step 2: DELETE from all tables

DECLARE
  CURSOR c IS
  SELECT table_name
  FROM user_tables;
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'DELETE FROM ' || v.table_name;
  END LOOP;
END;
/

Step 3: COMMIT or ROLLBACK

ROLLBACK; --you can commit here instead if needed

STEP 4: Enable all FOREIGN KEY Constraints

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='DISABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' ENABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

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

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.

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

A function that converts a specially formatted character string to Oracle’s TIMESTAMP data type.

Problem: Convert a character string formatted ‘YYYY-MM-DD HH24:MI:SS.FF’ to TIMESTAMP. The string has to be validated before converting VARCHAR2 variable to TIMESTAMP.

The following function does the trick:

CREATE OR REPLACE FUNCTION TO_TS(p_ts_text VARCHAR2) RETURN TIMESTAMP
AS
BEGIN
    IF REGEXP_LIKE(p_ts_text, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{2}') THEN
        RETURN TO_TIMESTAMP(p_ts_text, 'YYYY-MM-DD HH24:MI:SS.FF');
    END IF;
    
    RAISE_APPLICATION_ERROR(-20012,'Invalid timestamp format is used');
END;

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

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

A tricky alternative to using the TYPE … IS RECORD statement in PL/SQL, by Zahar Hilkevich

This technique is handy when we get a refcursor from a stored procedure, so we cannot use cursor’s FOR LOOP.

We need to define a variable to be used for fetching the cursor.

TYPE dept_info_rt IS RECORD
   (
      dept_name      VARCHAR2(100),
      dept_number    NUMBER,
      emp_count      NUMBER,
      manager_name   VARCHAR2(100),
      budget         NUMBER,
      location       VARCHAR2(100)
   );

   v_rec dept_info_rt;

If we had a view (vw_dept) with all these fields, we could define the record variable using the %ROWTYPE attribute:

   v_rec vw_dept%ROWTYPE;

As a good alternative to a view in this case, we can use a custom cursor:

  CURSOR c IS
  SELECT dname as dept_name, deptno AS dept_number, 1 AS emp_count, ename AS manager_name,
         sal as budget, loc AS location
  FROM emp JOIN dept USING (deptno)
  WHERE 1=2;

  v_rec c%ROWTYPE;

As we can see, the cursor perfectly substitutes the view for our fetching needs.

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

Oracle’s Equivalent for Networkdays Function in Excel, by Zahar Hilkevich

I was recently approached by a colleague at work with a question if I know an Oracle’s Equivalent for Networkdays Function in Excel. I did not have an immediate answer, but a few minutes later I was able to find a way.

First, let’s define what the Networkdays function does. It should give the number of days between two given dates excluding Sundays and Saturdays.

If we had a calendar table (with a work_day column) the problem would be solved fairly easily:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D') BETWEEN 2 AND 6

To ensure that this code works for different geographic regions, it would be safer to add nls_date_language attribute as follows:

SELECT COUNT(1)
FROM calendar_table
WHERE TO_CHAR(work_day, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6

In absence of the calendar_table, we can mimic it using a standard method for a numeric/date range generator. Let say, we need to generate a range of dates between 15-May-2015 and 20-Aug-2015:

SELECT DATE'2015-05-15'+LEVEL-1 AS day
FROM dual
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result (partial):
DAY
---------
15-MAY-15
16-MAY-15
17-MAY-15
18-MAY-15
...
17-AUG-15
18-AUG-15
19-AUG-15
20-AUG-15

So after excluding the weekend days, the counting task becomes trivial:

SELECT COUNT(1)
FROM dual
WHERE TO_CHAR(DATE'2015-05-15'+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
CONNECT BY DATE'2015-05-15'+LEVEL-1 <= DATE'2015-08-20';

Result:
COUNT(1)
--------
      70

Finally, we are ready to package it all together and create a function:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_result INTEGER;
BEGIN
  SELECT COUNT(1) INTO v_result
  FROM dual
  WHERE TO_CHAR(p_date1+LEVEL-1, 'D', 'nls_date_language=AMERICAN') BETWEEN 2 AND 6
  CONNECT BY TRUNC(p_date1)+LEVEL-1 <= TRUNC(p_date2);

  RETURN v_result;
END;
/

This is a quite elegant solution, though I had a feeling that it is a bit over-complicated. A few minutes later, I derived a non-SQL approach:

CREATE OR REPLACE FUNCTION NETWORKDAYS(p_date1 DATE, p_date2 DATE) RETURN INTEGER
AS
   v_date1 DATE:=CASE TO_CHAR(p_date1, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date1)+1
		      WHEN 6 THEN TRUNC(p_date1)+2
		      ELSE TRUNC(p_date1)
		 END;
   v_date2 DATE:=CASE TO_CHAR(p_date2, 'D', 'nls_date_language=AMERICAN') 
		      WHEN 1 THEN TRUNC(p_date2)+1
		      WHEN 6 THEN TRUNC(p_date2)+2
		      ELSE TRUNC(p_date2)
		 END;
BEGIN
  IF p_date1>p_date2 THEN
	RETURN 0;
  ELSIF p_date1=p_date2 THEN
	RETURN 1;
  ELSE
	RETURN (TRUNC(v_date2, 'D') - TRUNC(v_date1, 'D')) * 5/7 +
 	       TO_CHAR(v_date2, 'D', 'nls_date_language=AMERICAN') -
	       TO_CHAR(v_date1, 'D', 'nls_date_language=AMERICAN');
  END IF;
END;
/

A few comments for better understanding the above code:
1) If one(or both) function parameter values is a week-end day, we change it to the following Monday as it does not change the number of non-weekend days in the given range.
2) TRUNC function with ‘D’ argument returns the first day of the week, so we can expect the number of days between two Sundays (or whatever the first day is) to be a multiple of 7, and so 5/7 gives us the number of non-weekend days.
3) TO_CHAR(v_date2, ‘D’, ‘nls_date_language=AMERICAN’) – TO_CHAR(v_date1, ‘D’, ‘nls_date_language=AMERICAN’) – is an adjustment to the result based on the day of the week of each of the date range parameters.

Quick test for the new function – Find number of work days before the end of the year:

SELECT  SYSDATE+LEVEL-1 AS day,
        TO_CHAR(sysdate+level-1, 'DY') AS day_of_week,
        networkdays(sysdate+level-1, date'2015-12-31') netdays
FROM dual
CONNECT BY LEVEL<=15

Result:
DAY       DAY_OF_WEEK     NETDAYS
--------- ------------ ----------
10-OCT-15 SAT                  58
11-OCT-15 SUN                  58
12-OCT-15 MON                  58
13-OCT-15 TUE                  57
14-OCT-15 WED                  56
15-OCT-15 THU                  55
16-OCT-15 FRI                  54
17-OCT-15 SAT                  53
18-OCT-15 SUN                  53
19-OCT-15 MON                  53
20-OCT-15 TUE                  52
21-OCT-15 WED                  51
22-OCT-15 THU                  50
23-OCT-15 FRI                  49
24-OCT-15 SAT                  48

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