Skip to Main Content
  • Questions
  • PRAGMA SERIALLY_REUSABLE implications in a callback service

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Avni.

Asked: August 12, 2020 - 11:50 am UTC

Answered by: Chris Saxon - Last updated: August 12, 2020 - 2:01 pm UTC

Category: PL/SQL - Version: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Viewed 100+ times

You Asked

Scenario:
Oracle Recipe Tool / Microservices
JNDI : jdbc/SOAXAOPS
PLSQL : schema.pkg1.procedure

This is the entry point to an on-premise DB package.
It can be called from cloud and non-cloud services.

Now whenever I would compile schema.pkg1
The callback will give below error :
ORA-04065: not executed, altered or dropped package body "schema.pkg1"
ORA-06508: PL/SQL: could not find program unit being called: "schema.pkg1"

This error can be bypassed if a DDL was issued in this schema or existing stale connections are explicitly killed.

So I added PRAGMA SERIALLY_REUSABLE; to schema.pkg1 based on below url
========================================================================
https://stackoverflow.com/questions/1761595/frequent-error-in-oracle-ora-04068-existing-state-of-packages-has-been-discarde
https://docs.oracle.com/en/cloud/paas/integration-cloud/database-adapter/resolve-error-ora-04068-existing-state-packages-has-been-discarded.html
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/packages.htm#LNPLS99977

In addition integrations team did this modification:
Under Connection Properties:
Test Connections on Reserve: Yes
Test Table Name:
SQL begin
dbms_session.modify_package_state(dbms_session.reinitialize);
end;
Seconds to trust Idle Pool Connection: 0

This solved initial error of ORA-04065 , ORA-06508
However it gave 1-off error : ORA-06508: PL/SQL: could not find program unit being called
This was at the line in schema.pkg1 which was giving call to schema.pkg2
Now schema.pkg2 is not having PRAGMA SERIALLY_REUSABLE;

In a request set of 2 requests... first one faced this err and subsequent requests have been fine so far... (This is UAT environment)

==============
Question is :
==============
When I recompile pkg (HOT patch) in UAT can it re happen.
Do I need to add this pragma to all nested packages.
What are the pros and cons of using this Pragma apart from trigger and SQl prompt usage as mentioned on Oracle documentation.
Is there an alternate way to deal with these errors for callback services.


and we said...

Using PRAGMA SERIALLY_REUSABLE means that package state only lasts for the duration of the call. Without this package state lasts for the duration of the session.

The difference between these is clear in this example from the docs:

CREATE OR REPLACE PACKAGE pkg IS
  n NUMBER := 5;
END pkg;
/

CREATE OR REPLACE PACKAGE sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  n NUMBER := 5;
END sr_pkg;
/

BEGIN
  pkg.n := 10;
  sr_pkg.n := 10;
END;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n);
  DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n);
END;
/

pkg.n: 10
sr_pkg.n: 5


The first anonymous block sets the global variable to 10 in both packages. For the regular package, this persists for the rest of the session. For the serially reusable package this state is lost after the anonymous block completes.

This the WRONG SOLUTION for hot patching!

You should be look at Edition-Based Redefinition instead. This brings the concept of an edition which extends the namespace.

Before compiling your updated packages, you:

- Create a new edition
- Connect to it
- Compile your packages and check everything worked

The current version of the code continues to exist in the original edition. So you're free to make whatever changes you need without service interruption.

Once your release is complete, you need a process to migrate the existing sessions from the current edition to the new. To do this seamlessly, you typically need a load balancer to migrate the client connections. I'm not sure exactly what you mean by "callback services", so I can't comment on exactly what you need to do.

Read more about this at:

https://blogs.oracle.com/oraclemagazine/a-closer-look-at-the-new-edition
https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/editions.html#GUID-58DE05A0-5DEF-4791-8FA8-F04D11964906
https://oracle-base.com/articles/11g/edition-based-redefinition-11gr2

and you rated our response

  (1 rating)

Reviews

Thanks for the review

August 12, 2020 - 3:32 pm UTC

Reviewer: A reader

Thanks for your quick response .. am checking with our internal DBA team on possibility of using Edition-Based Redefinition within our schema. .. and behavior with callback schema.

New suggestion will need time for approvals and implementation.

Meanwhile what I understood was I can keep this Pragma for entry point Package and prefer a cold patching window for code change.
I have only single record processing for each callback service... each time a new connection should be initiated.

Avni

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.