MacLochlainns Weblog

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.

1. Create an external file, like this character table.

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;

2. 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));
/

3. Create a user-defined collection of your object type, like

CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/

4. 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;
/

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

I saw a post in the OTN forum that asked a simple question and had no simple example as an answer, so I thought it would be nice to provide one. Basically, somebody wanted to know how to call into a stored procedure and return a value with more or less one pass-by-value and another pass-by-reference variable.

This defines a simple echo procedure, which takes a message and returns a formatted message:

1
2
3
4
5
6
7

CREATE OR REPLACE PROCEDURE echo
( message IN     VARCHAR2
, reply      OUT VARCHAR2 ) IS
BEGIN
  reply := 'Message ['||message||'] received.';
END;
/

The following PHP calls the procedure and returns the value. It uses the required connection syntax for the Zend Server Community Edition.

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

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/xe");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN echo(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you need to size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

You can then test it with or without a parameter, like this example with a parameter:

http://localhost/message_reply.php?msg="Sample message"

I put a link in the forum to this, and I hope it helps a few folks.

I finally got around to summarizing how to use PHP to store, retrieve, and display CLOBs and BLOBs from an Oracle database. I think too often we default to BFILEs. I put all the code in zip files with instructions and suggestions for locations. This is really the second entry that goes with configuring Zend Server Community Edition or the DEPRECATED Zend Core for Oracle.

If you’re new to PHP, check out the Underground PHP and Oracle book from Christopher Jones and Alison Holloway. It’s FREE!

The Oracle LOB Processing entry is in this blog page. I know it means another click, but I’ll probably add and modify it over time. If you’ve got time and interest, take a look and let me know what you think and what improvements you’d like to see. Thanks.

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.

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;
/

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;
/

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;
/

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.

Somebody felt that I’d short changed Oracle by providing only an example for calling a stored procedure in MySQL. So, here’s an equivalent post to the MySQL sample that works in an Oracle database with PHP.

The largest difference between the two approaches is that Oracle is transactional by default while MySQL isn’t. However, the use of savepoints and rollbacks are shown in the procedure, which are the appropriate transaction controls in Oracle.

Here are the detailed steps, even though there are other blog entries with information on related subjects.

1. Sign on as the system user, and create a new user. Users in Oracle have their own schema or work area, and they don’t require a database like MySQL or SQL Server.

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CONNECT, resource, CREATE any VIEW TO student;

2. Create a create_oracle_procedure.sql file with the following contents:

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

-- Conditionally drop the objects to make this rerunnable.
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name IN ('A','B')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('A_SEQ','B_SEQ')) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/  
 
-- Create the tables and sequences.
CREATE TABLE a
( a_id   NUMBER CONSTRAINT a_pk PRIMARY KEY
, a_text VARCHAR2(12));
 
CREATE SEQUENCE a_seq;
 
CREATE TABLE b
( b_id   NUMBER CONSTRAINT b_pk PRIMARY KEY
, a_id   NUMBER 
, b_text CHAR(12)
, CONSTRAINT fk_a FOREIGN KEY(a_id) REFERENCES a (a_id));
 
CREATE SEQUENCE b_seq;
 
-- Create a stored procedure with IN-only (default) mode parameters.
CREATE OR REPLACE PROCEDURE double_insert
( input_a VARCHAR2, input_b VARCHAR2) IS
BEGIN
  SAVEPOINT starting_point;
  INSERT INTO a VALUES (a_seq.NEXTVAL, input_a);
  INSERT INTO b VALUES (b_seq.NEXTVAL, a_seq.CURRVAL, input_b);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/
 
-- Define a couple local session variables.
VARIABLE text1 VARCHAR2(12)
VARIABLE text2 VARCHAR2(12)
 
-- Assign values to session variables.
BEGIN
  :text1 := 'This is one.';
  :text2 := 'This is two.';
END;
/
 
-- Call the local procedure.
EXECUTE double_insert(:text1,:text2);
 
-- Select the data set. 
SELECT * FROM a;
SELECT * FROM b;

3. Quit the session as the system user. You can simply reconnect to the new schema like this:

SQL> CONNECT student/student@orcl

4. Run the file from the relative directory where you started the sqlplus executable.

