MacLochlainns Weblog

Gary Myers made a comment on the blog that got me thinking about how to look up user defined types (UDTs). Like those UDTs that you define to leverage pipelined functions and procedures. It became more interesting while considering how Oracle Object Types act as SQL record types. At least, that’s their default behavior when you don’t qualify a return type of self (that’s this in Oracle objects for those who write in any other object-oriented programming language).

The following query creates a view of data types in your user schema. It is fairly straightforward and written to let you deploy the view in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.

CREATE OR REPLACE VIEW schema_types AS
SELECT   ut.type_name AS type_name
,        uta.attr_no AS position_id
,        uta.attr_name AS attribute_name
,        DECODE(uta.attr_type_name
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,uta.attr_type_name
,          'BINARY_DOUBLE',uta.attr_type_name
,          'BLOB'         ,uta.attr_type_name
,          'CLOB'         ,uta.attr_type_name
,          'CHAR'         ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'DATE'         ,uta.attr_type_name
,          'FLOAT'        ,uta.attr_type_name
,          'LONG RAW'     ,uta.attr_type_name
,          'NCHAR'        ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NVARCHAR2'    ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,          uta.attr_type_name||'('||uta.length||')')
,          'NUMBER'       ,DECODE(NVL(uta.precision||uta.scale,0)
,          0,uta.attr_type_name
,        DECODE(NVL(uta.scale,0),0
,        uta.attr_type_name||'('||uta.precision||')'
,        uta.attr_type_name||'('||uta.precision||','|| uta.scale||')'))
,          'RAW'          ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR'      ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'VARCHAR2'     ,DECODE(NVL(uta.length,0),0,uta.attr_type_name
,        uta.attr_type_name||'('||uta.length||')')
,          'TIMESTAMP'     , uta.attr_type_name,uta.attr_type_name) AS attr_type_name
FROM     user_types ut, user_type_attrs uta
WHERE    ut.type_name = uta.type_name
ORDER BY ut.type_name, uta.attr_no;

You can query and format the view as follows:

CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
 
TTITLE OFF
 
SET ECHO ON
SET FEEDBACK OFF
SET NULL ''
SET PAGESIZE 999
SET PAUSE OFF
SET TERM ON
SET TIME OFF
SET TIMING OFF
SET VERIFY OFF
 
ACCEPT INPUT PROMPT "Enter type name: "
 
SET HEADING ON
TTITLE LEFT o1 SKIP 1 -
'--------------------------------------------------------' SKIP 1
 
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON c1 SKIP PAGE
 
COL c1 NEW_VALUE o1 NOPRINT
COL c2 FORMAT 999 HEADING "ID"
COL c3 FORMAT A32 HEADING "Attribute Name"
COL c4 FORMAT A33 HEADING "Attribute Type"
 
SELECT   st.type_name c1
,        st.position_id c2
,        st.attribute_name c3
,        st.attr_type_name c4
FROM     schema_types st
WHERE    st.type_name LIKE UPPER('&input')||'%'
ORDER BY st.type_name
,        st.position_id;

Here’s a sample output for an object type named common_lookup_object:

COMMON_LOOKUP_OBJECT
--------------------------------------------------------
  ID Attribute Name                   Attribute TYPE
---- -------------------------------- ------------------
   1 COMMON_LOOKUP_ID                 NUMBER
   2 COMMON_LOOKUP_TYPE               VARCHAR2(30)
   3 COMMON_LOOKUP_MEANING            VARCHAR2(255)

It certainly makes the point that a named data type is most convenient. I’m still working through the metadata to find how to link those meaningless type names back to meaningful package specifications. If you know, let me know in a comment. Hope this helps somebody.

You can’t beat play’n around with the technology. It seems that each time I experiment with something to answer a question, I discover new stuff. So, I really appreciate that Cindy Conlin asked me to net out why a PL/SQL Pipelined Table function existed at UTOUG Training Days 2009.

I found that Java and PHP have a great friend in Pipelined Table functions because when you wrap them, you can simplify your code. While a reference cursor lets you return the product of a bulk operation, it requires two hooks into the database. One for the session connection and another for the connection to the system reference cursor work area. While this was a marvelous feature of the OCI8 library, which I duly noted in my Oracle Database 10g Express Edition PHP Web Programming book, there’s a better way.

The better way is a Pipelined Table function because you can query it like you would a normal table or view. Well, not exactly but the difference involves the TABLE function, and it is really trivial.

