Download
Download
Red Stack 04/2019
Publication date 30.08.2019
Zum Download
Diese Artikel erwarten Sie:
Athanasios Manolopoulos
"Stateless Packages in PL/SQL und Hochverfügbarkeit von Applikationen"
---
---
---
---
---
---
---
---
---
---
---
---
---
- "Heute sind die ganz großen Anwendungsfelder die Produktnachverfolgung,
das Identitätsmanagement und alles, was unter dem Begriff Machine
to Machine Economy läuft", Interview mit Prof. Dr. Thomas Rose - Erfahrungen mit dem Oracle Autonomous Blockchain Cloud Service, Tim Rüb
- Reaktion von Oracle auf die DOAG Oracle-Support-Umfrage, Christian Trieb
- Testautomatisierung mit Selenium in Docker, Davide Groppuso
- Der Schutz von Geschäftsgeheimnissen und seine Neuerungen, Dr. Jan Bohnstedt
- Mit Jenkins in Richtung DevOps, Moritz Reinwald
- REST-Schnittstellen und Application Express, Carsten Czarski
- Agil unterwegs mit APEX in einem stark regulierten Umfeld, Hansjörg Grässlin, Christophe Girardey, Dr. Christian Wattinger
- Datenbank in der Wolke – Teil 3: Cloud-Lösungen in der Praxis, Borys Neselovskyi
- Datentransfer mit Oracle Tools – Was ist möglich?, Christian Gohmann
- Klick, Klick, Test – Automatisierte Tests mit APEX, Kai Donato
- Stateless Packages in PL/SQL und Hochverfügbarkeit von Applikationen, Athanasios Manolopoulos
- Teile und herrsche: Partitionierung in der Oracle-Datenbank, Dierk Lenz
- Das Design von Formularen, Maximilian Liesegang
Athanasios Manolopoulos
"Stateless Packages in PL/SQL und Hochverfügbarkeit von Applikationen"
create or replace package pkg_demo as
procedure doit;
end;
/
create or replace package body pkg_demo as
gv_n number := 5;
procedure doit is
begin
DBMS_OUTPUT.put_line(gv_n);
null;
end;
end;
/
Listing 1---
set serveroutput on
execute pkg_demo.doit
Listing 2---
create or replace package body pkg_demo as
gv_n number := 5;
procedure doit is
begin
DBMS_OUTPUT.put_line(gv_n);
--null;
end;
end;
/
Listing 3---
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "HR.PKG_DEMO" has been invalidated
ORA-04065: not executed, altered or dropped package body "HR.PKG_DEMO"
ORA-06508: PL/SQL: could not find program unit being called: "HR.PKG_DEMO"
ORA-06512: at line 1
04068. 00000 - "existing state of packages%s%s%s has been discarded"
*Cause: One of errors 4060 - 4067 when attempt to execute a stored procedure.
*Action: Try again after proper re-initialization of any application's state.
Listing 4---
create or replace package pkg_demo as
PRAGMA SERIALLY_REUSABLE;
procedure doit;
end;
/
create or replace package body pkg_demo as
PRAGMA SERIALLY_REUSABLE;
gv_n number := 5;
procedure doit is
begin
DBMS_OUTPUT.put_line(gv_n);
null;
end;
end;
/
Listing 5---
create or replace package pkg_demo as
PRAGMA SERIALLY_REUSABLE;
gv_n number := 5;
procedure doit is
begin
DBMS_OUTPUT.put_line(gv_n);
-- null;
end;
end;
/
Listing 6---
create or replace package pkg_demo as
PRAGMA SERIALLY_REUSABLE;
procedure doit;
function twice(p_n number) return number;
end;
/
create or replace package body pkg_demo as
PRAGMA SERIALLY_REUSABLE;
gv_n number := 5;
procedure doit is
begin
DBMS_OUTPUT.put_line(gv_n);
null;
end;
function twice(p_n number) return number is
begin
return p_n * 2;
end;
end;
/
Listing 7---
select pkg_demo.twice(5) from dual;
Listing 8---
ORA-06534: Cannot access Serially Reusable package "HR.PKG_DEMO"
ORA-06512: at line 1
06534. 00000 - "Cannot access Serially Reusable package %s"
*Cause: The program attempted to access a Serially Reusable package in
PL/SQL called from SQL context (trigger or otherwise). Such an
access is currently unsupported.
*Action: Check the program logic and remove any references to Serially Reusable
packages (procedure, function or variable references) which might happen
in PL/SQL called from sql context (trigger or otherwise).
Listing 9---
create or replace package pkg_demo as
procedure doit;
function twice(p_n number) return number is
end;
/
create or replace package body pkg_demo as
gv_n number := 5;
procedure init is
begin
gv_n := 5;
end;
procedure proceed is
begin
gv_n := gv_n + 4;
end;
procedure print is
begin
DBMS_OUTPUT.put_line(gv_n);
end;
procedure doit is
begin
init;
proceed;
print;
null;
end;
function twice(p_n number) return number is;
begin
return p_n * 2;
end;
end;
/
Listing 10---
create or replace package pkg_demo as
procedure init(p_n out number) is
begin
p_n := 5;
end;
procedure proceed(p_n in out number) is
begin
p_n := p_n + 4;
end;
procedure print(p_n number) is
begin
DBMS_OUTPUT.put_line(gv_n);
end;
procedure doit is
lv_n number;
begin
init(lv_n);
proceed(lv_n);
print(lv_n);
null;
end;
function twice(p_n number) return number is
begin
return p_n * 2;
end;
end;
/
Listing 11---
create or replace package pkg_demo as
gk_n constant number := 10;
procedure doit;
end;
/
create or replace package body pkg_demo as
procedure doit is
begin
DBMS_OUTPUT.put_line(gk_n);
end;
end;
/
Listing 12---
create or replace package pkg_demo as
function gk_n return number RESULT_CACHE;
procedure doit;
end;
/
create or replace package body pkg_demo as
function gk_n return number RESULT_CACHE is
begin
return 10;
end;
procedure doit is
begin
DBMS_OUTPUT.put_line(gk_n);
end;
end;
/
Listing 13---
