Skip to Main Content
  • Questions
  • PL/SQL code in packages on DB vs on APEX sites - how it affect performance?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jan.

Asked: December 18, 2019 - 7:43 am UTC

Answered by: Chris Saxon - Last updated: December 18, 2019 - 11:25 am UTC

Category: Application Express - Version: 18.2.0.00.12

Viewed 100+ times

You Asked

Dear Tom,

As you develop in APEX you can move all your PL/SQL code into packages or you can put all on APEX. How this affect performance?

I know that it is better to move code to packages to make quick changes and have more control over code.

Regards
Jan Kowalski

and we said...

There are some PL/SQL performance features that are only available in compiled database code. For example, the result cache:

declare

  l int;
  
  function f ( p int ) 
    return int result_cache as
    retval int;
  begin
    return retval;
  end f;
  
begin
  l := f ( 1 );
end;
/

ORA-06550: line 5, column 12:
PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in anonymous blocks


Shifting PL/SQL as-is from APEX -> database packages may have little or no direct performance benefit. But it does open up tuning possibilities not available if all the code is in your APEX app.

More to Explore

PL/SQL

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