Skip to main content

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective of maintaibilty and readability of code.

Here's an example:
DECLARE
   SUBTYPE currency_t is NUMBER (10,2);
   l_salary currency_t;
BEGIN
   l_salary := 10.50607;
   dbms_output.put_line (l_salary);
END;

10.51
You can, however, certainly define your own composite types. The phrase "user-defined types" often refers to object-oriented structures, but as you will see below, you the user can define your own types of:
  • Records (kinda like a row in a table, though usually consisting of a subset of the columns of a row)
  • Collections (similar to arrays in other programming languages)
  • Object types (comparable to classes in object-oriented languages)

User-Defined Record Types

You can very easily define records based on existing structures, such as a table or cursor. Sometimes, though, you will want to define your own record type to group together related elements or return multiple pieces of information from a function.

You can do this by defining your own record. Here's an example of a record type used as the return type of a function:
CREATE OR REPLACE PACKAGE comp_pkg
   AUTHID DEFINER
IS
   TYPE compensation_rt IS RECORD
   (
      salcomp        employees.salary%TYPE,
      total_salary   NUMBER
   );

   FUNCTION comp_info (emp_id_in IN employees.employee_id%TYPE)
      RETURN compensation_rt;
END;
/

CREATE OR REPLACE PACKAGE BODY comp_pkg
IS
   FUNCTION comp_info (emp_id_in IN employees.employee_id%TYPE)
      RETURN compensation_rt
   IS
      l_comp   compensation_rt;
   BEGIN
      SELECT salary, salary + NVL (commission_pct, 0)
        INTO l_comp
        FROM employees
       WHERE employee_id = emp_id_in;
      
      RETURN l_comp;
   END;
END;
/

DECLARE
   l_comp   comp_pkg.compensation_rt;
BEGIN
   l_comp := comp_pkg.comp_info (101);
   DBMS_OUTPUT.put_line ('Total salary = ' || l_comp.total_salary);
END;
/
[Thanks to Gavin Zzw for several suggestions to improve the code above]

And here's an example of nesting one record type inside another. Normalizing my runtime data structures!
DECLARE
   TYPE phone_rectype IS RECORD
   (
      area_code    PLS_INTEGER,
      exchange     PLS_INTEGER,
      phn_number   PLS_INTEGER,
      extension    PLS_INTEGER
   );

   TYPE contact_rectype IS RECORD
   (
      day_phone#    phone_rectype,
      eve_phone#    phone_rectype,
      cell_phone#   phone_rectype
   );

   l_sales_rep   contact_rectype;
BEGIN
   /* Set the day phone # */
   l_sales_rep.day_phone#.area_code := 773;
   l_sales_rep.day_phone#.exchange := 426;
   l_sales_rep.day_phone#.phn_number := 9093;
   l_sales_rep.day_phone#.extension := NULL;

   /* Copy day phone to evening phone */
   l_sales_rep.eve_phone# := l_sales_rep.day_phone#;

   /* "Override" just phn_number field. */
   l_sales_rep.eve_phone#.phn_number := 2056;
END;
Since we can define records based on a table, view or cursor using the %ROWTYPE attribute, user-defined records do not proliferate in PL/SQL code.

When it comes to collections, though, we almost always declare our own types.

User-Defined Collection Types

Collections are the analogue of arrays in PL/SQL. There are three types of collections: associative arrays, nested tables, and arrays.

Collections come in very handy when you need temporary datasets in your program and do not want or need to rely on global temporary tables, SQL and the context switches between the PL/SQL and SQL engines. Collections are also the enabling technology for table functions, which are functions that are invoked in the FROM clause of a SELECT statement.

You can certainly take advantage of a variety of pre-defined collection types. In the following code fragment, for example, I grab the topics and difficulty levels of a set of Oracle Dev Gym workouts:
   l_topics         DBMS_SQL.number_table;
   l_difficulties   DBMS_SQL.number_table;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;
The IDs are all integers, so why not use the DBMS_SQL collection? Actually, I suggest you do not do this. That's right. Don't do what I do. Do what I say. Why?