SQL> @create_oracle_procedure.sql

You see the following query results at the end of the script:

      A_ID A_TEXT
---------- ------------
         1 This IS one.
 
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.

5. Write the following into a call_oracle_procedure.php:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Declare two variables for the test procedure call.
    $val1 = "Hello Hal!";
    $val2 = "Hello Dave!";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN double_insert(:a,:b); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement.
    oci_bind_by_name($stmt, ":a", $val1);
    oci_bind_by_name($stmt, ":b", $val2);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print "Congrats! You've executed a Oracle stored procedure from PHP!";
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

6. Run the call_oracle_procedure.php from the command line, like this:

php call_oracle_procedure.php

7. Now you can requery the database to see the inserts made by the PHP program.

SQL> SELECT * FROM a;
 
      A_ID A_TEXT
---------- ------------
         1 This IS one.
         2 Hello Hal!
 
SQL> SELECT * FROM b;
 
      B_ID       A_ID B_TEXT
---------- ---------- ------------
         1          1 This IS two.
         2          2 Hello Dave!

There’s now a sample file for both approaches. As always, I hope this helps some folks.

An interesting thing happened today, as I was explaining how you call functions with embedded DML statements. The students were stunned at seeing an ORA-06576 error for a function that they knew existed. It’s one of those imperfect error messages …

Basically, they wrote a wrapper function to a parallel enabled function, and then they tried to call it into a session level bind variable, like this:

SQL> VARIABLE verified NUMBER
SQL> CALL update_contact INTO :verified;

It failed with the following message:

CALL update_contact INTO :verified
     *
ERROR at line 1:
ORA-06576: NOT a valid FUNCTION OR PROCEDURE name

They were stunned but I was mum. I suggested that they test the call in an anonymous block program. Here’s the PL/SQL call example:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> BEGIN
  2    IF update_contact = 1 THEN
  3      DBMS_OUTPUT.put_line('Success!');
  4    ELSE
  5      DBMS_OUTPUT.put_line('Failure!');
  6    END IF;
  7  END;
  8  /

It returns

Success!

I removed the puzzled look by explaining that while you don’t need to provide the open and close parentheses inside PL/SQL, you do generally require them in the context of a CALL statement. They’re not required for stored functions in SQL statements, but they’re required for stored instantiable object types.

Here’s the correct way call the program:

SQL> CALL update_contact() INTO :verified;
SQL> SELECT DECODE(:verified,1,'Success!','Failure!') AS answer FROM dual;

It prints this to console:

ANSWER
--------
Success!

The natural question is why don’t you just run the function as part of a query. It’s a great question because it lets me demonstrate another principle. The principle that you can’t can’t perform a DML in a query, which is abstract until you see it up front and personal.

SQL> SELECT update_contact() FROM dual;

this query raises the following exception:

SELECT update_contact() FROM dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "PLSQL.UPDATE_CONTACT", line 4

Setup Code Supplement

You can find the base code for this example here. It comes from the Oracle Database 11g PL/SQL Programming book. After you run the create_store.sql script, you’ll need to run the following:

-- Add a null allowed column for derived data.
ALTER TABLE contact ADD (full_name VARCHAR2(44));
 
-- Define a function concatenate strings.
CREATE OR REPLACE FUNCTION MERGE
( last_name     VARCHAR2
, first_name    VARCHAR2
, middle_initial VARCHAR2 )
RETURN VARCHAR2 PARALLEL_ENABLE IS
BEGIN
  RETURN last_name ||', '||first_name||' '||middle_initial;
END;
/
 
-- Define a wrapper function around the merge function.
CREATE OR REPLACE FUNCTION update_contact RETURN NUMBER IS
  ret_val NUMBER := 0; -- The default return value to false or zero.
BEGIN
  UPDATE contact c1
  SET    c1.full_name = (SELECT MERGE(c2.last_name
                                     ,c2.first_name
                                     ,c2.middle_initial)
                         FROM   contact c2
                         WHERE  c1.rowid = c2.rowid);
  IF SQL%ROWCOUNT > 0 THEN
    ret_val := 1; -- This is only reached when 1 or more rows are updated.
  END IF;
  RETURN ret_val;
END update_contact;
/

