Archive for the ‘Objects’ Category
Use an object in a query?
Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:
SELECT instance.get_type() * ERROR AT line 4: ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier |
The problem is how Oracle treats runtime views, which appears to me as a casting error. Somewhat like the ORDER BY clause irregularity that I noted in July, the trick is complete versus incomplete syntax. The following query fails and generates the foregoing error:
1 2 3 4 | SELECT instance.get_type() AS object_type , instance.to_string() AS object_content FROM (SELECT dependent()AS instance FROM dual); |
If you add a table alias, or name, to the runtime view on line 4, it works fine:
1 2 3 4 | SELECT cte.instance.get_type() AS object_type , cte.instance.to_string() AS object_content FROM (SELECT dependent() AS instance FROM dual) cte; |
That is the trick. You use an alias for the query, which assigns the alias like a table reference. The reference lets you access instance methods in the scope of a query. Different columns in the query’s SELECT-list may return different results from different methods from the same instance of the object type.
You can also raise an exception if you forget the open and close parentheses for a method call to a UDT, which differs from how Oracle treats no argument functions and procedures. That type of error would look like this:
SELECT cte.instance.get_type AS object_type * ERROR AT line 1: ORA-00904: : invalid identifier |
It is an invalid identifier because there’s no public variable get_type, and a method is only found by using the parenthesis and a list of parameters where they’re required.
The object source code is visible by clicking on the expandable label below.
Setup Object Types ↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | DROP TYPE dependent_t; DROP TYPE base_t; SET SERVEROUTPUT ON SIZE UNLIMITED -- Create an object. CREATE OR REPLACE TYPE base_t IS OBJECT ( TYPE VARCHAR2(20) , CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT , MEMBER FUNCTION get_type RETURN VARCHAR2 , MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / -- Create an object body. CREATE OR REPLACE TYPE BODY base_t IS CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS BEGIN RETURN; END base_t; MEMBER FUNCTION get_type RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END; MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END to_string; END; / -- Create a subtype. CREATE OR REPLACE TYPE dependent UNDER base_t ( child VARCHAR2(40) , CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION dependent ( child VARCHAR2 ) RETURN SELF AS RESULT , OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / -- Create a subtype body. CREATE OR REPLACE TYPE BODY dependent IS CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT IS BEGIN /* Assign subtype name to type. */ self.TYPE := 'DEPENDENT'; RETURN; END dependent; CONSTRUCTOR FUNCTION dependent ( child VARCHAR2 ) RETURN SELF AS RESULT IS lv_dependent DEPENDENT := dependent(); BEGIN /* Assign default constructor to self instance. */ self := lv_dependent; /* Assign parameters to object instance. */ self.TYPE := 'DEPENDENT'; self.child := child; RETURN; END dependent; OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END; OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END to_string; END; / |
As always, I hope this helps those solving problems.
Find Type Dependents
Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:
DROP TYPE item_object * ERROR at line 1: ORA-02303: cannot DROP OR REPLACE a TYPE WITH TYPE OR TABLE dependents |
They had a great point, so here I’m providing and object table function (object table functions are covered in Chapter 8 of Oracle Database 12c PL/SQL Programming book) that displays an object dependency tree.
Unlike table functions in Microsoft SQL Server or PostgreSQL, Oracle requires that you create the object type before you can return a result set table from a parameter-driven function. Here’s the object type for this object table function:
1 2 3 4 5 6 7 8 9 10 11 | -- Create an object type that mimics a record structure. CREATE OR REPLACE TYPE type_tree IS object ( level_id NUMBER , type_name VARCHAR2(30) , object VARCHAR2(10) , parent_type VARCHAR2(30)); / -- Create a UDT Collection. CREATE OR REPLACE TYPE type_tree_table IS TABLE OF type_tree; / |
Now, you can create the object table function that returns an object type dependent tree. This function uses branching recursion because you need to walk the dependency tree. The cursor against the three types of metadata in the database catalog lets us walk the tree. Each object type, and their dependents may have:
- A dependent object type
- A dependent collection, which uses the object type as a base object type.
- A dependent schema function, which uses the object type as a parameter data type or return data type.
- A dependent schema procedure, which uses the object type as a parameter data type.
- A dependent package, which contains a function or procedure using the object type as a parameter data type, or a function using the object type as a return data type.
Here’s the type_dependent function that walks the tree and displays the hierarchical results:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | CREATE OR REPLACE FUNCTION type_dependents ( pv_base_object_type VARCHAR2 , pv_level_id NUMBER DEFAULT 1 , pv_collection TYPE_TREE_TABLE DEFAULT NULL ) RETURN TYPE_TREE_TABLE IS /* Declare a return data type. */ lv_level_id NUMBER; /* Declare item type. */ lv_type_name VARCHAR2(30); lv_object_type VARCHAR2(30); lv_parent_type VARCHAR2(30); /* Declare a collection variable. */ lv_collection TYPE_TREE_TABLE := type_tree_table(); lv_incoming TYPE_TREE_TABLE := type_tree_table(); /* The first part of the cursor finds the dependent type names of complex object types, and the second part of the cursor finds the dependent collection types. Effectively the set operator finds two distinct branches because you may use any base type as an element of a complex object or of a collection. */ CURSOR base_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT (cv_level_id) AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || ut.type_name AS type_name , ut.typecode AS object_type , NULL AS parent_type FROM user_types ut WHERE ut.type_name = cv_base_type; CURSOR dependent_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uta.type_name AS type_name , ut.typecode AS object_type , ut.type_name AS parent_type FROM user_type_attrs uta INNER JOIN user_types ut ON uta.attr_type_name = ut.type_name WHERE ut.type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uct.type_name AS type_name , CASE WHEN uct.coll_type = 'TABLE' THEN uct.coll_type ELSE 'VARRAY' END AS object_type , ut.type_name AS parent_type FROM user_types ut INNER JOIN user_coll_types uct ON ut.type_name = uct.elem_type_name WHERE uct.elem_type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , CASE WHEN package_name IS NULL THEN LPAD(' ', 2*(cv_level_id - 1)) || ua.object_name ELSE LPAD(' ', 2*(cv_level_id - 1)) || ua.package_name END AS type_name , CASE WHEN package_name IS NULL THEN uo.object_type ELSE 'PACKAGE' END AS object_type , ua.type_name AS parent_type FROM user_arguments ua LEFT JOIN user_objects uo ON ua.package_name = uo.object_name OR ua.object_name = uo.object_name WHERE type_name = cv_base_type ORDER BY object_type; BEGIN /* Set the call level indicator. */ lv_level_id := pv_level_id; /* Check if the collection holds values, which should occur in recursive calls. */ IF pv_collection IS NOT NULL THEN lv_collection := pv_collection; ELSE /* Open the base type and record level 1. */ OPEN base_type (lv_level_id, pv_base_object_type); FETCH base_type INTO lv_level_id , lv_type_name , lv_object_type , lv_parent_type; CLOSE base_type; /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( lv_level_id , lv_type_name , lv_object_type , lv_parent_type ); /* Increment the type dependency level. */ lv_level_id := lv_level_id + 1; END IF; /* Loop through and return records. */ FOR i IN dependent_type(lv_level_id, pv_base_object_type) LOOP /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( i.level_id , i.type_name , i.object_type , i.parent_type ); /* Recursively call down to dependent types. */ IF i.type_name <> i.parent_type THEN lv_collection := type_dependents(TRIM(i.type_name), (lv_level_id + 1), lv_collection); END IF; END LOOP; /* Return 0 for false. */ RETURN lv_collection; END; / |
You would query the function with the TABLE function, like this:
COLUMN level_id FORMAT 99999 HEADING "Level|ID #" COLUMN type_name FORMAT A30 HEADING "Type Name" COLUMN object FORMAT A10 HEADING "Object Type" COLUMN parent_type FORMAT A30 HEADING "Parent Type" SELECT * FROM TABLE(type_dependents('ITEM_OBJECT',1)); |
It displays the following results based on the sample types created by the original blog post:
Level
ID # Type Name Object Typ Parent Type
------ ------------------------------ ---------- ------------------------
1 ITEM_OBJECT OBJECT
2 GET_ITEM_OBJECT FUNCTION ITEM_OBJECT
2 IDENTIFIED_OBJECT OBJECT ITEM_OBJECT
2 ITEM_PACKAGE PACKAGE ITEM_OBJECT
2 ITEM_TABLE TABLE ITEM_OBJECT
3 ITEM_ASYNC_TABLE COLLECTION ITEM_TABLE
4 ITEM_LIST TABLE ITEM_ASYNC_TABLE
2 ITEM_VARRAY VARRAY ITEM_OBJECT
3 ITEM_ASYNC_VARRAY COLLECTION ITEM_VARRAY
4 ITEM_ARRAY VARRAY ITEM_ASYNC_VARRAY
10 rows selected. |
I hope this helps those working with Oracle Database 10g (de-supported), 11g, or 12c. As a note, you don’t need to drop type dependents in Oracle 12c because it supports type evolution. I cover type evolution in Appendix B of Oracle Database 12c PL/SQL Programming. As always, I hope this helps those trying to determine type dependents in an Oracle database.
OUT Parameter Trick
Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an OUT-only mode parameter from a procedure.
That’s a great question, and it’s actually simple once you understand the difference between Oracle object types and other data types. Oracle object types must always be initiated before you use them, which means you must initialize any OUT-only mode parameters at the top of your execution section, like this:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE PROCEDURE reset_troll ( pv_troll OUT TROLL_OBJECT ) IS /* Troll default name. */ lv_troll_name VARCHAR2(20) := 'Bert'; BEGIN /* Initialize the incoming parameter by allocating memory to it. */ pv_troll := troll_object(); /* Set the name to something other than the 'Tom' default value. */ pv_troll.set_troll(lv_troll_name); END reset_troll; / |
Line 7 shows you the trick, initialize the incoming parameter because there isn’t an incoming parameter for an OUT-only mode parameter. The calling parameter to an OUT-only mode parameter is only a reference where PL/SQL will copy the internal object reference. While the calling parameter has been initialized, the reference to the call parameter’s object is where the internal object will be copied. The local program must first ensure a new memory location for a new instance of the object type before it can act on or return an object instance to the external reference. More or less, the internal object is copied to the calling object instance’s memory location when the procedure completes its execution.
Here’s the source code for the troll_object object type and body:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | CREATE OR REPLACE TYPE troll_object IS OBJECT ( troll VARCHAR2(20) , CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION troll_object ( troll VARCHAR2 ) RETURN SELF AS RESULT , MEMBER FUNCTION get_troll RETURN VARCHAR2 , MEMBER PROCEDURE set_troll (troll VARCHAR2) , MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / CREATE OR REPLACE TYPE BODY troll_object IS /* Default no-argument constructor. */ CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT IS troll TROLL_OBJECT := troll_object('Tom'); BEGIN SELF := troll; RETURN; END troll_object; /* Single argument constructor. */ CONSTRUCTOR FUNCTION troll_object (troll VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.troll := troll; RETURN; END troll_object; /* A getter function. */ MEMBER FUNCTION get_troll RETURN VARCHAR2 IS BEGIN RETURN SELF.troll; END get_troll; /* A setter procedure. */ MEMBER PROCEDURE set_troll (troll VARCHAR2) IS BEGIN SELF.troll := troll; END set_troll; /* A function that returns the formatted object type's contents. */ MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN 'Hello '||SELF.troll; END to_string; END; / |
You can test the reset_troll procedure with the following anonymous block:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | /* Enable printing from a PL/SQL block. */ SET SERVEROUTPUT ON SIZE UNLIMITED /* Anonymous testing block. */ DECLARE lv_troll TROLL_OBJECT := troll_object('Bill'); BEGIN dbms_output.put_line('--------------------'); /* Prints 'Hello William' */ dbms_output.put_line(lv_troll.to_string()); dbms_output.put_line('--------------------'); reset_troll(lv_troll); /* Prints 'Hello Bert' */ dbms_output.put_line(lv_troll.to_string()); dbms_output.put_line('--------------------'); END; / |
If you remark out line 7 from the reset_troll procedure, you’d raise the following exception by the call on line 10 because the local object hasn’t been instantiated (given life). It means there’s no memory location allocated for the instantiated (instance of an object type).
-------------------- Hello Bill -------------------- DECLARE * ERROR at line 1: ORA-30625: method dispatch ON NULL SELF argument IS disallowed ORA-06512: at "VIDEO.RESET_TROLL", line 10 ORA-06512: at line 8 |
Hope this helps those trying to solve the same problem.
Encrypting a Column
A few years ago, I gave a session on Oracle’s Data Vault. An attendee from that session and I happened to be seated at the same table for lunch last Thursday when I presented at Utah Oracle User Group Fall Symposium. He asked if I’d ever found a way to encrypt a column from the prying eyes of the SYS user. I said yes, and he asked how. It was a bit more than could be explained at lunch and promised to put it on the blog. (Disclaimer: It is possible to unwrap wrapped code but the mechanics change with each release and they require access to the SYS schema and substantial Application DBA knowledge; however, there are scripts published on the Internet to unwrap the code. There are also other vulnerabilities in the example, but I’ll pass on exposing them. After all this was written to illustrate an approach.)
It’s important to note you can now hide columns in Oracle Database 12c, but they’re still visible to the DBA-level staff. That’s why I’ll publish a new article on re-writing this encrypted object as a Java library, and disclose how to hide password exchanges from the SGA area.
This demonstrates how you can encapsulate a column from even the SYS user. I also put it into Appendix D, PL/SQL Built-in Packages and Types to illustrate the DBMS_CRYPTO package. It uses a User Defined Type (UDT) and assumes you have a working knowledge of object types in Oracle 10g forward. If not, you can find them in:
- Chapter 11 of my Oracle Database 12c PL/SQL Programming book.
- Chapter 14 of my Oracle Database 11g PL/SQL Programming book.
It also assumes basic knowledge of Oracle’s encryption technology, some of which is explained in the new Oracle Database 12c PL/SQL Programming book.
For reference, a good DBA would simply find this clear text password in the SGA. A more secure approach might be hiding the encryption keyword in a one column and table or embedded in some string within a common lookup table column value as a position specific substring. Alas, I don’t have time to write something so elaborate.
The steps are:
- You must create a user defined type (UDT), which sets the up a single salary column.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE TYPE masked IS OBJECT ( salary RAW(1000) , CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT , MEMBER FUNCTION get_raw_salary RETURN RAW , MEMBER FUNCTION get_salary ( KEY VARCHAR2 ) RETURN NUMBER , MEMBER PROCEDURE to_string , ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER ) INSTANTIABLE FINAL; / |
- As the
SYSuser, you must grantEXECUTEprivilege on theDBMS_CRYPTOpackage to the targetSCHEMAuser.
1 | GRANT EXECUTE ON dbms_crypto TO schema_name; |
- You implement the
MASKEDUDT, as follows below (source unencrypted, don’t worry I show you how to encrypt [wrap] it in a moment).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | CREATE OR REPLACE TYPE BODY masked IS CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS /* Create a placeholder for a zero salary, like an empty_clob() call. */ zero MASKED := masked(0); BEGIN /* Assign an encrypted zero salary to the instance. */ self := zero; RETURN; END masked; CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_key RAW(1000); lv_raw RAW(1000); lv_encrypted_data RAW(1000); BEGIN /* Dynamic assignment. */ lv_raw := UTL_RAW.cast_to_raw(NVL(salary,0)); /* Convert to a RAW 64-character key. */ lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); /* Encrypt the salary before assigning it to the object type attribute */ lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); self.salary := lv_encrypted_data; RETURN; END masked; MEMBER FUNCTION get_raw_salary RETURN RAW IS BEGIN RETURN self.salary; END get_raw_salary; MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_decrypted_data RAW(4000); lv_key RAW(1000); lv_return_value NUMBER; BEGIN /* Verify key value matches local value before decrypting, substitute a zero value when the key doesn't match. */ IF key = lv_key_string THEN lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); lv_return_value := TO_NUMBER(TO_CHAR(UTL_RAW.cast_to_number(lv_decrypted_data),'9999990.00')); ELSE lv_return_value := 0; END IF; RETURN lv_return_value; END get_salary; ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_decrypted_self RAW(4000); lv_decrypted_peer RAW(4000); lv_key RAW(1000); BEGIN /* Decrypt the current and peer object attribute values before comparing their values. */ lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); /* Sort order key. */ IF lv_decrypted_self < lv_decrypted_peer THEN RETURN 1; ELSE RETURN 0; END IF; END equals; MEMBER PROCEDURE to_string IS BEGIN /* Provide a to_string method for good practice. */ DBMS_OUTPUT.put_line('Encrypted value'); END to_string; END; / |
- You implement the
MASKEDUDT encrypted by using theDBMS_DDLpackage, as follows below. This ensures that others can’t read the source code by querying theALL_,DBA_, orUSER_SOURCEviews. You should note that I’ve removed comments and unnecessary spaces.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | BEGIN DBMS_DDL.create_wrapped( 'CREATE OR REPLACE TYPE BODY masked IS ' || 'CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS ' || 'zero MASKED := masked(0); ' || 'BEGIN ' || 'self := zero; ' || 'RETURN; ' || 'END masked; ' || 'CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_key RAW(1000); ' || 'lv_raw RAW(1000) := RPAD(utl_raw.cast_from_number(salary),32,''0''); ' || 'lv_encrypted_data RAW (1000); ' || 'BEGIN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string); ' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); ' || 'self.salary := lv_encrypted_data; ' || 'RETURN; ' || 'END masked; ' || 'MEMBER FUNCTION get_raw_salary RETURN RAW IS ' || 'BEGIN ' || 'RETURN self.salary; ' || 'END get_raw_salary; ' || 'MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_decrypted_data RAW(4000); ' || 'lv_key RAW(1000); ' || 'lv_return_value NUMBER; ' || 'BEGIN ' || 'IF key = lv_key_string THEN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string); ' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); ' || 'lv_return_value := TO_NUMBER(TO_CHAR(utl_raw.cast_to_number(lv_decrypted_data),''9999990.00'')); ' || 'ELSE ' || 'lv_return_value := 0; ' || 'END IF; ' || 'RETURN lv_return_value; ' || 'END get_salary; ' || 'ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_decrypted_self RAW(4000); ' || 'lv_decrypted_peer RAW(4000); ' || 'lv_key RAW(1000); ' || 'BEGIN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string);' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); ' || 'lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); ' || 'IF lv_decrypted_self < lv_decrypted_peer THEN ' || 'RETURN 1; ' || 'ELSE ' || 'RETURN 0; ' || 'END IF; ' || 'END equals; ' || 'MEMBER PROCEDURE to_string IS ' || 'BEGIN ' || 'dbms_output.put_line(''Encrypted value''); ' || 'END to_string; ' || 'END; '); END; / |
You can read more about wrapping PL/SQL in Appendix F of Oracle Database 12c PL/SQL Programming.
- You can test a single instance with this anonymous PL/SQL block.
1 2 3 4 5 6 7 8 9 | DECLARE o MASKED := masked(82000.12); BEGIN DBMS_OUTPUT.put('Override: '); o.to_string(); DBMS_OUTPUT.put_line('Decrypted: '||o.get_salary('Encrypt Me!')); DBMS_OUTPUT.put_line('Bad Key: '||o.get_salary('Incorrect')); END; / |
Override: Encrypted value Decrypted: 82000.12 Bad Key: 0 |
- You can test a series of instances by making them persistent objects, or columns in a table, and then query values from them. It also lets you you test the sorting feature provided in the UDT.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE sort_demo (salary MASKED); INSERT INTO sort_demo VALUES (masked(82000.24)); INSERT INTO sort_demo VALUES (masked(61000.12)); INSERT INTO sort_demo VALUES (masked(93000.36)); SELECT salary AS "Encrypted" FROM sort_demo; COLUMN unordered FORMAT 9,999,990.00 HEADING "Unordered|List" SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Unordered FROM sort_demo; COLUMN ordered FORMAT 9,999,990.00 HEADING "Ordered|List" SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Ordered FROM sort_demo ORDER BY 1; |
Encrypted(SALARY)
--------------------------------------------------------------------------
MASKED('1798C04F8342C53A826144568075CBDB145D0C9BD226B410C8C7167B76382D86')
MASKED('82F783F2E117AA60955B0A2E73545506936D6F8FFBEC5D9E0D8E70B82D4B694D')
MASKED('1551F350AAEB30ADFC1527F25CAA935732243858AF1C5D724A78B997A4394EAD')
Unordered
List
-------------
82,000.24
61,000.12
93,000.36
Ordered
List
-------------
61,000.12
82,000.24
93,000.36 |
If you want to retest this, make sure you drop the SORT_DEMO table first. Hope this helps any interested in an elaborate solution.
Understanding Java Enum
Somebody wanted an example of how to write an Enum class in Java 7 (a bit late since its introduced in Java 5) because they found the Enum tutorial unhelpful (not as helpful to their purpose at hand). They wanted to understand how to use an Enum type in another class. Here’s an example set of files to do that and here’s the link to the jazzed up Java 7 API online docs).
First, you need to understand that while the equals(), toString(), and hashCode() override methods should always be provided in your classes. The exception is when they’re designated final, like the toString() and hashCode() methods of the Enum class. Second, you can write an Enum class with or without private variables. The inclusion of private instance variables makes the Enum a complex Enum (that’s just the vocabulary for the Java certification tests.
Sample Enum Class
The AppleComputer class is a complex Enum, and you should note that the constructor is private, and must always define the values of instance variables. The instance variables are defined within the parentheses after the name in the enumeration list.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | /** * Enumeration class AppleComputer - write a description of the enum class here * * @author Michael McLaughlin * @version 1.0 */ public enum AppleComputer { /** * This is an entry-level desktop computer. */ IMAC("Entry-level Desktop",1199), /** * This is an manager-level laptop computer. */ MACBOOKAIR("Manager-level Laptop",999), /** * This is an manager-level laptop computer. */ MACBOOKPRO("Developer-level Laptop",1199), /** * This is an developer-level laptop computer. */ MACMINI("Mini-Desktop",599), /** * This is a mini-desktop computer. */ MACPRO("Desktop",2499); /** * Private variable definitions. */ private double cost; private String description; /** * Constructs an instance with a cost and description. */ private AppleComputer(String description, double cost) { this.description = description; this.cost = cost; } /** * Returns the cost field of an Apple Computer. */ public double getCost() { return this.cost; } /** * Returns the description field of an Apple Computer. */ public String getDescription() { return description; } /** * Returns the description field of an Apple Computer. */ public String getDescription(String name) { return this.description; } /** * Returns the equality of between two AppleComputer Enum types. */ public boolean equals(AppleComputer ac) { // First comparision on primitives and second on String instances. if ((this.cost == ac.getCost()) && (this.description.equals(ac.getDescription()))) return true; else return false; } /** * Method to test class integrity. */ public static void main(String[] args) { if (args.length == 1) { System.out.printf("Apple Computer : %s is %s\n", AppleComputer.valueOf(args[0]).toString(), AppleComputer.IMAC.getDescription(args[0])); System.exit(0); } else { for (AppleComputer ac : AppleComputer.values()) System.out.printf("Apple Computer : %s is %s\n", ac, ac.description); }} } |
Sample Class that uses the Enum Class
The EnumTextUse class demonstrates how to use and identify the instance of a complex Enum class in another class. The setState() method has two approaches, one where you pass the name and discover viable enumeration possibilities, and two when you pass an Enum instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | /** * The EnumTextUse class demonstrates how to identify and use an Enum * class that contains a text value. * * @author Michael McLaughlin * @version 1.0 */ /** * Import classes. */ import java.text.NumberFormat; import java.text.DecimalFormat; public class EnumTextUse { /** * Declare class level variables. */ private AppleComputer ac; private String desc; /** * Constructor for objects of class TestEnum */ public EnumTextUse() {} /** * An example of a method that takes a text string to find the ENUM * element value. */ public int setState(String name) { // Declare local variable for false. int returnValue = 0; // put your code here try { // Verify that it's a valid instance of AppleComputer. if (AppleComputer.valueOf(name) instanceof AppleComputer) { if (AppleComputer.IMAC == AppleComputer.valueOf(name)) { this.ac = AppleComputer.IMAC; } else if (AppleComputer.MACBOOKAIR == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACBOOKAIR; } else if (AppleComputer.MACBOOKPRO == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACBOOKPRO; } else if (AppleComputer.MACPRO == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACPRO; } else if (AppleComputer.MACMINI == AppleComputer.valueOf(name)) { this.ac = AppleComputer.MACMINI; }} // Return -1 as the truth indicator state was set or unnecessary. returnValue = -1; } catch (Exception e) { System.out.println(name + " is no longer sold."); } // Return the int value for true or false. return returnValue; } /** * An example of a method that takes a text string to find the ENUM * element value. */ public int setState(AppleComputer ac) { // Assign the AppleComputer fields to a local variable. double costSavings; double localCost = this.ac.getCost(); String localDesc = this.ac.toString() + " : " + this.ac.getDescription(); // Define format mask for output. NumberFormat f = new DecimalFormat("##,###,##0.00"); // Declare local variable for false. int returnValue = 0; // put your code here try { // Check for an instance of the Enum. if (this.ac instanceof AppleComputer) { // Find different (unequal) instances and update with the new one. if (this.ac.equals(ac)) { // Print message on match between prior and set value of AppleComputer. this.desc = this.ac + " is the authorized platform and no cost difference."; } else { // Assign the new Enum value, calculate and display cost savings message. this.ac = ac; costSavings = this.ac.getCost() - localCost; // Determine the message based on a reduced or increased cost of replacement. if (costSavings > 0) { this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(costSavings) + " more than planned."; } else { this.desc = this.ac + " is substituted for " + localDesc + " at $" + f.format(Math.abs(costSavings)) + " less than planned."; }}} // Return -1 as the truth indicator state was set or unnecessary. returnValue = -1; } catch (Exception e) { System.out.println(e.getMessage()); } // Return the int value for true or false. return returnValue; } /** * Return the current description value. */ public String getState() { return this.desc; } /** * Allows testing the program. */ public static void main(String [] args) { // Declare a string of possible enumeration types. String [] list = {"IMAC","MACBOOK","MACBOOKAIR","MACBOOKPRO","MACMINI","MACPRO"}; // Construct a test instance. EnumTextUse etu = new EnumTextUse(); /** * Read through the list of enumeration types, printing output from * the inherited or overridden toString() method. */ for (int i = 0; i < list.length; i++) { if (etu.setState(list[i]) != 0) { // The company standard must apply in all cases. if (etu.setState(AppleComputer.IMAC) != 0) System.out.println(etu.getState()); } } } } |
You can run the EnumTextUse class from the command-line or tool of your choice, like:
$ java EnumTextUse |
It’ll print the following text:
IMAC is the authorized platform and no cost difference. MACBOOK is no longer sold. IMAC is substituted for MACBOOKAIR : Manager-level Laptop at $200.00 more than planned. IMAC is substituted for MACBOOKPRO : Developer-level Laptop at $0.00 less than planned. IMAC is substituted for MACMINI : Mini-Desktop at $600.00 more than planned. IMAC is substituted for MACPRO : Desktop at $1,300.00 less than planned. |
You can check this Java Community Process page for the nuts and bolts of the Enum class. As always, I hoped this helped. Let me know if anything requires more clarity or any correction.
MySQL Foreign Keys
One of my students asked how you validate the foreign keys in a MySQL database. First off, this only works if the database engine supports referential integrity (the fancy word for foreign keys as database level constraints). InnoDB and Falcon support referential integrity. The answer can be found by leveraging the data catalog in the INFORMATION_SCHEMA.
Here’s the query:
SELECT CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint" , CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key" , CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key" FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY' ORDER BY tc.table_name , kcu.column_name; |
It’s a bit different then the USER_CONSTRAINTS and USER_CONS_COLUMNS views in Oracle, which are covered in this prior post.
I recently ran across another interesting detail on MySQL foreign keys creation and removal that has to do with the case sensitivity of constraints. If you create the constraint in lower case and then try to drop the foreign key constraint in upper case, you may encounter the following error:
ERROR 1025 (HY000): Error on rename of '.\database_name\table_name' to '.\database_name\#sql2-79c-1' (errno: 152) |
The reason appears to be that MySQL can’t find the table with the constraint name, and therefore throws an error that appears related to failure writing the new structure. It can be misleading.
Watch the Event Logs
It’s the end of our Spring term, and yes occasionally somebody can’t sign on to their Oracle instance because their event log is full. They get the following message on Winodws:
C:\>sqlplus / AS sysdba SQL*Plus: Release 11.1.0.7.0 - Production ON Wed Jul 15 10:19:37 2009 Copyright (c) 1982, 2008, Oracle. ALL rights reserved. Enter password: ERROR: ORA-28056: Writing audit records TO Windows Event Log failed |
The fix is simple, just delete your items from your Windows’ event log. 😉
External Table Query Fix
The fact that you could raise an ugly error when you query an external table always bothered me. I looked at Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy.
If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don’t know about objects and object collection, you can refer to this post. Having provided you with the context, here’s an example that eliminates errors when querying an external table without an external file.
- Create an external file, like this
charactertable.
CREATE TABLE CHARACTER ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY download ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'DOWNLOAD':'character.bad' DISCARDFILE 'DOWNLOAD':'character.dis' LOGFILE 'DOWNLOAD':'character.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('character.csv')) REJECT LIMIT UNLIMITED; |
- Create a user-defined object type that mirrors your external table defintion, like this:
CREATE OR REPLACE TYPE character_obj IS OBJECT ( character_id NUMBER , first_name VARCHAR2(20) , last_name VARCHAR2(20)); / |
- Create a user-defined collection of your object type, like
CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj; / |
- Create a function that returns the user-defined collection of your object type, like
CREATE OR REPLACE FUNCTION character_source RETURN character_obj_table IS c NUMBER; collection CHARACTER_OBJ_TABLE := character_obj_table(); BEGIN FOR i IN (SELECT * FROM CHARACTER) LOOP collection.EXTEND; collection(c) := character_obj( i.character_id , i.first_name , i.last_name); c := c + 1; END LOOP; RETURN collection; EXCEPTION WHEN OTHERS THEN RETURN collection; END; / |
- Query the function not the table, which returns no rows found when the file doesn’t physically exist, or the file contains no data. Lastly, the function returns the data when it is there.
SELECT * FROM TABLE(character_source); |
Hope this helps those using external tables to avoid the typical error stack:
SELECT * FROM CHARACTER * ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: DATA cartridge error KUP-04040: file CHARACTER.csv IN CHARACTER NOT found |
I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.
Object constructor quirk
Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.
I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:
LINE/COL ERROR -------- ----------------------------------------------------------------- 4/11 PL/SQL: Item ignored 4/26 PLS-00307: too many declarations OF 'HELLO_THERE' MATCH this CALL 6/5 PL/SQL: Statement ignored 6/13 PLS-00320: the declaration OF the TYPE OF this expression IS incomplete OR malformed |
All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.
Broken Code ↓
This shows you the broken code and explains why it’s broken.
The broken code has an object type like the following. Interestingly enough, the object type will compile fine but the object body fails when the attribute variable name differs from a constructor function parameter value.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE TYPE hello_there IS OBJECT ( iv_who VARCHAR2(20) , CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION hello_there ( pv_who VARCHAR2 ) RETURN SELF AS RESULT , MEMBER FUNCTION get_who RETURN VARCHAR2 , MEMBER PROCEDURE set_who (pv_who VARCHAR2) , MEMBER PROCEDURE to_string ) INSTANTIABLE NOT FINAL; / |
The broken code has an object body like the following. The difference between the parameter name in the constructor and the object type attribute name causes the PLS-00307 exception. It took some playing around to figure out what it was really complaining about.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | CREATE OR REPLACE TYPE BODY hello_there IS CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS hello HELLO_THERE := hello_there('Generic Object.'); BEGIN SELF := hello; RETURN; END hello_there; CONSTRUCTOR FUNCTION hello_there (pv_who VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.iv_who := pv_who; RETURN; END hello_there; MEMBER FUNCTION get_who RETURN VARCHAR2 IS BEGIN RETURN SELF.iv_who; END get_who; MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS BEGIN SELF.iv_who := pv_who; END set_who; MEMBER PROCEDURE to_string IS BEGIN dbms_output.put_line('Hello '||SELF.iv_who); END to_string; END; / |
Working Code ↓
This shows you the working code and explains why it works.
The working code has an object type like the following. You should notice that the only difference renames the pv_who in the overriding constructor’s parameter list to iv_who. While this doesn’t throw an exception creating the user-defined object type, it does throw an exception when you try to create the object body or implementation of the object type.
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE TYPE hello_there IS OBJECT ( iv_who VARCHAR2(20) , CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION hello_there ( iv_who VARCHAR2 ) RETURN SELF AS RESULT , MEMBER FUNCTION get_who RETURN VARCHAR2 , MEMBER PROCEDURE set_who (pv_who VARCHAR2) , MEMBER PROCEDURE to_string ) INSTANTIABLE NOT FINAL; / |
The working code has an object body like the following. Like the object type before, the pv_who as a formal parameter of the constructor now uses the same variable name as the attribute for the object type.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | CREATE OR REPLACE TYPE BODY hello_there IS CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS hello HELLO_THERE := hello_there('Generic Object.'); BEGIN SELF := hello; RETURN; END hello_there; CONSTRUCTOR FUNCTION hello_there (iv_who VARCHAR2) RETURN SELF AS RESULT IS BEGIN SELF.iv_who := iv_who; RETURN; END hello_there; MEMBER FUNCTION get_who RETURN VARCHAR2 IS BEGIN RETURN SELF.iv_who; END get_who; MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS BEGIN SELF.iv_who := pv_who; END set_who; MEMBER PROCEDURE to_string IS BEGIN dbms_output.put_line('Hello '||SELF.iv_who); END to_string; END; / |
This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.
Subscribe via RSS