Because this is an example of "bad lazy." When someone comes along later to maintain my code, they will see my use of a DBMS_SQL element and wonder: "Where's the dynamic SQL?"

Our code should answer, not raise, questions. So what I should do is have my own table of numbers and use that. Funnily enough, I do. So I am going to fix my code right now to look this like:
   l_topics         qdb_numbers_nt;
   l_difficulties   qdb_numbers_nt;
BEGIN
     SELECT topic_id, difficulty_id
       BULK COLLECT INTO l_topics, l_difficulties
       FROM dg_workouts
      WHERE goal_id = goal_id_in
   ORDER BY week_number;
Ah....much better!

User-defined record types and collection types are often used together.

For example, in the code underlying the Oracle Dev Gym, the qdb_rankings package defines both a record type and collection type of those records:
/* The record type */

TYPE user_info_rt IS RECORD
(
   comp_event_id   INTEGER,
   total_seconds   INTEGER
);

/* Collection type; each element contains one of those records */
TYPE user_info_tt IS TABLE OF user_info_rt
   INDEX BY PLS_INTEGER;

/* A variable of that collection type */

l_user_timings  user_info_tt;

....

/* Now I populate the collection of records with a BULK COLLECT query */

SELECT comp_event_id, total_seconds
  BULK COLLECT INTO l_user_timings
  FROM mv_qdb_compev_answers eva
 WHERE     eva.competition_id = competition_id_in
       AND TRUNC (eva.start_date) BETWEEN start_in AND end_in
       AND eva.user_id = rec.user_id
       AND eva.pct_correct_answers = 100;
Collections are a lot of fun and very handy. Check out my Practically Perfect PL/SQL YouTube channel for hours of instruction on collections. And the PL/SQL doc offers extensive guidance on collections, as well.

User-Defined Object Types

Way back in Oracle8, object-oriented capabilities were added to Oracle Database, implemented in PL/SQL syntax. Instead of classes, they are called object types. And now, a number of big releases later, these object types support most of the features developers have come to expect from object-oriented languages including inheritance and dynamic polymorphism.

Here, for example, is a hierarchy of types to manage one of my all-time favorites things in the world: food.
CREATE TYPE food_t AS OBJECT
(
   name VARCHAR2 (100),
   food_group VARCHAR2 (100),
   grown_in VARCHAR2 (100),
   /* Generic foods cannot have a price, but we can
      insist that all subtypes DO implement a price
      function. */
   NOT INSTANTIABLE MEMBER FUNCTION price RETURN NUMBER
)
   NOT FINAL NOT INSTANTIABLE;
/

CREATE TYPE dessert_t UNDER food_t (
      contains_chocolate CHAR (1),
      year_created NUMBER (4),
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   )
   NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_t
IS
   OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   IS
      multiplier   NUMBER := 1;
   BEGIN
      DBMS_OUTPUT.put_line ('Dessert price!');

      IF self.contains_chocolate = 'Y'
      THEN
         multiplier := 2;
      END IF;

      IF self.year_created < 1900
      THEN
         multiplier := multiplier + 0.5;
      END IF;

      RETURN (10.00 * multiplier);
   END;
END;
/

CREATE TYPE cake_t UNDER dessert_t (
      diameter NUMBER,
      inscription VARCHAR2 (200),
      /* Inscription and diameter determine the price */
      OVERRIDING MEMBER FUNCTION price RETURN NUMBER
   );
/

CREATE OR REPLACE TYPE BODY cake_t
IS
   OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Cake price!');
      RETURN (5.00 + 0.25 * (LENGTH (self.inscription)) + 0.50 * diameter);
   END;
END;
/

DECLARE
   last_resort_dessert   dessert_t
                            := dessert_t ('Jello',
                                          'PROTEIN',
                                          'bowl',
                                          'N',
                                          1887);
   heavenly_cake         cake_t
                            := cake_t ('Marzepan Delight',
                                       'CARBOHYDRATE',
                                       'bakery',
                                       'N',
                                       1634,
                                       8,
                                       'Happy Birthday!');