I saw an interesting post on INTERVAL YEAR TO MONTH while checking things out today. It struck me as odd, so I thought I’d share a similar sample along with my opinion about how it should be done in a PL/SQL block.

The example is a modification of what I found in a forum. You should see immediately that it’s a bit complex and doesn’t really describe what you should do with any months. Naturally, the example only dealt with years.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-APR-1975';
BEGIN
  lv_interval := TO_CHAR(FLOOR((lv_end_day - lv_start_day)/365.25))||'-00';
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

I suggest that the better way is the following because it allows for months, which are a bit irregular when it comes to divisors.

DECLARE
  lv_interval  INTERVAL YEAR TO MONTH;
  lv_end_day   DATE := '30-APR-2009';
  lv_start_day DATE := '30-JAN-1976';
BEGIN
  lv_interval := TO_CHAR(EXTRACT(YEAR FROM lv_end_day) -
                            EXTRACT(YEAR FROM lv_start_day)) ||'-'||
                   TO_CHAR(EXTRACT(MONTH FROM lv_end_day) -
                             EXTRACT(MONTH FROM lv_start_day));
  DBMS_OUTPUT.put_line(lv_interval);
END;
/

Let me know if you’ve another alternative that you prefer.

Some folks asked me to post Oracle Database 10g Express Edition step-by-step installation and how to setup an initial user/schema. There’s not much difference between the Windows and Linux installation but this one is the Windows XP installation.

Here are the eight steps required to run the MSI program:

1. Launch the MSI icon from your Windows Explorer:

2. Click the run button to extracing the zip file:

3. Click the Next button to run the Oracle Database 10g Expression Edition install wizard:

4. Accept the license agreement and click the Next button:

5. You can change the default installation base folder by clicking the Browse button, but why bother. Click the Next button to proceed.

6. This is the screen where you enter the privileged user account for the Oracle database. Make sure you’ll remember the password you enter. When you’ve entered it twice, click the Next button to continue the installation.

7. The Summary dialog shows you the ports for the installation. If you want to access this machine externally from the local host, you should open these ports for TCP transactions in your firewall. Click the Install button to proceed.

8. The last screen tells you that the InstallShield Wizard is complete. Make sure the Launch the Database homepage check box checked and click the Finish button to complete the installation.

After you’ve installed the database, you should immediately create a schema where you’ll work. The next steps show you how to create your own user/schema in the Oracle Database 10g XE instance.

1. The Database homepage provides you with a web login to the database. The only configured user at this point is the privileged user, which is known as the system user. The password to this account is the same as the one you entered during the product installation.

2. After you sign on to the database, you’ll be on the following console page. Click the Administration button on the far left.

3. The middle Database Users button in the web page allows you to create new database users. Click the Database Users image to proceed.

4. The Manage Database Users console lets you create or manage existing users. Click the Create button to create a new user.

5. The Create Database User console lets you enter a user name and password plus lets you grant necessary permissions to the new user. The entries on this form create a STUDENT user/schema with superuser responsibilities. The DBA role grants those superuser privileges. You click the Create button to setup a new user.

6. After you create the user, you’ll see the following screen. You can now click the Logout link to exit the application. Then, you can logon again as the STUDENT user.

I hope this meets the needs of new users.

Playing around with Microsoft SQL Server 2008 Express edition, I’ve sorted through a bunch of tidbits. One that I thought was interesting, is how to perform a recursive or hierarchical query. This describes how you can perform the magic.

The official name of the WITH clause in Oracle’s lexicon (otherwise known as Oraclese) is a subquery factoring clause. You can find more on that in this earlier blog post. Microsoft has a different name for the WITH clause. They call it a Common Table Expression or CTE.

You perform recursive queries in Microsoft SQL Server 2008 by leveraging CTEs. I’ve modified the setup code from that earlier blog post to run in SQL Server 2008. You’ll find it at the bottom of this blog post.

Unless you want to write your own C# (.NET is the politically correct lingo) equivalent to Oracle’s SQL*Plus, you’ll need to run this script in the SQL Server Management Studio. Actually, you can use Microsoft SQL Server 2008’s command-line utility, which is called sqlcmd.exe but it is much less robust than SQL*Plus. In the Management Studio, you click File, then Open, and File… to load the file for execution, and then click the Execute button. You need to be careful you don’t click the Debug button, which is the green arrow to the right of the Execute button.