When you call a Pipelined Table function, you only need to manage a single hook into the database. That hook is for the session connection. You can find a full (really quite detailed) treatment of Table and Pipelined Table functions in this blog page. Building on that blog page, here’s a simple PHP program that demonstrates the power of leveraging the SQL context provided by a Pipelined Table function.

<?php
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE'))");
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

You’ll notice that all the information that is expected from a query against a table or view is also available from the result of Pipelined Table function. That’s because the Pipeline Table function actually places the internal record structure of a PL/SQL collection into the SQL context along with the data.

This sample PHP program produces the following XHTML output:

Naturally, you can parameterize your PHP program and add bind variables to make this more dynamic. An example of parameterizing the call to a Pipelined Function is provided in the next program example.

You would use the following URL to call the dynamic PHP program:

http://mclaughlin11g/GetCommonLookup.php?table=ITEM&column=ITEM_TYPE

The working PHP program code is:

<?php
  // Declare input variables.
  (isset($_GET['table'])) ? $table = $_GET['table'] : $table = "ITEM";
  (isset($_GET['column'])) ? $column = $_GET['column'] : $column = 'ITEM_TYPE';
 
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    // Don't use table and column because they're undocumented reserved words in the OCI8.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table(:itable,:icolumn))");
 
    // Bind a variable into the resource statement.
    oci_bind_by_name($s,":itable",$table,-1,SQLT_CHR);
    oci_bind_by_name($s,":icolumn",$column,-1,SQLT_CHR);
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    print '</table>';
 
    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }
?>

You may note that the parameter values (placeholders or bind variables inside the SQL statement) are prefaced with an i. That’s because TABLE and COLUMN are restricted key words in the context of OCI8, and their use triggers an ORA-01036 exception.

This makes PHP more independent of the OCI8 library and easy to cross port to other databases if that’s a requirement. Hope this helps some folks.

When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. The question asked was: “What’s the primary purpose for pipelined table functions?”

My answer is: The primary purpose of a pipelined table function lets you retrieve PL/SQL record collection structures in a SQL context.

If there’s another reason that I missed, please let me know. It took a couple days to expand the older post to be more complete.

You can see the new one here …

Today, one of my students made a common error working in a PL/SQL programming unit. The stumped student called the tutor over, and then the tutor called me over. The tutor asked me how the DBMS_OUTPUT package could be out of scope. When I saw the SP2-0552 error, it was straightforward to remove a stray colon, but I realized that recognizing the colon as an error wasn’t straightforward.

The error picks the string that follows a colon inside a program unit. This behavior is the same regardless of whether the colon is found in the declaration, execution, or exception block. Most likely, the string is the beginning of the next line because the colon is most frequently a mistyped semicolon.

SP2-0552: Bind variable "DBMS_OUTPUT" NOT declared.

The easiest way to find it is not to look for the string but simply for a colon with the find feature of the editor. Then, you must either replace it or give it a valid bind variable name. You define bind variables in the SQL*Plus environment with the VARIABLE keyword.

You may also be interested in how to put a colon inside an NDS statement. If so, you can check this blog post.

I really like Alan Beaulieu’s Learning SQL because its simple, direct, and clearer than other books on SQL. While his focus is MySQL, he does a fair job of injecting a bit about Oracle’s syntax. Comparative concatenation syntax is one of topics I wished he’d spent more time on. Here’s some clarification on cross platform SQL concatenation.

Oracle

Oracle supports two forms of string concatenation. Concatenation for those new to the idea means gluing two strings into one, or three strings into one, et cetera. One uses the || operator, which looks like two pipes. You can use the || operator between any number of string elements to glue them together. A quick example of the || operator that returns an ABCD string is:

SELECT 'A' || 'B' || 'C' || 'D' FROM dual;

The Oracle database also supports the CONCAT operator that many use in MySQL. Those converting to an Oracle database should beware the difference between how the CONCAT function is implemented in Oracle versus MySQL. In an Oracle database, the CONCAT function only takes two arguments. When you call it with three or more arguments like this:

SELECT CONCAT('A','B','C','D') FROM dual;

It raises the following exception:

SELECT CONCAT('A','B','C','D') FROM dual
       *
ERROR at line 1:
ORA-00909: invalid NUMBER OF arguments

You can use the CONCAT function to process more than two arguments but you must do so by calling the function recursively. You’d do it like this if you must use it:

SELECT CONCAT('A',CONCAT('B',CONCAT('C','D'))) FROM dual;

As to an Oracle specific SQL book recommendation, I’d go with Alan’s as a beginner even though it’s focus is MySQL. By the way, if you don’t own Learning SQL hold off on buying it until the second edition is available in May 2009. If you’re using Oracle and have some basic SQL competence, I’d suggest Mastering Oracle SQL, 2nd Edition by Sanjay Mishra and Alan Beaulieu as a reference. Just make sure you get the 2^nd Edition of it too.