BEGIN
   DBMS_OUTPUT.put_line (last_resort_dessert.price);
   DBMS_OUTPUT.put_line (heavenly_cake.price);
END;
/

/* Demonstration of dynamic polymorphism */

DECLARE
   TYPE foodstuffs_nt IS TABLE OF food_t;

   fridge_contents   foodstuffs_nt
                        := foodstuffs_nt (dessert_t ('Strawberries and cream',
                                                     'FRUIT',
                                                     'Backyard',
                                                     'N',
                                                     2001),
                                          cake_t ('Chocolate Supreme',
                                                  'CARBOHYDATE',
                                                  'Kitchen',
                                                  'Y',
                                                  2001,
                                                  8,
                                                  'Happy Birthday, Veva'));
BEGIN
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            'Price of '
         || fridge_contents (indx).name
         || ' = '
         || fridge_contents (indx).price);
   END LOOP;
END;
/
As you can see from the above example, Oracle Database supports substitutability with object types as well. The easiest way to understand this concept is:

Every cake is a dessert; every dessert is a food.
But of course not every food is a dessert, nor is every dessert a cake.

Take a look at that example of dynamic polymorphism. I declare a nested table of food_t, but I have no problem inserting a dessert and cake into the collection.

The same is true for relational tables. In the code below, I create a table whose single column is of type food_t. I then insert two rows into the table (a dessert and cake).
CREATE TABLE food_tab (food food_t)
/

DECLARE
   s_and_c    dessert_t
                 := dessert_t ('Strawberries and cream',
                               'FRUIT',
                               'Backyard',
                               'N',
                               2001);
   choc_sup   cake_t
                 := cake_t ('Chocolate Supreme',
                            'CARBOHYDATE',
                            'Kitchen',
                            'Y',
                            2001,
                            8,
                            'Happy Birthday, Veva');
BEGIN
   INSERT INTO food_tab
        VALUES (s_and_c);

   INSERT INTO food_tab
        VALUES (choc_sup);
END;
/

SELECT COUNT (*) FROM food_tab
/

2
I have heard from several developers who have used object types to implement applications within a comprehensive object-oriented framework. But it is rare. Most Oracle Database developers rely on the relational model of SQL and the procedural structure of PL/SQL to build their application backends.

There is, as I am sure you can imagine, lots more to learn about object types. If this approach intrigues you, check out the very useful Database Object-Relational Developer's Guide.

Well, there's your introduction to the different kinds of user-defined types in Oracle Database and PL/SQL. Did I leave anything important out? Let me know!

And here's a LiveSQL script that demonstrates all the object type-related functionality shown above.

