Archive for March, 2013
Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an
ORA-22979 is raised is one of those, and the error is typically:
ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
This error occurs on an
INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an
UPDATE statement to populate
The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the
OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.
If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the
OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the
REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the
REF column empty:
-- Create the BASE_T type, or specification for IDL. CREATE OR REPLACE TYPE base_t IS OBJECT ( obj_id NUMBER , obj_name VARCHAR2(30) , obj_ref REF base_t) NOT FINAL; /
You can then create a table like the following:
CREATE TABLE base OF base_t ( obj_id CONSTRAINT base_pk PRIMARY KEY ) OBJECT IDENTIFIER IS PRIMARY KEY;
Let’s insert some rows to test for ourselves that this fails when you try to assign references:
INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL)); INSERT INTO base VALUES (base_t(2, 'Borfur',NULL)); INSERT INTO base VALUES (base_t(3, 'Gloin',NULL)); INSERT INTO base VALUES (base_t(4, 'Kili',NULL)); INSERT INTO base VALUES (base_t(5, 'Fili',NULL));
UPDATE statement attempts to assign references, but fails as shown below:
UPDATE base b SET obj_ref = REF(b);
UPDATE fails as shown:
UPDATE base b * ERROR at line 1: ORA-22979: cannot INSERT object VIEW REF OR user-defined REF
The simple fix redefines the object table by removing the
OBJ_ID column as an object identifier and primary key value. You do that by removing the
OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.
CREATE TABLE base OF base_t
Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.
The next statement creates a
CHILD table that holds a reference to the
BASE (or parent) table and another instance of the same
BASE_T object type:
CREATE TABLE child ( child_id NUMBER CONSTRAINT child_pk PRIMARY KEY , base_ref REF base_t SCOPE IS base , child base_t);
INSERT statement can’t use a
VALUES clause because we MUST capture the reference (or in this case primary key) from the
BASE (or parent) table. An
INSERT statement with a query does the trick:
INSERT INTO child SELECT 1, obj_ref, base_t(1, 'Gimli',NULL) FROM base b WHERE b.obj_name = 'Gloin';
You should note that the reference for the
CHILD column isn’t set but is likewise not required for the example to work.
Now, lets perform an standard
INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the
BASE (or parent) table and the table, column, and embedded
OBJ_REF of the CHILD table.
COLUMN father FORMAT A10 COLUMN son FORMAT A10 SELECT b.obj_name AS "Father" , c.child.obj_name AS "Son" FROM base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;
You get the following results:
Father Son ---------- ---------- Gloin Gimli
You can make a view of this table with either of these syntaxes:
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS SELECT * FROM base;
CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS SELECT * FROM base;
Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.
Somebody was trying to create a striped view based on a table’s
end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).
Basically, you create a user-defined data type, or structure:
1 2 3 4
CREATE OR REPLACE TYPE item_structure IS OBJECT ( id NUMBER , lookup VARCHAR2(30)); /
Then, you create a list (an Oracle table) of the structure, like:
CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure; /
Lastly, you create an object table function, like:
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
CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS -- Declare a variable that uses the record structure. lv_counter PLS_INTEGER := 1; -- Declare a variable that uses the record structure. lv_lookup_table ITEM_LOOKUP := item_lookup(); -- Declare static cursor structure. CURSOR c IS SELECT cl.common_lookup_id AS lookup_id , SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning FROM common_lookup cl WHERE cl.common_lookup_table = 'ITEM' AND cl.common_lookup_column = 'ITEM_TYPE' AND SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1) ORDER BY cl.common_lookup_meaning; BEGIN FOR i IN c LOOP lv_lookup_table.EXTEND; /* The assignment pattern for a SQL collection is incompatible with the cursor return type, and you must construct an instance of the object type before assigning it to a collection. */ lv_lookup_table(lv_counter) := item_structure( i.lookup_id , i.lookup_meaning ); lv_counter := lv_counter + 1; END LOOP; /* Call an autonomous function or procedure here! It would allow you to capture who queried what and when; and acts like a pseudo trigger for queries. */ RETURN lv_lookup_table; END; /
Now you can embed the object table function in a view, like this:
1 2 3
CREATE OR REPLACE VIEW item_lookup_view AS SELECT * FROM TABLE(get_item_types);
Why not simply use an embedded query in the view, like the following?
SQL> CREATE OR REPLACE VIEW normal_view AS 2 SELECT cl.common_lookup_id AS lookup_id 3 , SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning 4 FROM common_lookup cl 5 WHERE cl.common_lookup_table = 'ITEM' 6 AND cl.common_lookup_column = 'ITEM_TYPE' 7 AND SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1) 8 ORDER BY cl.common_lookup_meaning;
My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.
Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.
It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).
Anyway, let me quote the rules:
Oracle recommends that the password you specify:
- Contains at least one lowercase letter.
- Contains at least one uppercase letter.
- Contains at least one digit.
- Is at least 8 characters in length.
- Uses the database character set which can include the underscore (_), dollar ($), and pound sign (#) character.
- If (the password) contains special characters, including beginning the password with a number or symbol, then enclose the password with double-quotation marks.
- Should not be an actual word.
Likewise, you can’t use the old educational passwords:
SYSaccount password cannot be
SYSTEMaccount password cannot be
SYSMANaccount password cannot be
DBSNMPaccount password cannot be
- If you choose to use the same password for all the accounts, then that password cannot be
Hope this helps, and by the way reading the documentation never hurts too much! 🙂
I’m off to speak at the Utah Oracle User’s Group Training Days 2013 tomorrow and Thursday. I’m presenting on Oracle Database Triggers and on techniques leveraging PHP and MySQL Striped Views. If you’re not there, you can check this older (but recently updated) post on PHP and MySQL Striped Views.
Hope to see a few folks who use the blog. BTW, I won’t be at Collaborate 2013 this year. Travel budgets are always tight, and this year they’re more than tight. 😉
My take on the good, the bad, and the ugly of our Winter season, beside all the snow I’ve shoveled and blown to get in and out of my driveway, is:
February brought us MySQL 5.6 GA (General Availability) and Ruby 2.0.0-p0. Mega thanks to the MySQL and Ruby teams! By the way, if you’ve always wanted to know a little about Ruby without a great deal of effort, you should check this awesome little 20 minute Ruby tutorial. Naturally, the best Ruby Programming book is a bit dated, and maybe there will be a new one later in the year. If anybody knows whether there’s a new edition forthcoming, I’d appreciate hearing about it! Another more current, Ruby 1.9, book is Programming Ruby 1.9: The Pragmatic Programmers’ Guide.
It seems like a lot of folks who implemented Microsoft SharePoint are rapidly preparing to migrate from SharePoint 2010 to 2013, but what’s unclear is whether they’re upgrading for the features or bug fixes. It does seem many are opting for the Microsoft cloud services, and rumor has it that’s because a little collaboration requires a lot of hardware in the data center. It’s probably a good idea to do some research before implementing Microsoft SharePoint.
If NBC got the quote right today, President Obama disappointed me by saying, “… I should somehow do a Jedi mind meld with these (republican) folks …” How can the President not know that Star Trek Vulcan’s aren’t in the Star Wars universe? Worse yet, my disappointment with Peter Jackson returns because he is releasing the theatrical version The Hobbit: An Unexpected Journey Bluray and DVD on March 19th and then an extended version later in the year. Ouch!!! He’s getting into my wallet again.