MySQL

MySQL appears to support the two same forms of string concatenation as an Oracle database. The one that uses the || operator (known as pipe concatenation), actually only returns a zero unless you configure the sql_mode to allow pipe concatenation.

The following concatenation statement uses pipe concatenation:

mysql> SELECT 'A'||'B'||'C'||'D';
+--------------------+
| 'A'||'B'||'C'||'D' |
+--------------------+
|                  0 |
+--------------------+
1 ROW IN SET, 4 warnings (0.00 sec)

By default, this fails and returns a zero unless you’ve added the PIPES_AS_CONCAT mode to your sql_mode variable. It returns a zero because it attempts to see whether either of the adjoining elements are true. Strings inherently fail to resolve as expressions or Boolean values and the function returns a zero, which means the composite expression was evaluated as false.

You can query the sql_mode variable as follows. The default values are shown in the results.

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

You can modify the sql_mode as follows from the command line:

SET sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT';

If you want to make this a permanent change, you can edit the my.ini file in Windows or the my.conf file in Unix or Linux. The following shows the modified line in a configuration file.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT"

With these changes pipe concatenation works in MySQL, as follows:

mysql> SELECT 'A'||'B'||'C'||'D';
+--------------------+
| 'A'||'B'||'C'||'D' |
+--------------------+
| ABCD               |
+--------------------+
1 ROW IN SET (0.02 sec)

You can use the CONCAT function to glue any number of string elements together when you’ve no control of the sql_mode variable. The CONCAT function in MySQL takes several arguments. I’ve never needed to use more than the limit and suspect that there isn’t one (based on the documentation). It appears to use a recursive algorithm for parameter processing. Please post a note correcting me if I’m wrong on this.

You call the CONCAT function like this:

SELECT CONCAT('A','B','C','D');

As to a MySQL specific SQL book recommendation, I’d go with Alan Beaulieu’s Learning SQL as a beginner. As noted earlier, don’t buy it until the 2^nd Edition ships in May 2009.

Microsoft® Access or SQL Server

Microsoft® SQL Server doesn’t support two forms of string concatenation like Oracle and MySQL. You can only use the + operator. There is no CONCAT function in Microsoft® Access or SQL Server. A quick example of the + operator in Microsoft’s SQL returns an ABCD string like this:

SELECT 'A' + 'B' + 'C' + 'D';

As to a Microsoft® T-SQL book recommendation, I’d go with Itzik Ben-Gan’s Microsoft SQL Server 2008 T-SQL Fundamentals. Just understand, that like most things Microsoft, T-SQL is a dialect and approach that differs substantially from other commercial products.

Somebody asked how to validate foreign key constraints in an Oracle database. The following query finds constraints, and displays the table and column that holds constraint with table and column name pointed to by the constraint. Since a foreign key to primary key relationship defines the list of values for a foreign key column, the values must be found in the primary key column.

Both user_constraints and user_cons_columns are catalog views that limit you to your own schema. The user_constraints view lets you find information about constraints, while the user_cons_columns view lets you see column level detail about the constraints.

The query lets you resolve where to look for those keys without manually inspecting table creation scripts.

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
ORDER BY ucc1.table_name
,        uc.constraint_name;

You generate the following output when you run this query. The results shows you: (a) constraint names with their corresponding table and column names; and (b) table and column names that holds the primary key which is referenced by foreign keys.