Comments

  1. Hi Steven,
    one question about the last example.
    Supposing you need to store in a table the instantiated objects, can you define the relevant column as type "food_t" or do you need to have different tables depending on the specific type "dessert_t" or "cake_t"?

    Thank you
    Flavio

    ReplyDelete
  2. You certainly can. The principle of substitutability (every dessert is also a food) works both in PL/SQL and in tables. I will add this information to the blog post, along with a link to a LiveSQL script demonstrating this.

    ReplyDelete
    Replies
    1. Thanks for the added LiveSQL example! @lesalgadosup

      Delete
  3. Hi Steven,
    I was continuing to the topic about the type of Obeject. and found out that the code of an example you pasted as below,
    CREATE TYPE food_t AS OBJECT
    (
    name VARCHAR2 (100),
    food_group VARCHAR2 (100),
    grown_in VARCHAR2 (100),
    /* Generic foods cannot have a price, but we can
    insist that all subtypes DO implement a price
    function. */
    NOT INSTANTIABLE MEMBER FUNCTION price RETURN NUMBER
    )
    NOT FINAL NOT INSTANTIABLE;
    It was created as the "not instantiable,but in the following block of PL/SQL, you tried to instantiate an Object instance of "food_t". I am understanding that an Object defined as "not instantiable" can not be directly instantiated, correct?
    In your Book 'Oracle_PL_SQL_Programming', you mentioned that the not instantiable Object can be assigned an instance of a subtype.
    Meanwhile, I also tried running the code you pasted for food_t,

    DECLARE
    my_favorite_vegetable food_t
    := food_t ('Brussel Sprouts', 'VEGETABLE', 'farm');
    BEGIN
    p.l (my_favorite_vegetable.price);
    END;

    And I was prompted with the error in the following,
    "[Error] Execution (3: 10): ORA-06550: line 3, column 10:
    PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE
    ORA-06550: line 2, column 28:
    PL/SQL: Item ignored
    ORA-06550: line 5, column 9:
    PLS-00320: the declaration of the type of this expression is incomplete or malformed
    ORA-06550: line 5, column 4:
    PL/SQL: Statement ignored"

    And so could you please take a look at the problem when available?

    And one more thing for the example code is that ther are 2 property variables(name VARCHAR2 (100)and food_group VARCHAR2 (100)) defined for Object 'food_t', but in the block you initiated an instance with 3 values as below,

    my_favorite_vegetable food_t
    := food_t ('Brussel Sprouts', 'VEGETABLE', 'farm');

    Is it correct?

    Thanks,
    Gavin Zzw



    ReplyDelete
    Replies
    1. Hi Steven,

      Please ignore the last question because I made a mistake. 'food_t' is created with 3 property variables.

      regards,
      Gavin

      Delete
    2. Gavin, thanks for pointing that out. The block that attempted to instantiate an instance of food_t would fail not only because of the type definition (NOT INSTANTIABLE) but because of the call to an undefined proc, p.l. I have removed the block, which was not germane to the blog post.

      Thanks for your close reading!

      Delete
  4. Hi Steven,

    the output of your first anonymous block will be:
    Dessert price!
    15
    Cake price!
    12.75

    But why don't use the price of the dessert as the base value (in your example "5.0") for the price of the cake. The result would be:
    Dessert price!
    15
    Cake price!
    22.75

    Now one could think that the code to calculate the dessert price must be repeated:
    CREATE OR REPLACE TYPE BODY cake_t
    IS
    OVERRIDING MEMBER FUNCTION price
    RETURN NUMBER
    IS
    multiplier NUMBER := 1;
    baseprice NUMBER;
    BEGIN
    IF self.contains_chocolate = 'Y' THEN multiplier := 2; END IF;
    IF self.year_created < 1900 THEN multiplier := multiplier + 0.5; END IF;
    baseprice := 10.00 * multiplier;

    DBMS_OUTPUT.put_line ('Cake price!');
    RETURN (baseprice + 0.25 * (LENGTH (self.inscription)) + 0.50 * diameter);
    END;
    END;
    /

    But in such cases you can use generalized invocation "(… AS …)" to access the values of the superclass(s):
    CREATE OR REPLACE TYPE BODY cake_t
    IS
    OVERRIDING MEMBER FUNCTION price
    RETURN NUMBER
    IS
    baseprice NUMBER;
    BEGIN
    baseprice := (self AS dessert_t).Price();
    DBMS_OUTPUT.put_line ('Cake price!');
    RETURN (baseprice + 0.25 * (LENGTH (self.inscription)) + 0.50 * diameter);
    END;
    END;
    /

    We have used this for a hierarchy of object types which all have a function to get the actual state of the instance as JSON and so you don't need to repeat all the code to create the JSON for the supertypes but can easily get the "parental" JSON by including a "(instance AS supertype).AsJSON()" into the JSON generation.

    Kind regards, Niels Hecker


    ReplyDelete
    Replies
    1. Thanks for the reminder about the use of the generalized invocation, Niels. I originally wrote the above code before that feature was available.

      Delete
  5. Hi Steven,

    There is any way, in a user-defined type, to define a variable (attribute) of pl/sql package record ?
    In my scenario, I need to define some variable (attribute) like utl_tcp.connection.
    In the type specification, it's not possible.
    In the type body, I can't to define it at the body header like in a package body.

    I need something like this

    CREATE OR REPLACE TYPE ty_tcp as object
    (
    isOpen char(1),

    CONSTRUCTOR Function ty_tcp return self as result,

    MEMBER Procedure OpenConnection
    ( i_host in varchar2,
    i_port in integer ),
    MEMBER Procedure CloseConnection
    )
    /

    CREATE OR REPLACE TYPE BODY TY_tcp is

    tcp_conn utl_tcp.ty_connection,

    CONSTRUCTOR Function ty_tcp
    return self as result is
    begin
    isOpen := 'N';
    end;

    MEMBER Procedure OpenConnection
    ( i_host in varchar2,
    i_port in integer )
    is
    begin
    isOpen := 'N';
    tcp_conn := utl_tcp.open_connection( i_host, i_port );
    isOpen := 'Y';
    exception
    when others then
    dbms_output.put_line( sqlerrm );
    raise;
    end;

    MEMBER Procedure CloseConnection
    is
    begin
    utl_tcp.close_connection ( tcp_conn );
    isOpen := 'N';
    exception
    when others then
    dbms_output.put_line( sqlerrm );
    raise;
    end;

    end;

    Thanks on advance

    ReplyDelete
  6. Sorry that is not going to work. You cannot declare private attributes in an object type body. You SHOULD be able to, but that is not supported. Instead, declare the record inside the method in which you want to use it. But that record will NOT persist between invocations of the method. If you need that sort of functionality then you should use a package, not an object type.

    ReplyDelete
  7. Very good Steven
    I would like to know where I can find more materials on the subject

    ReplyDelete
    Replies
    1. Start with the user guide for O-O development that I provide in the post. Have you already looked through that?

      Delete
  8. Hey Steven.

    Is it possible to bulk collect into a collection where the value is in a physical DB table & not in UDT? Im also using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

    Object Type:
    CREATE OR REPLACE type dbo.P_REC AS OBJECT
    (
    ATTR1 VARCHAR2(64 BYTE),
    ATTR2 VARCHAR2(128 BYTE),
    ATTR3 VARCHAR2(128 BYTE),
    ATTR4 VARCHAR2(128 BYTE)
    );

    Collection Type:
    CREATE OR REPLACE type dbo.P_REC_LIST is table of P_REC;

    Stored Procedure:
    PROCEDURE Get_PRecList(tmpPList IN P_REC_LIST,
    resultCursor out sys_refcursor)
    IS

    TYPE CNsTable IS TABLE OF PERSON.ATTR1%TYPE INDEX BY PLS_INTEGER;
    TYPE TmpTable IS TABLE OF P_REC INDEX BY PLS_INTEGER;

    tmpTBL1 CNsTable;
    Collection1 TmpTable;

    BEGIN

    IF tmpPList.count > 0 THEN

    SELECT ATTR1
    BULK COLLECT INTO tmpTBL1
    FROM Table1
    WHERE ATTR1 NOT IN (SELECT ATTR1 FROM TABLE(tmpPList)); --99% sure problem is here.

    /*SELECT ATTR1
    BULK COLLECT INTO tmpTBL1
    FROM Table1
    WHERE ATTR1 NOT IN (SELECT ATTR1 FROM Table2); Works fine. -- Table2 is a physical DB table*/

    FOR indx IN 1 .. tmpTBL1.COUNT
    LOOP
    Collection1(Collection1.COUNT + 1) := tmpPList(x);
    END LOOP;

    IF Collection1.COUNT > 0 THEN
    FORALL ind IN 1 .. Collection1.COUNT
    DELETE
    FROM PERSON
    WHERE CN = Collection1(ind).CN;
    END IF;
    /*Records do not get deleted if i use tmpPList but do it i use a physical DB table such as Table2
    */

    END IF;
    END;

    ReplyDelete
    Replies
    1. My apologies, Mike. I somehow missed this coming in.

      In case you are still looking for help, both approaches should work. Here's the thing: your collection type must be defined at the SCHEMA LEVEL.

      In other words, either create or replace type ... (nested table and varray only) or starting in 18c, you can also do this with index by tables whose types are declared in the package specification.

      Please give this a try - and if you have more problems, please create an unlisted script on livesql.oracle.com and post the link so I and others can take a look.

      Again, my apologies for the ridiculous delay.

      Delete
  9. It's about time Oracle fixed their terribly broken Object types with a constructor and a static method issue.

    create or replace type TBroken as object (
    Value integer,
    constructor function TBroken(pValue integer) return self as result,
    static procedure TimesFive(pValue integer),
    member procedure TimesTen
    );
    /

    create or replace type body TBroken is
    constructor function TBroken(pValue integer) return self as result is
    begin
    Value := pValue;
    end;

    static procedure TimesFive(pValue in out integer) is
    begin
    pValue := pValue * 5;
    end;

    member procedure TimesTen is
    begin
    TBroken.TimesFive(Value);
    TBroken.TimesFive(Value);
    end;
    end;
    /

    show errors;

    Type created.
    Warning: compiled but with compilation errors
    Errors for TYPE BODY TBROKEN

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    14/13 PLS-00225: subprogram or cursor 'TBROKEN' reference is out of sco


    14/5 PL/SQL: Statement ignored
    15/13 PLS-00225: subprogram or cursor 'TBROKEN' reference is out of sco


    15/5 PL/SQL: Statement ignored
    4/20 PLS-00538: subprogram or cursor 'TIMESFIVE' is declared in an obj
    ect type specification and must be defined in the object type bod


    7/20 PLS-00539: subprogram 'TIMESFIVE' is declared in an object type b
    ody and must be defined in the object type specification

    ------------------------------------------------------------

    That's right, if you declare an constructor, you can't call a static procedure. Why? You have to reference the type name to call a static method, but when you do, it thinks you're calling the constructor. That was tested well at Oracle, wasn't it. It's pretty much crippled the functionality.

    Workarounds :
    * Avoid one of constructor or static procs (and lose functionality)
    * Create a synonym for the type (you are kidding, right?)
    * Make all static methods functions (hack).
    * Prefix the schema name in front of the static call (Oracle have never worked in deploying in multi schema environments).

    How about fixing a poorly implemented and serious bug?

    And don't get me started on the inability to have method names like Add or Set. Not allowed.... Why not?!! It'll be qualified by the instance name. Every other language seems to allow it. It's a common method name to want to add.

    ReplyDelete
    Replies
    1. Thanks for this, Paul. Have you submitted any of these as issues with Oracle Support?

      Otherwise (or in any case), I will see if the team is aware of these.

      Thanks, Steven

      Delete
    2. Hi Steve, it's been logged several times since 11g. It looks like the static method bug may have been fixed in 19c, but still exists in 12c. The limitations on method names still exist.

      Delete
    3. Thanks, Paul! Yes, I just confirmed on LiveSQL.Oracle.com that tbroken now compiles without error. Progress is being made....

      :-)

      Delete
  10. Hi Steve, in the section "User-Defined Collection Types," you bulk collect into a collection of user defined types (l_user_timings). Can this collection be used like a table? In other words can I do something analogous to:

    select total_seconds
    into
    from l_user_timings
    where comp_event_id = N;

    ReplyDelete
    Replies
    1. Absolutely, Don. Here's the thing: your collection type must be defined at the SCHEMA LEVEL. Plus at least until 12.2, you need to reference it as TABLE(my_collection).

      In other words, either create or replace type ... (nested table and varray only) or starting in 18c, you can also do this with index by tables whose types are declared in the package specification.

      Please give this a try - and if you have more problems, please create an unlisted script on livesql.oracle.com and post the link so I and others can take a look.

      The collection variable can be declared locally, but since you are referencing it inside the SELECT statement, the type must be resolvable at the schema level.

      Delete
    2. Great, thanx. This gives me some more ammunition for upgrading (we're still on 11).

      Delete
    3. Ah please pass on my sympathies to the entire dev team. :-)

      Delete
  11. Is the usage restricted to SQL or can INDEX-BY-Tables also be used in DML statements in 19c?
    For 12.1 it was obviously restricted to SQL (see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532864900346799695)

    ReplyDelete

Post a Comment

Popular posts from this blog

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel