What’s new for Developers in Oracle AI Database 23.26.0

By / 2025-11-11 / Technology

JavaScript Oracle Oracle Database PL/SQL SQL

For more information on what’s new in Oracle Database 23.9, see: What’s new for Developers in Oracle Database 23.9

Oracle AI Database 23.26.0 brings some fantastic new features to developers—again! This release has quite a broad spectrum of new features, some of which are near and dear to everyday developers, such as the SQL QUALIFY clause, and some of them help venture into new(er) avenues, such as GraphQL support. All in all, the release update shipped 17 new features. What follows are my favorite developer features.

Table of Contents

QUALIFY clause

The QUALIFY clause filters results based on analytic (window) functions in a SELECT statement. It is analogous to the HAVING clause for aggregates( GROUP BY). It processes after WHERE, GROUP BY, HAVING, and WINDOW but before ORDER BY and row limiting. It simplifies analytic queries by reducing the need for nested queries for filtering window results, improving readability, maintenance, and expressiveness of the query.

Let’s say you want to calculate the percentage of a country’s population compared to the rest of the world. You can do that via the RATIO_TO_REPORT() window function:

Select countries and their population ratio compared to the rest of the world
SQL> SELECT name, RATIO_TO_REPORT(population) OVER () AS population_ratio
     FROM countries
     ORDER BY population_ratio DESC
     FETCH FIRST 10 ROWS ONLY;

NAME          POPULATION_RATIO
_____________ __________________________________________
China         0.1849460196954984579917964362180262027864
India         0.1732116645006871920491412436773656703161
United States 0.0439770855844605108327912819437450831375
Indonesia     0.0350991519956618080163056181456099180044
Brazil        0.0278946545941693524366935176886839704608
Pakistan      0.0277632266104268871736171679139413836535
Nigeria       0.0271742048540201068787323076429913757064
Bangladesh     0.021297343792365156090765447796778144488
Russia        0.0189826619242178766099594095891673422831
Japan         0.0168516319642473143412773357517507035538

Because RATIO_TO_REPORT()produces a ratio from 0 – 1, you can convert this to a percentage by multiplying the value by 100. Add an additional ROUND() function over it to round to 2 digits right of the decimal point.

Select countries and their population percentage compared to the rest of the world 
SQL> SELECT name,
            ROUND(100 * RATIO_TO_REPORT(population) OVER (), 2) AS population_percent
     FROM countries
     ORDER BY population_percent DESC
     FETCH FIRST 10 ROWS ONLY;

NAME          POPULATION_PERCENT
_____________ __________________
China                      18.49
India                      17.32
United States                4.4
Indonesia                   3.51
Brazil                      2.79
Pakistan                    2.78
Nigeria                     2.72
Bangladesh                  2.13
Russia                       1.9
Japan                       1.69

The previous query retrieves the top ten countries, which is accomplished via ORDER BY population_percent DESC FETCH FIRST 10 ROWS ONLY clauses. But what if you want to retrieve the top N countries that are home to at least 1% of the world’s population? A percentage is a portion of the overall result; it is a calculated value. 1% of the world population is different than 1% of Europe’s population. How do you know when to stop returning rows when the value is below 1%? You can’t restrict the query output via the WHERE clause because the WHERE clause filters the input, in this case, each row in the country table. At that stage, the computation of the ratio over multiple rows hasn’t happened yet. You can also not use the HAVING clause, because that would apply a filter on the aggregated groups. But in this query, there is no grouping happening at all. The solution so far would have been to make the query a subquery and then apply the filtering via a WHERE clause on the outer query. At that stage, the computation of the percentage has already happened in the subquery and is now input to the outer query. Hence, the WHERE clause can filter on it:

Select the top N countries that are home to at least 1% of the world population
SQL> SELECT name, population_percent
     FROM (
      SELECT name,
             ROUND(100 * RATIO_TO_REPORT(population) OVER (), 2) AS population_percent
      FROM countries
     )
     WHERE population_percent >= 1
     ORDER BY population_percent DESC;

NAME                             POPULATION_PERCENT
________________________________ __________________
China                                         18.49
India                                         17.32
United States                                   4.4
Indonesia                                      3.51
Brazil                                         2.79
Pakistan                                       2.78
Nigeria                                        2.72
Bangladesh                                     2.13
Russia                                          1.9
Japan                                          1.69
Mexico                                         1.68
Ethiopia                                       1.45
Philippines                                    1.41
Egypt                                          1.33
Vietnam                                         1.3
Democratic Republic of the Congo               1.14
Iran                                           1.11
Turkey                                         1.09
Germany                                        1.07

But the query is quite verbose, isn’t it? In comes the QUALIFY clause, which filters on the output of a window function. So, to return all countries that have at least 1% of the world population, you can use this query now:

Select the top N countries that are home to at least 1% of the world population using QUALIFY
SQL> SELECT name,
         ROUND(100 * RATIO_TO_REPORT(population) OVER (), 2) AS population_percent
     FROM countries
     QUALIFY population_percent >= 1
     ORDER BY population_percent DESC;

NAME                             POPULATION_PERCENT
________________________________ __________________
China                                         18.49
India                                         17.32
United States                                   4.4
Indonesia                                      3.51
Brazil                                         2.79
Pakistan                                       2.78
Nigeria                                        2.72
Bangladesh                                     2.13
Russia                                          1.9
Japan                                          1.69
Mexico                                         1.68
Ethiopia                                       1.45
Philippines                                    1.41
Egypt                                          1.33
Vietnam                                         1.3
Democratic Republic of the Congo               1.14
Iran                                           1.11
Turkey                                         1.09
Germany                                        1.07

Concise, readable, maintainable, neat.

GraphQL Table Function for SQL

GraphQL (not to be confused with GQL (Graph Query Language) nor SQL/PGQ (SQL/Property Graph Query) has gained traction in the API world. As stated on the official website:

GraphQL is an open‑source query language for APIs and a server‑side runtime. It provides a strongly‑typed schema to define relationships between data, making APIs more flexible and predictable. And it isn’t tied to a specific database or storage engine — it works with your existing code and data, making it easier to evolve APIs over time.

graphql.org

The syntax is somewhat close to JSON, but not to be confused with JSON. GraphQL support was added to Oracle Database with the introduction of JSON Relational Duality, where Duality Views can be expressed in either SQL/JSON or GraphQL. Both languages are powerful, and it’s worth mentioning that everything and more that can be expressed in GraphQL can also be expressed in SQL/JSON. GraphQL is more concise than SQL/JSON, which is equally a benefit and a downside, depending on the use case and preference of the reader.

As GraphQL is a standalone query language for data, there is no reason to restrict it only to Duality Views. 23.26.0 introduces a new GRAPHQL() table function that takes GraphQL syntax as input, transforms it into SQL, and executes it. This allows applications to also accept queries in GraphQL format, having to do nothing but pass them on to the table function. For example, to retrieve all countries within Europe, the SQL query would be:

All countries of Europe in alphabetical order via SQL
SQL> SELECT c.name
     FROM countries c
      JOIN regions r ON (c.region_id = r.region_id)
     WHERE r.name = 'Europe'
     ORDER BY name;

NAME
______________________
Albania
Andorra
Austria
Belarus
Belgium
Bosnia and Herzegovina
Bulgaria
Croatia
Cyprus
Czechia
Denmark
Estonia
Finland
France
Germany
Greece
Hungary
Iceland
Ireland
Italy
Kosovo
Latvia
Liechtenstein
Lithuania
Luxembourg
Malta
Moldova
Monaco
Montenegro
Netherlands
North Macedonia
Norway
Poland
Portugal
Romania
Russia
San Marino
Serbia
Slovakia
Slovenia
Spain
Sweden
Switzerland
Ukraine
United Kingdom
Vatican City

In GraphQL, the same query would be:

All countries of Europe in alphabetical order via GraphQL
SQL> SELECT *
     FROM GRAPHQL(
     'regions (name: "Europe") {
        countries @orderby(sql: "name ASC") {
          name
        }
     }') ;

DATA
________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
{"countries":[{"name":"Albania"},{"name":"Andorra"},{"name":"Austria"},{"name":"Belarus"},{"name":"Belgium"},{"name":"Bosnia and Herzegovina"},{"name":"Bulgaria"},{"name":"Croatia"},{"name":"Cyprus"},{"name":"Czechia"},{"name":"Denmark"},{"name":"Estonia"},{"name":"Finland"},{"name":"France"},{"name":"Germany"},{"name":"Greece"},{"name":"Hungary"},{"name":"Iceland"},{"name":"Ireland"},{"name":"Italy"},{"name":"Kosovo"},{"name":"Latvia"},{"name":"Liechtenstein"},{"name":"Lithuania"},{"name":"Luxembourg"},{"name":"Malta"},{"name":"Moldova"},{"name":"Monaco"},{"name":"Montenegro"},{"name":"Netherlands"},{"name":"North Macedonia"},{"name":"Norway"},{"name":"Poland"},{"name":"Portugal"},{"name":"Romania"},{"name":"Russia"},{"name":"San Marino"},{"name":"Serbia"},{"name":"Slovakia"},{"name":"Slovenia"},{"name":"Spain"},{"name":"Sweden"},{"name":"Switzerland"},{"name":"Ukraine"},{"name":"United Kingdom"},{"name":"Vatican City"}]}

As you can see, the output is different. GraphQL queries will return JSON as a result, which makes sense because GraphQL is built for the API world, where a request usually comes in as a REST call and expects JSON as the payload coming back. This is again great for an application that wants to facilitate GraphQL queries against an Oracle Database. That application has to do nothing else than pass the GraphQL query in as a bind variable to the table function and return the result as is. Beyond the query execution and fetching the result, there is zero extra code needed in the application itself.

You may have also seen in the query above that Oracle offers a few what are known as GraphQL directives that spill over into the SQL world. The @orderby(sql: ...) is one of them. This allows you to mix and match the query syntax between GraphQL and SQL. For example, the same join condition and filter on the regions table could be expressed via a mixed-in sub-SQL-query, eliminating the need for a nested hierarchy, i.e., the regions level is no longer required and the result can be 1 row per country:

All countries of Europe in GraphQL, with one object per country
SQL> SELECT *
     FROM GRAPHQL(
     'countries @where(sql: "region_id = (SELECT region_id FROM regions WHERE NAME = ''Europe'')") {
        name
     }') ;

DATA
_________________________________
{"name":"Andorra"}
{"name":"Albania"}
{"name":"Austria"}
{"name":"Bosnia and Herzegovina"}
{"name":"Belgium"}
{"name":"Bulgaria"}
{"name":"Belarus"}
{"name":"Switzerland"}
{"name":"Cyprus"}
{"name":"Czechia"}
{"name":"Germany"}
{"name":"Denmark"}
{"name":"Estonia"}
{"name":"Spain"}
{"name":"Finland"}
{"name":"France"}
{"name":"United Kingdom"}
{"name":"Greece"}
{"name":"Croatia"}
{"name":"Hungary"}
{"name":"Ireland"}
{"name":"Iceland"}
{"name":"Italy"}
{"name":"Liechtenstein"}
{"name":"Lithuania"}
{"name":"Luxembourg"}
{"name":"Latvia"}
{"name":"Monaco"}
{"name":"Moldova"}
{"name":"Montenegro"}
{"name":"North Macedonia"}
{"name":"Malta"}
{"name":"Netherlands"}
{"name":"Norway"}
{"name":"Poland"}
{"name":"Portugal"}
{"name":"Romania"}
{"name":"Serbia"}
{"name":"Russia"}
{"name":"Sweden"}
{"name":"Slovenia"}
{"name":"Slovakia"}
{"name":"San Marino"}
{"name":"Ukraine"}
{"name":"Vatican City"}
{"name":"Kosovo"}

And because GraphQL is invoked via a table function, one can even apply SQL operations to the results. See how the column is called DATA and the content is JSON? Well, since 12.1.0.2, one can apply SQL operations on JSON, so this is legitimate too (the ORDERY BY on the result:

All countries of Europe in GraphQL, sorted in SQL
SQL> SELECT *
     FROM GRAPHQL(
     'countries @where(sql: "region_id = (SELECT region_id FROM regions WHERE NAME = ''Europe'')") {
        name
     }')
     ORDER BY data.name;

DATA
_________________________________
{"name":"Albania"}
{"name":"Andorra"}
{"name":"Austria"}
{"name":"Belarus"}
{"name":"Belgium"}
{"name":"Bosnia and Herzegovina"}
{"name":"Bulgaria"}
{"name":"Croatia"}
{"name":"Cyprus"}
{"name":"Czechia"}
{"name":"Denmark"}
{"name":"Estonia"}
{"name":"Finland"}
{"name":"France"}
{"name":"Germany"}
{"name":"Greece"}
{"name":"Hungary"}
{"name":"Iceland"}
{"name":"Ireland"}
{"name":"Italy"}
{"name":"Kosovo"}
{"name":"Latvia"}
{"name":"Liechtenstein"}
{"name":"Lithuania"}
{"name":"Luxembourg"}
{"name":"Malta"}
{"name":"Moldova"}
{"name":"Monaco"}
{"name":"Montenegro"}
{"name":"Netherlands"}
{"name":"North Macedonia"}
{"name":"Norway"}
{"name":"Poland"}
{"name":"Portugal"}
{"name":"Romania"}
{"name":"Russia"}
{"name":"San Marino"}
{"name":"Serbia"}
{"name":"Slovakia"}
{"name":"Slovenia"}
{"name":"Spain"}
{"name":"Sweden"}
{"name":"Switzerland"}
{"name":"Ukraine"}
{"name":"United Kingdom"}
{"name":"Vatican City"}

SQL Objects and Collections in MLE JavaScript

MLE JavaScript now supports objects and collection types and can be used as parameters and return types. Although I’m not necessarily a big fan of collection types myself, they still do have their uses. Let’s say you want to model countries as a user-defined type and return it as such, but using JavaScript. The first step would be to define a type:

Type definition of a country object
SQL> CREATE OR REPLACE TYPE country AS OBJECT (
       country_code VARCHAR2(2),
       name         VARCHAR2(100),
       population   NUMBER,
       region       VARCHAR2(100)
     );
     /

Type COUNTRY compiled

No errors.

Then you can define a JavaScript function that finds the country by code and returns the object. Note that the object instantiation and data conversion are implicit:

JavaScript function definition that takes a string input and returns a user-defined type
SQL> CREATE OR REPLACE FUNCTION get_country_by_code( p_country_code IN VARCHAR2 )
       RETURN country
       AS MLE LANGUAGE JAVASCRIPT
     {{
         const result = session.execute(
             `SELECT c.country_code,
                     c.name,
                     c.population,
                     r.name AS region
                FROM countries c
                 JOIN regions r ON (c.region_id = r.region_id)
               WHERE c.country_code = :code`,
             { code: P_COUNTRY_CODE }
         );
   
         return result.rows[0];
     }};
     /

Function GET_COUNTRY_BY_CODE compiled

No errors.

Once created, when you select the function, you will retrieve a brand new object instance:

Selecting a JavaScript function returning an object type:
SQL> select get_country_by_code('AT') AS country;

COUNTRY(COUNTRY_CODE, NAME, POPULATION, REGION)
--------------------------------------------------------------------------------
COUNTRY('AT', 'Austria', 8793000, 'Europe')

You can also pass in a user-defined type and retrieve attributes from it. Remember that Oracle treats identifiers generally case-insensitively by making them uppercase (as standardized by the SQL Standard). So in the JavaScript code, you have to use uppercase for the parameter and attribute name, too:

JavaScript function definition that takes a country object and returns the country_code attribute of the object
SQL> CREATE OR REPLACE FUNCTION get_country_name( p_country IN country)
       RETURN VARCHAR2
       AS MLE LANGUAGE JAVASCRIPT
     {{
         // Retrieve the attribute name from the object and return it
         return P_COUNTRY.NAME;
     }};
     /

Function GET_COUNTRY_NAME compiled

No errors.

The next example demonstrates two capabilities in one. It calls the previously defined get_country_by_code() function to retrieve the country information from the countries table and returns a country object. That object is then passed into the new get_country_name() function that disseminates the object and retrieves the name attribute from the object. This demonstrates the seamless integration between SQL and JavaScript by running SQL within a JavaScript function that returns a user-defined SQL type and passes that type into a JavaScript function that treats the user-defined type as a JavaScript object, retrieves the name from it, and returns a VARCHAR2 type to SQL:

Select the get_country_name() function with the input being the output of the get_country_by_code() function
SQL> select get_country_name(get_country_by_code('AT'));

GET_COUNTRY_NAME(GET_COUNTRY_BY_CODE('AT'))
___________________________________________
Austria

RESETTABLE clause

The RESETTABLE clause marks PL/SQL package instances as safe for state discard during reinstantiation. This often happens when a package is recreated while other users are using it. Marking a package as RESETTABLE prevents ORA-04068 errors when package states are invalidated. But it comes with another enhancement that isn’t really documented (yet). A resettable package will also reuse a package state if it can be determined that the state hasn’t been impacted.

What is ORA-04068: existing state of packages has been discarded anyway

The error ORA-04068: existing state of packages has been discarded occurs when a session attempts to use a package that has been invalidated or recompiled since the session last referenced it. This error occurs when the package body changes or when the package is stateful and its state becomes invalid. A package can maintain state between procedure or function calls within a session. This state is stored in the session’s memory (PGA). If a PL/SQL package declares at least one variable, constant, or cursor, it is stateful; otherwise, it is stateless. The state persists for the entirety of the session’s lifetime, unless the package is declared as SERIALLY_REUSABLE.

When a package is modified (e.g., its body is recompiled), all sessions have to obtain the new package containing the modifications. Hence, the package state is invalidated for all sessions. Any subsequent attempt to use the package in a session that had previously used it will result in ORA-04068. After ORA-04068 has been raised, the next use of the package will re-instantiate it.

The ORA-04068 error is raised to notify the user that the package state has been reset. Depending on the package used and whether its state is important, a session may execute compensating logic to recover from state loss or choose to ignore the error.

Non-RESETTABLE example

The following package GET_NUMBER enables a user to get a new random number. The package body has three components:

  1. A stateful variable v_invocations that keeps count of the number of invocations of the get_next() function
  2. A function get_invocations() that returns the value of the invocations variable
  3. A function get_next() that increments the invocations variable and returns a random number
Create GET_NUMBER PL/SQL package
SQL> -- Create a RESETTABLE package
SQL> CREATE OR REPLACE PACKAGE GET_NUMBER
     AS
       -- Function declaration for getting the number of invocations
       FUNCTION get_invocations RETURN NUMBER;

       -- Function declaration for getting the next random number
       FUNCTION get_next RETURN NUMBER;
     END GET_NUMBER;
     /

Package GET_NUMBER compiled

No errors.
SQL> -- Create the package body with a stateful variable
SQL> CREATE OR REPLACE PACKAGE BODY GET_NUMBER
     AS
       -- Stateful variable to keep track of invocations
       v_invocations NUMBER := 0;
   
       -- Function to return the number of invocations
       FUNCTION get_invocations RETURN NUMBER IS
       BEGIN
         RETURN v_invocations;
       END get_invocations;

       -- Function to return the next random number and increment invocations
       FUNCTION get_next RETURN NUMBER IS
       BEGIN
         v_invocations := v_invocations + 1;
         RETURN ROUND(DBMS_RANDOM.VALUE(1, 100), 0);
       END get_next;
     END GET_NUMBER;
     /

Package Body GET_NUMBER compiled

No errors.

The following sequence of events will reproduce the error:

  1. Session 1 uses the package, hence instantiating it in the session PGA
  2. Session 2 recompiles the package body, hence discarding all sessions’ states
  3. Session 1 uses the package again –> The error is triggered because the state has been discarded

Keep a lookout for the v_invocations counter output in session 1. The recompilation and loss of the package state will reset the v_invocations counter to 0. If the user of session 1 relies on that state, it would have to abort any current work and retry:

Session 1 using the package
Ses1> -- Use the package
Ses1> set serveroutput on;
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value: 46
Invocations: 1

PL/SQL procedure successfully completed.

Ses1> -- Use the package again, invocations increase
Ses1> set serveroutput on;
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value: 92
Invocations: 2

PL/SQL procedure successfully completed.
Session 2 recompiling (or changing) the package body
Ses2> -- Recompile the package body in a different session (resets the state)
Ses2> ALTER PACKAGE GET_NUMBER COMPILE BODY;

Package GET_NUMBER altered.
Session 1, continuing to use the package; first call triggers the error that the state has been discarded, subsequent call reinitializes the package state
Ses1> -- Attempt to use the package again in the original session
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value after recompilation: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations after recompilation: ' || GET_NUMBER.get_invocations);
      END;
      /
BEGIN
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "GERALD.GET_NUMBER" has been invalidated
ORA-04065: not executed, altered or dropped package body "GERALD.GET_NUMBER"
ORA-06508: PL/SQL: could not find program unit being called: "GERALD.GET_NUMBER"
ORA-06512: at line 2

https://docs.oracle.com/error-help/db/ora-04068/

Ses1>
Ses1> set serveroutput on;
Ses1> -- Attempt to use the package again one more time, package state was reset, invocations start from 1 again
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value after recompilation: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations after recompilation: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value after recompilation: 94
Invocations after recompilation: 1

PL/SQL procedure successfully completed.

If you read the error messages carefully, you can actually see what has happened. These PL/SQL error message stacks read from innermost to outermost, i.e., the top error is the root cause, and the errors below are the callers adding their errors on top of it. So, to read it from the outside in, you have to stop at the bottom up:

  1. ORA-06512: at line 2: There is an error at line 2, i.e., the call to DBMS_OUTPUT ... || GET_NUMBER.get_next);
  2. ORA-06508: PL/SQL: could not find program unit being called: "GERALD.GET_NUMBER": the program unit being called, in this case, the GET_NUMBER package, can not be found. This is not so much that the package itself is gone, but the pointer in the PGA to the previous package instance is gone.
  3. ORA-04065: not executed, altered or dropped package body "GERALD.GET_NUMBER": Not executed, altered, or dropped package body. Indeed, the body has been altered because it has been recompiled.
  4. ORA-04061: existing state of package body "GERALD.GET_NUMBER" has been invalidated: The state of the GET_NUMBER package has been invalidated (due to recompliation).
  5. ORA-04068: existing state of packages has been discarded: The package state has been discarded.

Using RESETTABLE

The RESETTABLE clause lets the package author decide whether the package’s is important to the caller. In the example package above, the package author may determine that the number of invocations can be safely reset at any time without affecting the user calling either function. Hence, the package body is defined as RESETTABLE:

Create GET_NUMBER PL/SQL package as RESETTABLE
Ses1> -- Create a RESETTABLE package
Ses1> CREATE OR REPLACE PACKAGE GET_NUMBER
      AS
        -- Function declaration for getting the number of invocations
        FUNCTION get_invocations RETURN NUMBER;

        -- Function declaration for getting the next random number
        FUNCTION get_next RETURN NUMBER;
      END GET_NUMBER;
      /

Package GET_NUMBER compiled

No errors.
Ses1> -- Create the package body with a stateful variable
Ses1> CREATE OR REPLACE PACKAGE BODY GET_NUMBER
        RESETTABLE
      AS
        -- Stateful variable to keep track of invocations
        v_invocations NUMBER := 0;

        -- Function to return the number of invocations
        FUNCTION get_invocations RETURN NUMBER IS
        BEGIN
          RETURN v_invocations;
        END get_invocations;

        -- Function to return the next random number and increment invocations
        FUNCTION get_next RETURN NUMBER IS
        BEGIN
          v_invocations := v_invocations + 1;
          RETURN ROUND(DBMS_RANDOM.VALUE(1, 100), 0);
        END get_next;
      END GET_NUMBER;
      /

Package Body GET_NUMBER compiled

No errors.
Session 1 using the package
Ses1> -- Use the package
Ses1> set serveroutput on;
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value: 83
Invocations: 1

PL/SQL procedure successfully completed.

Ses1> -- Use the package again, invocations increase
Ses1> set serveroutput on;
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value: 60
Invocations: 2

PL/SQL procedure successfully completed.
Session 2 recompiling (or changing) the package body
Ses2> -- Recompile the package body in a different session (resets the state)
Ses2> ALTER PACKAGE GET_NUMBER COMPILE BODY;

Package GET_NUMBER altered.
Session 1, continuing to use the package; this time, no error is raised and v_invocations has been reset
Ses1> -- Attempt to use the package again in the original session
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value after recompilation: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations after recompilation: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value after recompilation: 33
Invocations after recompilation: 1

PL/SQL procedure successfully completed.

Ses1> -- Attempt to use the package again one more time, package state was reset, invocations start from 1 again
Ses1> BEGIN
        DBMS_OUTPUT.PUT_LINE('Number value after recompilation: ' || GET_NUMBER.get_next);
        DBMS_OUTPUT.PUT_LINE('Invocations after recompilation: ' || GET_NUMBER.get_invocations);
      END;
      /
Number value after recompilation: 32
Invocations after recompilation: 2

PL/SQL procedure successfully completed.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top