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”.

How to generate a list of first N binary numbers in Oracle SQL?

In my recent post I showed how to convert a decimal number (i.e. an integer) into a binary string. We can build upon that technique to answer the question:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.bin
FROM x, LATERAL (SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                        WITHIN GROUP(ORDER BY LEVEL DESC) bin
                 FROM dual
                 CONNECT BY POWER(2, LEVEL-1)<=x.N) y

Note the LATERAL keyword (Oracle 12c new feature) that enables us to reference “x” in the inline view “y”. In pre-12c world, we would have to use TABLE/CAST/MULTISET function composition to achieve the same result:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.column_value bin
FROM x, TABLE(CAST(MULTISET(
          SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                 WITHIN GROUP(ORDER BY LEVEL DESC) bin
          FROM dual
          CONNECT BY POWER(2, LEVEL-1)<=x.N) AS sys.odcivarchar2list)) y

The idea used in the following query is based on a totally different approach. It builds a string of “0”s and “1”s in a loop until its length reaches a desired value:

WITH x(v, n) AS (
SELECT column_value, 1
FROM TABLE(sys.odcivarchar2list('0','1'))
UNION ALL
SELECT x.v || t.column_value, x.n+1
FROM TABLE(sys.odcivarchar2list('0','1')) t JOIN x on LENGTH(x.v)=n
WHERE n<=CEIL(LOG(2,50))
), y AS (
SELECT NVL(LTRIM(x.v,'0'),'0') bin, ROWNUM-1 dec
FROM x
WHERE n=(SELECT MAX(n) FROM x)
)
SELECT *
FROM y
WHERE dec<=50

To better understand the above query, try the following one:

SELECT *                            
FROM TABLE(sys.odcivarchar2list('0','1')), 
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1'))

If we put enough tables in the Cartesian product and concatenate all column_value columns in a single character string expression, we will achieve our goal. The challenge with this approach is to dynamically change the number of the tables in the FROM clause. This can be simulated in the recursive WITH clause by repeatedly adding more and more collections of bits (0 and 1).

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”.