Constraint Name:                       References
Table.Column                           Table.Column
-------------------------------------- --------------------------------
FK_ADDRESS_1                           REFERENCES
(ADDRESS.CONTACT_ID)                   (CONTACT.CONTACT_ID)
 
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_ADDRESS_3                           REFERENCES
(ADDRESS.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_ADDRESS_4                           REFERENCES
(ADDRESS.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_1                     REFERENCES
(COMMON_LOOKUP.CREATED_BY)             (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_2                     REFERENCES
(COMMON_LOOKUP.LAST_UPDATED_BY)        (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_1                           REFERENCES
(CONTACT.MEMBER_ID)                    (MEMBER.MEMBER_ID)
 
FK_CONTACT_2                           REFERENCES
(CONTACT.CONTACT_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_CONTACT_3                           REFERENCES
(CONTACT.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_4                           REFERENCES
(CONTACT.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)

You can then query the table and column referenced by the foreign key to determine the valid list of primary keys in the table. Extending the basic query design, you can narrow it to a specific constraint. This becomes very useful when you try to insert a row into an address table with an foreign key value that isn’t found in the list of valid primary keys.

A sample INSERT statement would be:

INSERT INTO address
VALUES
( 1101
, 1008
, 2001                                  -- This foreign key isn't a valid primary key.
,'Nowhereville'
,'Beatledom'
,'11111-1111'
, 3
, SYSDATE
, 3
, SYSDATE );

In my test instance, you would get a constraint violation error like the one below. You can download the setup scripts from McGraw-Hill’s web site for Oracle Database 11g PL/SQL Programming book that I wrote.

INSERT INTO address
*
ERROR at line 1:
ORA-02291: integrity CONSTRAINT (STUDENT.FK_ADDRESS_2) violated - parent KEY NOT found

Then, you can modify the earlier query to find the offending primary key column. Its offense is that there is no equivalent value to what you tried to input into another table. Here’s how you find the primary key column table:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('&input_constraint_name');

It returns the following in my test instance:

CONSTRAINT Name:                       REFERENCES:
 TABLE.Column                           TABLE.Column
-------------------------------------- --------------------------------------
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You can now verify whether the value, 2001, that you tried to insert into a foreign key column exists. The query would be like follows:

SELECT   common_lookup_id
FROM     common_lookup
WHERE    common_lookup_id = 2001;

The query will say that no rows were found. You can remove the WHERE clause to find the list of valid primary key values.

I played around with a friend’s Kindle and really had to wonder why would people buy one of them. Since my books sell an electronic edition on Kindle, I hoped for a better solution.

Great news today, a better physical technology has arrived. You can now download a Kindle application for your iPhone! As a big iPhone fan, this is awesome. Naturally, I was curious how my last book looked.

I downloaded the sample chapter and it looked great on the iPhone. The images are well rendered and clear on the screen. I’m probably going to have to buy an e-copy (ouch) because they don’t provide authors with electronic copies.

Thanks a bunch to the team who ported it to the iPhone. Great job!

Somebody suggested that I post a simple quick view of how a basic query works. The following illustrates how the FROM is read first. Aliases assigned in the FROM clause, like the i, replace the full name of the table or view. Column references don’t require prepending with the table or view alias but doing so adds clarity in the query. You do need prepend table aliases or names when two or more columns returned by the query have the same names. This happens when you’re joining two or more tables because the SQL parser finds that they’re ambiguously defined otherwise.

While the column aliases use the optional AS keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH statement – blogged on it here.

Selection comes in two phases, identifying the sources or tables in the FROM clause, and then filtering the sources based on comparisons in the WHERE clause. Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM clause as part of a ON or USING subclause when queries use key words like JOIN et cetera. You find join statements in the WHERE clause when the queries list tables as comma separated elements in a FROM clause.

Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT clause.

While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.

Aggregation typically happens after selection but before projection. While columns in the SELECT clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY clause.

Sorting by a column is done through the ORDER BY clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY clause.

Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.

The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.

Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a weakly typed system reference cursor.
  item_cursor   SYS_REFCURSOR;
 
  -- Define a target variable for the query result.
  item_type_out VARCHAR2(30);
 
  -- Create NDS statement, with a bind or placeholder variable.
  stmt          VARCHAR2(2000) := 'SELECT type_name '
                               || 'FROM   item_type '
                               || 'WHERE  UPPER(type_name) = UPPER(:type_name_in)';
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  OPEN item_cursor FOR stmt USING item_type_in;
 
  -- Fetch the first row return and return the value.
  FETCH item_cursor INTO item_type_out;
 
  -- CLose the cursor.
  CLOSE item_cursor;
 
  -- Return the value.
  RETURN item_type_out;
END;
/

This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a dynamic cursor.
  CURSOR c (item_type_name VARCHAR2) IS
    SELECT type_name
    FROM   item_type
    WHERE  UPPER(type_name) = UPPER(item_type_name);
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  FOR i IN c(item_type_in) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

An even more primitive approach relies on implicit assignment, like the following:

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  -- Open the cursor and rely on implicit assignment within the cursor.
  FOR i IN (SELECT type_name
            FROM   item_type
            WHERE  UPPER(type_name) = UPPER(item_type_in)) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) 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 (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

I went back and edited that post about MySQL nuances not in Oracle from last week about the MySQL ENUM data type. You can find a framework suggestion that lets you not have to change existing string conditioning application code during a migration from MySQL to Oracle.

You’ll see this in the middle of the page, just click it to unfold the details. Yes, JQuery has arrived on my blog.

As with everything else on this blog, let me know if you see an opportunity for improvement.