This is the magic query in the illustration. You can also find it in the source code. At the end of the day, I’m hard pressed to understand why they’d use a UNION ALL to support recursion.

The top-most CTE, or subquery factoring clause, simply joins the ORGANIZATION_NAME to the ORG_PARENT_ID and ORG_CHILD_ID columns to provide a single working source. The second CTE performs the recursion. The top-query sets the starting row, and the second query recursively navigates the tree. After all children are found, the first query moves to the next element in the table and recursively searches for its children.

You should note that the CTE self-references itself from inside the second query. Then, the external query (the non-CTE query) returns the results by querying the same CTE.

This logic behaves more like a nested loop, and actually fails to move down branches of the tree like a recursive program. Otherwise line 19 would be line 14 in the output. You could write another CTE to fix this shortfall, thereby mirroring a true recursive behavior, or you can write a stored procedure.

The illustrated query outputs the following hierarchical relationship, which navigates down the hierarchical tree:

You can also go up any branch of the tree by changing some of the logic. You’ll find the query to navigate up the tree as the second query in the setup script at the end of the blog. It renders the following output:

The blog will be updated if I discover the equivalent to the LEVEL in Oracle’s self-referencing semantics. If you know it, please share it with everybody.

Setup Script

Microsoft SQL Server 2008 Join Script

USE student;
 
BEGIN TRAN;
 
-- Conditionally drop tables when they exist.
IF OBJECT_ID('dbo.ORGANIZATION','U') IS NOT NULL DROP TABLE dbo.ORGANIZATION;
IF OBJECT_ID('dbo.ORG_STRUCTURE','U') IS NOT NULL DROP TABLE dbo.ORG_STRUCTURE;
 
-- Create the organization table. 
CREATE TABLE ORGANIZATION
( organization_id INT
, organization_name VARCHAR(10));
 
-- Seed the organizations. 
INSERT INTO dbo.ORGANIZATION VALUES
 (1,'One'), (2,'Two'), (3,'Three'), (4,'Four'), (5,'Five')
,(6,'Six'), (7,'Seven'), (8,'Eight'), (9,'Nine'), (10,'Ten')
,(11,'Eleven'), (12,'Twelve'), (13,'Thirteen'), (14,'Fourteen'), (15,'Fifteen')
,(16,'Sixteen'), (17,'Seventeen'), (18,'Eighteen'), (19,'Nineteen'), (20,'Twenty');
 
-- Create the organization structure table that holds the recursive key. 
CREATE TABLE org_structure
( org_structure_id INT
, org_parent_id INT
, org_child_id INT );
 
-- Seed the organization structures. 
INSERT INTO org_structure VALUES
 ( 1, 0, 1),( 1, 1, 2),( 1, 1, 3),( 1, 1, 4),( 1, 2, 5)
,( 1, 2, 6),( 1, 3, 7),( 1, 3, 8),( 1, 4, 9),( 1, 4,10)
,( 1, 5,11),( 1, 5,12),( 1, 6,13),( 1, 6,14),( 1, 7,15)
,( 1, 8,16),( 1, 8,17),( 1, 9,18),( 1, 9,19),( 1,14,20);
 
COMMIT TRAN;
 
-- Navigating down the tree from the root node.
WITH org_name AS
 (SELECT   os.org_parent_id AS org_parent_id
  ,        o1.organization_name AS org_parent_name
  ,        os.org_child_id AS org_child_id
  ,        o2.organization_name AS org_child_name
  FROM     dbo.organization o1 RIGHT JOIN dbo.org_structure os 
  ON       o1.organization_id = os.org_parent_id RIGHT JOIN dbo.organization o2
  ON       o2.organization_id = os.org_child_id)
, jn AS
 (SELECT   org_parent_id, org_parent_name
  ,        org_child_id, org_child_name
  FROM     org_name
  WHERE    org_parent_id = 1  
  UNION ALL
  SELECT   c.org_parent_id, c.org_parent_name
  ,        c.org_child_id, c.org_child_name
  FROM     jn AS p JOIN org_name AS c
  ON       c.org_parent_id = p.org_child_id)
