Archive for March, 2010
I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.
I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.
Naturally, suggestions are always welcome.
My son’s graduating with his B.S. in Computer Information Technology next month, moving on to his internship, and today released his first independent iPhone App – Chronos Stopwatch. This link takes you to his blog. He did an awesome job on writing the Bizarro iPhone App under contract, but I’m very happy he and Miles Ponson started writing their own software.
It was amazing seeing him sort through all the myriad issues in balancing analog clocks, the decaseconds refresh rates, and a lap counter. I’m sure a few swimming and track coaches will like this utility if they have an iPhone. Throughout his development cycle, the process has been fun to watch. He certainly loves Objective C.
Just as an aside, it has also been gratifying to know that he finally found major uses for the database, like set operators, inline views, and stored procedures. He once noted they weren’t too useful. 😉
Ruairi asked how you could disable automatic start of the Oracle Service for Oracle 11g on Windows 7 (a comment here). Ruairi also provided a nice Windows shell script that you can copy for starting and stopping the Oracle Service in his last comment.
The simplest way is to launch a command shell because I don’t want to provide all the navigation variations for different Windows versions.
Basically, you do that by clicking the Windows Start button and type
cmd word in the run entry box. This launches a command session. Type the following from the prompt. It launches the Windows Services console in all relevant versions:
Now you’ll see the Windows Services console. Navigate to the Oracle Service and right click on it. You choose Properties.
That will bring you to this screen. Click on the drop down for the Startup type and choose Manual. Click the OK button to complete the step. That’s it, the next time you start the machine the Oracle database won’t start automatically. You should do the same to the other Oracle Services.
If you don’t have a lot of memory and it’s a development machine, this makes a lot of sense.
I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.
Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.
I once used the comma-delimited tables (like everybody else), but now I try to always use the newer
CROSS JOIN syntax. In both cases the range join is put in the
WHERE clause. The new syntax uses an
INNER JOIN and an
ON clause to hold the range match. Examples of all are below.
Comma-delimited Filtered Cross Join
1 2 3 4 5
SELECT c.month_short_name , t.transaction_amount FROM calendar_join c, transaction_join t WHERE t.transaction_date BETWEEN c.start_date AND c.end_date ORDER BY EXTRACT(MONTH FROM t.transaction_date);
1 2 3 4 5
SELECT c.month_short_name , t.transaction_amount FROM calendar_join c CROSS JOIN transaction_join t WHERE t.transaction_date BETWEEN c.start_date AND c.end_date ORDER BY EXTRACT(MONTH FROM t.transaction_date);
1 2 3 4 5
SELECT c.month_short_name , t.transaction_amount FROM calendar_join c INNER JOIN transaction_join t ON (t.transaction_date BETWEEN c.start_date AND c.end_date) ORDER BY EXTRACT(MONTH FROM t.transaction_date);
INDEX on the start and end date of the
CALENDAR_JOIN table, the Oracle explain plan for all three queries is:
1 2 3 4 5 6 7 8 9 10
Query Plan ---------------------------------------------- SELECT STATEMENT Cost = 9 SORT ORDER BY MERGE JOIN SORT JOIN TABLE ACCESS FULL TRANSACTION_JOIN FILTER SORT JOIN TABLE ACCESS FULL CALENDAR_JOIN
INDEX on the
END_DATE columns improves performance. The results again for all three are the same.
1 2 3 4 5 6 7 8
Query Plan ---------------------------------------------- SELECT STATEMENT Cost = 6 SORT ORDER BY TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN NESTED LOOPS TABLE ACCESS FULL TRANSACTION_JOIN INDEX RANGE SCAN DATE_RANGE
Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉
Comments are always welcome …
An interesting question came up today while discussing PL/SQL database triggers. Could you create a trigger on a
MERGE statement, like this:
1 2 3 4 5 6 7 8
CREATE OR REPLACE TRIGGER contact_merge_t1 BEFORE MERGE OF last_name ON contact_merge FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END contact_merge_t1; /
The answer is, no you can’t. It’ll raise an
ORA-04073 error if you attempt it, like this:
BEFORE MERGE OF last_name ON contact * ERROR at line 2: ORA-04073: COLUMN list NOT valid FOR this TRIGGER TYPE
The only supported DML events are
DELETE. The following DML trigger works against a
MERGE statement. After all a
MERGE statement is nothing more than an
1 2 3 4 5 6 7 8
CREATE OR REPLACE TRIGGER contact_merge_t1 BEFORE INSERT OR UPDATE OF last_name ON contact_merge FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END contact_merge_t1; /
Complete Code Sample ↓
Expand this section to see the sample working code.
This script creates a
CONTACT table, a row-level
MERGE statement, and query to display the 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
-- Conditionally drop the table. BEGIN FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'CONTACT_MERGE') LOOP EXECUTE IMMEDIATE 'DROP TABLE contact_merge'; END LOOP; END; / -- Create the table. CREATE TABLE contact_merge ( contact_id NUMBER , member_id NUMBER NOT NULL , contact_type NUMBER NOT NULL , first_name VARCHAR2(20) NOT NULL , middle_name VARCHAR2(20) , last_name VARCHAR2(20) NOT NULL , created_by NUMBER NOT NULL , creation_date DATE NOT NULL , last_updated_by NUMBER NOT NULL , last_update_date DATE , CONSTRAINT contact_merge_pk PRIMARY KEY(contact_id)); -- Create the trigger to enforce hyphenated last names.. CREATE OR REPLACE TRIGGER contact_merge_t1 BEFORE INSERT OR UPDATE OF last_name ON contact_merge FOR EACH ROW WHEN (REGEXP_LIKE(NEW.last_name,' ')) BEGIN :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1); END contact_merge_t1; / -- Merge statement that violates business rule. MERGE INTO contact_merge target USING ( SELECT 2001 AS contact_id , 1001 AS member_id , 1001 AS contact_type ,'Catherine' AS first_name ,'' AS middle_name ,'Zeta Jones' AS last_name , 2 AS created_by , SYSDATE AS creation_date , 2 AS last_updated_by , SYSDATE AS last_update_date FROM dual) SOURCE ON (target.contact_id = SOURCE.contact_id) WHEN MATCHED THEN UPDATE SET target.last_updated_by = 3 WHEN NOT MATCHED THEN INSERT VALUES ( SOURCE.contact_id , SOURCE.member_id , SOURCE.contact_type , SOURCE.first_name , SOURCE.middle_name , SOURCE.last_name , SOURCE.created_by , SOURCE.creation_date , SOURCE.last_updated_by , SOURCE.last_update_date ); -- Query results. SELECT first_name||DECODE(middle_name,NULL,' ',' '||middle_name||' ')||last_name AS full_name FROM contact_merge WHERE first_name = 'Catherine';
I’ve been working with Oracle so long, sometimes it’s frustrating when I find a syntax feature isn’t in another database. I ran into another example tonight. There isn’t a multiple column look up operator in MySQL. For example, you can do this in Oracle:
1 2 3 4 5
DELETE FROM common_lookup WHERE (common_lookup_table,common_lookup_column) IN (('TRANSACTION','PAYMENT_METHOD_TYPE') ,('TRANSACTION','TRANSACTION_TYPE') ,('RENTAL_ITEM','RENTAL_ITEM_TYPE'));
When I transformed it to comply with MySQL, it seems just as clean. In fact, with strings it’s simpler.
1 2 3
DELETE FROM common_lookup WHERE common_lookup_table IN ('TRANSACTION','RENTAL_ITEM') AND common_lookup_column IN ('TRANSACTION_TYPE','PAYMENT_METHOD_TYPE','RENTAL_ITEM_TYPE');
Then, I thought about it. Oracle would let me write a single subquery returning the two columns, whereas MySQL requires two subqueries in their syntax. Likewise, MySQL doesn’t support the
WITH clause, which would let me reference a single query result in the scope of the master query (Oracle and SQL Server do support that).
I guess we can hope that Oracle will implement the feature in MySQL now that they own it. 🙂 Let me know if I’ve missed some fabulous syntax alternative.
As I continue down the warren hole of Persistent Stored Modules (SQL/PSM) in MySQL, I keep wondering about that mad hare, Johnny Depp. Alice isn’t a programming language to teach me anything in this dream. Moreover, TIm Burton’s tale this seems oddly familiar, like a child’s story gone mad.
A quick update on comparative SQL expression assignments between PL/SQL and MySQL. When you want to filter a value through SQL functions before assigning it to another variable in MySQL, it’s not like PL/SQL. Just like the new Alice in Wonderland movie isn’t like the book.
The programmatic differences lies in their origins. PL/SQL evolved from Pascal through Ada to become a recursive language where you can call SQL from PL/SQL and PL/SQL from SQL. MySQL implemented PSMs from the ANSI SQL:2003 specification, which didn’t see it the same way, apparently (a disclaimer since I’ve not read the details of the specification).
Personally, I think PL/SQL is easier to write but I’ve been using it for almost 20 years. Naturally, there may be a consistency thread on this that I’m missing and an opportunity that I may exploit. After all, it is dark in this warren hole.
Oracle PL/SQL Assignments from SQL Expressions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
-- Enable output printing. SET SERVEROUTPUT ON SIZE 1000000 -- Define an anonymous block. DECLARE -- Declare a source variable. lv_right_operand VARCHAR2(10) := 'March'; -- Define a target variable for the assignment. lv_left_operand VARCHAR2(10); BEGIN -- Return the expression from a nested call parameter of the source variable. lv_left_operand := UPPER(SUBSTR(lv_right_operand,1,3)); -- Print it to console. dbms_output.put_line('Output ['||lv_left_operand||']'); END; /
Oracle also supports this syntax, which isn’t frequently used because it’s much more verbose syntactically. It is also equivalent to the PSM syntax adopted by MySQL.
-- Define an anonymous block. DECLARE -- Declare a source variable. lv_right_operand VARCHAR2(10) := 'March'; -- Define a target variable for the assignment. lv_left_operand VARCHAR2(10); BEGIN -- Return the expression from a nested call parameter of the source variable. SELECT UPPER(SUBSTR(lv_right_operand,1,3)) INTO lv_left_operand FROM dual; -- Print it to console. dbms_output.put_line('Output ['||lv_left_operand||']'); END; /
That means we can do it like the White Queen wants it or the Red Queen wants it in Oracle. Flexibility in PL/SQL is clearly broader because of the assignment options. Not so in MySQL, as you’ll see.
MySQL PSM Assignment from SQL Expressions
First, MySQL’s PSM approach doesn’t support anonymous blocks. The example must create a stored function or procedure, and then call it. A procedure seems like the best fit for the example.
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
-- Conditionally drop procedure. SELECT 'DROP PROCEDURE IF EXISTS assignit' AS "Statement"; DROP PROCEDURE IF EXISTS assignit; -- Create the proceudre SELECT 'CREATE PROCEDURE assignit' AS "Statement"; DELIMITER $$ -- Define the procedure. CREATE PROCEDURE assignit() BEGIN /* Declare a source variable. */ DECLARE lv_right_operand VARCHAR(10) DEFAULT 'March'; /* Define a target variable for the assignment. */ DECLARE lv_left_operand VARCHAR(3); /* Assign the modified value through the SELECT-INTO model. */ SELECT UCASE(SUBSTRING(lv_right_operand,1,3)) INTO lv_left_operand; /* Display assigned value. */ SELECT lv_left_operand; END; $$ DELIMITER ; -- Call the procedure. CALL assignit();
The only question here in the warren is: Who’s the White Queen; and who’s the Red Queen. Which semantic should I choose? My I hope is that I wake up before it’s … oops, off with his head. Actually, 3D or not, I’ll probably not see it, that’s the new Alice in Wonderland film.
Likewise, when my students wake up and read this they’ll know I was just answering a question on how to perform assignments in MySQL stored procedures. By the way, I’ve updated this assignment process in my Debugging MySQL Procedures post.
As an aside, I’ve got a new MySQL debugger that I’m testing later in the week. When I complete the test cases, I’ll post a review.
Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled. While I’d still argue it’s broken, it’s the only way to get support for advanced aggregation concepts.
The reason that I find that it broken is a matter of perspective not standards. The fact that you can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the
GROUP BY clause seemed like a broken behavior. The MySQL behavior is explained in Chapter 11.12.3 of the MySQL 5.1 Reference. What wasn’t clear from the documentation when I read it was why the behavior is allowed. Gary’s comment on the original post made me look more deeply into the behavior.
If you check the referenced documentation, the process is called
GROUP BY hidden columns. That label didn’t immediately resonate with me. That’s probably of my background with IBM’s DB2 and Oracle. I’m so accustom to how they work, that sometimes I think they set the standards but they don’t. It turns out that the
GROUP BY clause in the ANSI SQL standards doesn’t allow for expressions.
The default configuration of MySQL allows for the expressions (functions) in the
GROUP BY clause. The documentation refers to hidden columns, which are in point of fact expressions in the
GROUP BY clause. MySQL does support expressions in the
GROUP BY by default, and requires, like Oracle, that when you use an expression in the
SELECT clause that you mirror it in the
GROUP BY clause. This means you group on the result of the expression, not a column in the table or view.
In the default configuration, you have three options. They are determinate results, indeterminate results, and hidden column results. The following cover these components.
Determinate results are straight forward. They require that all non-aggregated columns in the
SELECT clause are mirrored in the
GROUP BY clause. This means that the non-aggregated column values are the key upon which results are aggregated.
An example of determinate results is:
1 2 3 4 5 6
SELECT key_one , key_two , SUM(counter) FROM GROUPING GROUP BY key_one , key_two;
Indeterminate results isn’t straight forward but isn’t hard to grasp. An indeterminate result set is returned when one or more non-aggregated columns in a
SELECT clause aren’t listed in the
GROUP BY clause. The columns listed in the
SELECT clause but excluded from the
GROUP BY clause return meaningless values because they’re column values chosen indeterminately from all pre-aggregated rows.
The following query runs in a generically configured MySQL instance without an error. It returns a meaningless
key_two column value from the pre-aggregated row set. In the example, the aggregation column counts the unique
key_one column values. This behavior makes
key_one a determinate value, and
key_two an indeterminate value.
1 2 3 4 5
SELECT key_one , key_two , SUM(counter) FROM GROUPING GROUP BY key_one;
You can fix this mixed return set by adding the
key_two column to the
GROUP BY clause, which would return a determinate set. Alternatively, you can prevent the default behavior for the
GROUP BY clause by adding the
ONLY_FULL_GROUP_BY mode variable to your
SQL_MODE system variable.
After setting the
SQL_MODE> variable, a
GROUP BY must contain all non-aggregated columns. When you make this change to the
SQL_MODE system variable, you also disable any queries that use an expression in their
GROUP BY clause. Those queries with expressions in the group by will now raise an
ERROR 1055 exception, like this:
ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY
If you want to prevent indeterminate results and don’t use expressions in the
GROUP BY clause, you can add the
ONLY_FULL_GROUP_BY mode to your
SQL_MODE system variable. You can do that during a session with the following syntax:
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
Or, you can start the
mysqld with the following option:
mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"
A better alternative, is to add it to the
my.cnf configuration file on Linux, or
my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.
# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
If you opt for changing it at the server configuration file, you must stop and restart the
mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is
mysql. If you’ve set the Windows service to
mysql51, then you need to substitute
Hidden Column Results
A hidden column result, is a result generated by an expression in the
GROUP BY clause. The following is a query that lets you sum transactions by the month name. This is supported by the default behavior of hidden columns. The following is an approach that you might find in Oracle because they don’t support aggregation by only part of the non-aggregated columns in a query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT il.MONTH AS "MON-YEAR" , il.base AS "BASE" , il.plus10 AS "10_PLUS" , il.plus20 AS "20_PLUS" FROM (SELECT CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) AS MONTH , MONTH(t.transaction_date) AS sortkey , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10 , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20 FROM TRANSACTION t WHERE EXTRACT(YEAR FROM t.transaction_date) = 2009 GROUP BY CONCAT(UPPER(SUBSTRING(MONTHNAME(t.transaction_date),1,3)),'-',EXTRACT(YEAR FROM t.transaction_date)) , MONTH(t.transaction_date)) il ORDER BY il.sortkey;
The interesting thing about MySQL’s approach is that the Oracle equivalent of an inline view is unnecessary. You can simply switch the functions like the exmaple below.
1 2 3 4 5 6 7 8 9 10
SELECT DATE_FORMAT(t.transaction_date,'%m-%Y') AS MONTH , MONTH(t.transaction_date) AS sortkey , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.0,2)),10,' ') AS base , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.1,2)),10,' ') AS plus10 , LPAD(CONCAT('$',FORMAT(SUM(t.transaction_amount) * 1.2,2)),10,' ') AS plus20 FROM TRANSACTION t WHERE EXTRACT(YEAR FROM t.transaction_date) = 2009 GROUP BY DATE_FORMAT(t.transaction_date,'%m-%Y') , MONTH(t.transaction_date) ORDER BY sortkey;
When you add the
ONLY_FULL_GROUP_BY mode to your
SQL_MODE system variable, you disallow this type of behavior and the query would no longer work. It would return the following error:
ERROR 1055 (42000): 'sampledb.t.transaction_date' isn't in GROUP BY
Complete Code Sample ↓
Expand this section to see the sample working code for indeterminate results.
This builds the
GROUPING table, inserts nine rows, and tests it before and after setting the variable in the session. The change reverts after you exit and re-enter the database.
-- Conditionally drop sample table. SELECT 'DROP TABLE IF EXISTS grouping' AS "Statement"; DROP TABLE IF EXISTS GROUPING; -- Create sample table. SELECT 'CREATE TABLE grouping' AS "Statement"; CREATE TABLE GROUPING ( key_one CHAR(5) , key_two CHAR(5) , counter INT UNSIGNED ); -- Insert nine rows into the sample table. SELECT 'INSERT INTO grouping' AS "Statement" INSERT INTO GROUPING VALUES ('One','Uno-1',1),('Two','Due-1',2),('Three','Tre-1',3) ('One','Uno-2',1),('Two','Due-2',2),('Three','Tre-2',3) ('One','Uno-3',1),('Two','Due-3',2),('Three','Tre-3',3); -- Query with hidden columns, no enforcement on non-aggregate columns. SELECT 'SELECT non-aggregates FROM grouping with hidden columns' AS "Statement"; SELECT key_one, key_two, SUM(counter) FROM GROUPING GROUP BY key_one; -- Check current system mode variable assignment. SELECT 'SELECT @@sql_mode' AS "Statement"; SELECT @@sql_mode; -- Append the mode to the existing system mode variable. SELECT 'SET SQL_MODE=(SELECT ...)' AS "Statement"; SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY')); -- Check modified system mode variable assignment. SELECT 'SELECT @@sql_mode' AS "Statement"; SELECT @@sql_mode;
A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.
Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.
Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.
The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a
COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.
The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.
Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.
- Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL
RECORD, Associative Array (collection), and the
REF CURSORare defined in this package specification.
-- Create a package to hold the PL/SQL record structure. CREATE OR REPLACE PACKAGE example IS -- Force cursors to be read as if empty every time. PRAGMA SERIALLY_REUSABLE; -- Package-level record structure that mimics SQL object type. TYPE common_lookup_record IS RECORD ( common_lookup_id NUMBER , common_lookup_type VARCHAR2(30) , common_lookup_meaning VARCHAR2(255)); -- Package-level collection that mimics SQL object table. TYPE common_lookup_record_table IS TABLE OF common_lookup_record INDEX BY PLS_INTEGER; -- Cursor structure to support a strongly-typed reference cursor. CURSOR c IS SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup; -- Package-level strongly-typed system reference cursor. TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE; END; /
- Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type –
SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.
CREATE OR REPLACE FUNCTION get_common_lookup_cursor ( TABLE_NAME VARCHAR2, column_name VARCHAR2) RETURN example.cursor_lookup IS -- Define a local variable of a strongly-typed reference cursor. lv_cursor EXAMPLE.CURSOR_LOOKUP; BEGIN -- Open the cursor from a static cursor OPEN lv_cursor FOR SELECT common_lookup_id , common_lookup_type , common_lookup_meaning FROM common_lookup WHERE common_lookup_table = TABLE_NAME AND common_lookup_column = column_name; -- Return the cursor handle. RETURN lv_cursor; END; /
- Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.
CREATE OR REPLACE FUNCTION convert_common_lookup_cursor ( pv_cursor EXAMPLE.CURSOR_LOOKUP) RETURN example.common_lookup_record_table IS -- Declare a local counter variable. counter INTEGER := 1; -- Local PL/SQL-only variable. out_record EXAMPLE.COMMON_LOOKUP_RECORD; out_table EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Grab the cursor wrapper and return values to a PL/SQL-only record collection. LOOP FETCH pv_cursor INTO out_record; EXIT WHEN pv_cursor%NOTFOUND; -- Assign it one row at a time to an associative array. out_table(counter) := out_record; -- Increment the counter. counter := counter + 1; END LOOP; -- Return the record collection. RETURN out_table; END; /
- You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.
-- Open the session to see output from PL/SQL blocks. SET SERVEROUTPUT ON DECLARE -- Define a local associative array. process_table EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Print title block. dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE'); dbms_output.put_line('---------------------------------------------------'); -- Run the dynamic variables through the cursor generating function and then convert it. process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE')); -- Read the content of the Associative array. FOR i IN 1..process_table.COUNT LOOP dbms_output.put('['||process_table(i).common_lookup_id||']'); dbms_output.put('['||process_table(i).common_lookup_type||']'); dbms_output.put_line('['||process_table(i).common_lookup_meaning||']'); END LOOP; END; /
I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.
You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.
CREATE OR REPLACE FUNCTION get_common_lookup_record_table ( TABLE_NAME VARCHAR2 , column_name VARCHAR2 ) RETURN example.common_lookup_record_table IS -- Declare a local counter variable. counter INTEGER := 1; -- Define a dynamic cursor that takes two formal parameters. CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS SELECT * FROM TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in))); -- A local PL/SQL-only collection variable. list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Grab the cursor wrapper and return values to a PL/SQL-only record collection. FOR i IN c(TABLE_NAME, column_name) LOOP list(counter) := i; counter := counter + 1; END LOOP; -- Return the record collection. RETURN list; END get_common_lookup_record_table; /
You can then test this in an anonymous block, like so:
-- Open the session to see output from PL/SQL blocks. SET SERVEROUTPUT ON DECLARE -- Declare a local PL/SQL-only collection and assign the value from the function call. list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE; BEGIN -- Print title block. dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection'); dbms_output.put_line('---------------------------------------------------'); -- Assign wrapped SQL collection to a PL/SQL-only collection. list := get_common_lookup_record_table('ITEM','ITEM_TYPE'); -- Call the record wrapper function. FOR i IN 1..list.COUNT LOOP dbms_output.put('['||list(i).common_lookup_id||']'); dbms_output.put('['||list(i).common_lookup_type||']'); dbms_output.put_line('['||list(i).common_lookup_meaning||']'); END LOOP; END; /
As always, I hope this helps somebody without paying a fee for content. 😉
My students are into the upload lab and they’ve encountered the wonderful
ORA-30926 error from a
MERGE statement. An example of the
MERGE for an upload is in this earlier blog post.
This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the
USING clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the
ON clause. The
ON clause is where the
MERGE statment matches the source query’s rows against the target table’s rows. The best join condition in a
MERGE statement is one between a surrogate primary and foreign key column.
Error: ORA-30926 Text: Unable TO GET a stable SET OF ROWS IN the SOURCE TABLES. -------------------------------------------------------------------- Cause: A stable SET OF ROWS could NOT be got because OF LARGE dml activity OR a non-deterministic WHERE clause. Action: Remove any non-deterministic WHERE clauses AND reissue the dml.
The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an
ORA-01422 error in Oracle.
As a rule, I’ve found that using the
DISTINCT operator in the source
SELECT statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your
WHERE clause. It also happens when the
SELECT clause returns date-time data types like
SYSDATE instead of date data types. A date-time can be converted by using the
TRUNCate function like this:
MERGE INTO TABLE_NAME target USING (SELECT ... , TRUNC(SYSDATE) AS creation_date FROM ...) SOURCE ON (target.primary_key_column = SOURCE.primary_key_column) WHEN MATCHED THEN UPDATE SET last_updated_by = SOURCE.last_updated_by , last_update_date = SOURCE.last_update_date WHEN NOT MATCHED THEN INSERT VALUES ( column_list_of_values );
Hope this helps my students and others … 😉