Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using compile time constants in PL/SQL

JackKJul 9 2022 — edited Jul 9 2022

Hello All!
I am wondering if Oracle supports something like this...
Suppose I need to use repetitive code in my package. The code uses several variables (few input parameters from procedure/function and few output parameters).
I would like to write the repetitive code as a compile time constant which the compiler will put into my code in the place I need it. This would save me some time to write the code.
Example:

create or replace package body my_test_pkg is
  procedure get_proper_nums(p1 number := null, p2 number := null, o1 out number, o2 out number) is
begin
  o1 := p1;
  o2 := p2;
  if o1 is null then
    o1 := 0;
  else
    o1 := o1 + 1;
  end if;
  if o2 is null then
    o2 := 0;
  else
    o2 := o2 * 10;
  end if;
end get_proper_nums;

procedure my_proc1(p1 number, p2 number) is
  n1  number;
  n2 number;
begin
  get_proper_nums(p1, p2,  n1, n2);  --***  this is the line of code I'd like to not write like this
end my_proc1;
end;

Instead of the line marked with *** I'd like to have some precompile constant, something like:
$$C_proper_nums := 'get_proper_nums(p1, p2, n1, n2);'
and then write $$C_proper_nums instead of my original like (with ***). I wish the compiler will put my constant the this place and compile such generated code.
Does Oracle 19c support this?
Cheers,
Jacek

Comments
Post Details
Added on Jul 9 2022
6 comments
562 views