SELECT   jn.org_parent_id, jn.org_parent_name
,        jn.org_child_id, jn.org_child_name
FROM     jn
ORDER BY 1;
 
-- Navigating up the tree from the 20th leaf-node child.
WITH org_name AS
 (SELECT   os.org_parent_id AS org_parent_id
  ,        o1.organization_name AS org_parent_name
  ,        os.org_child_id AS org_child_id
  ,        o2.organization_name AS org_child_name
  FROM     dbo.organization o1 RIGHT JOIN dbo.org_structure os 
  ON       o1.organization_id = os.org_parent_id RIGHT JOIN dbo.organization o2
  ON       o2.organization_id = os.org_child_id)
, jn AS
 (SELECT   org_parent_id, org_parent_name
  ,        org_child_id, org_child_name
  FROM     org_name
  WHERE    org_child_id = 20  
  UNION ALL
  SELECT   c.org_parent_id, c.org_parent_name
  ,        c.org_child_id, c.org_child_name
  FROM     jn AS p JOIN org_name AS c
  ON       c.org_child_id = p.org_parent_id)
SELECT   jn.org_parent_id, jn.org_parent_name
,        jn.org_child_id, jn.org_child_name
FROM     jn
ORDER BY 1 DESC;

It must have been disgust with learning that a Result Cache function couldn’t use an object type that made me zone on showing how to use an object type as the return type of a PL/SQL table function. The nice thing about this approach, as pointed out by Gary Myer’s comment on another blog post, is that it doesn’t require a pipelined function to translate it from PL/SQL to SQL scope.

The first step is to create an object type without a return type of SELF, which Oracle elected as its equivalent to this for some unknown reason. A user-defined type (UDT) defined without a return type, returns the record structure of the object, but as mentioned it is disallowed in result cache functions. After you define the base type, you create a collection of the base UDT. Then, you can use the UDT as a SQL return type in your code, like this:

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

CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup
    WHERE    common_lookup_table = UPPER(table_name_in)
    AND      common_lookup_column = UPPER(table_column_name_in);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/

You can then query it in SQL like this:

COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));

This depends on the same sample code that I use elsewhere on the blog. You can download it from McGraw-Hill’s web site. You can also find a complete and re-runnable script by clicking on the down arrow below.

BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name = 'GET_COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT_TABLE') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
  FOR i IN (SELECT   type_name
            FROM     user_types
            WHERE    type_name = 'COMMON_LOOKUP_OBJECT') LOOP
    EXECUTE IMMEDIATE 'DROP TYPE '||i.type_name;
  END LOOP;
END;
/
 
CREATE OR REPLACE TYPE common_lookup_object IS OBJECT
( common_lookup_id      NUMBER
, common_lookup_type    VARCHAR2(30)
, common_lookup_meaning VARCHAR2(255));
/
 
CREATE OR REPLACE TYPE common_lookup_object_table IS TABLE OF common_lookup_object;
/
 
CREATE OR REPLACE FUNCTION get_common_lookup_object_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN common_lookup_object_table IS
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup
    WHERE    common_lookup_table = UPPER(table_name_in)
    AND      common_lookup_column = UPPER(table_column_name_in);
 
  -- Declare a counter variable.
  counter INTEGER := 1;
 
  -- Declare a package collection data type as a SQL scope table return type.
  list COMMON_LOOKUP_OBJECT_TABLE := common_lookup_object_table();
 
BEGIN
 
  -- Assign the cursor return values to a record collection.
  FOR i IN c(table_name, column_name) LOOP
    list.extend;
    list(counter) := common_lookup_object(i.common_lookup_id
                                         ,i.common_lookup_type
                                         ,i.common_lookup_meaning);
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_object_table;
/
 
TTITLE OFF
 
COLUMN common_lookup_id      FORMAT 9999 HEADING "ID"
COLUMN common_lookup_type    FORMAT A16  HEADING "Lookup Type"
COLUMN common_lookup_meaning FORMAT A30  HEADING "Lookup Meaning"
 
SELECT   *
FROM     TABLE(get_common_lookup_object_table('ITEM','ITEM_TYPE'));