Generate PL/SQL API Calls
Code to generate an API call for a given package and procedure or function
function generate_api_calls
( pv_package_name in all_arguments.package_name%type
, pv_owner in all_arguments.owner%type
, pv_object_name in all_arguments.object_name%type := '%')
return varchar2 is
cursor cur_api( pv_package_name in all_arguments.package_name%type
, pv_owner in all_arguments.owner%type
, pv_object_name in all_arguments.object_name%type) is
select object_name
, overload
, position
, sequence
, argument_name
, data_type
from all_arguments
where package_name = upper(pv_package_name)
and owner = upper(pv_owner)
and object_name like upper(pv_object_name)
and data_level = 0
order by object_name, overload, position, sequence;
lv_object varchar2(64);
lv_return_value varchar2(32767);
ln_loop_counter pls_integer;
begin
lv_object := 'First2Diff';
lv_return_value := '';
ln_loop_counter := 0;
for c_rec in cur_api( pv_package_name => pv_package_name
, pv_owner => pv_owner
, pv_object_name => pv_object_name) loop
ln_loop_counter := ln_loop_counter + 1;
if (lv_object = 'First2Diff'
and c_rec.position = 0) then -- it's a function
lv_object := c_rec.object_name || c_rec.overload;
lv_return_value :=
'l_'||lower(c_rec.data_type)||' := '||
lower(pv_package_name||'.'||c_rec.object_name)||chr(10);
elsif(lv_object = 'First2Diff'
and c_rec.position != 0) then -- it's a procedure
lv_object := c_rec.object_name || c_rec.overload;
lv_return_value :=
lower(pv_package_name||'.'||c_rec.object_name)||chr(10);
if c_rec.argument_name is not null then
lv_return_value:= lv_return_value
||' ( '||lower(c_rec.argument_name)||' => '||chr(10);
end if;
elsif (lv_object = c_rec.object_name || c_rec.overload
and c_rec.argument_name is not null) then -- it's the next arg
if c_rec.position = 1 then -- it's the first arg of a function
lv_return_value := lv_return_value
||' ( '||lower(c_rec.argument_name)||' => '||chr(10);
else
lv_return_value := lv_return_value
||' , '||lower(c_rec.argument_name)||' => '||chr(10);
end if;
elsif (lv_object = c_rec.object_name || c_rec.overload
and c_rec.argument_name is null) then -- it's a list argument
null;
elsif (lv_object != c_rec.object_name || c_rec.overload
and c_rec.position = 0) then -- it's a function
lv_object := c_rec.object_name || c_rec.overload;
lv_return_value := lv_return_value
||' );'||chr(10)||
'l_'||lower(c_rec.data_type)||' := '||
lower(pv_package_name||'.'||c_rec.object_name)||chr(10);
else -- it's a procedure
lv_object := c_rec.object_name || c_rec.overload;
lv_return_value := lv_return_value
||' );'||chr(10)||
lower(pv_package_name||'.'||c_rec.object_name)||chr(10)
||' ( '||lower(c_rec.argument_name)||' => '||chr(10);
end if;
end loop;
if ln_loop_counter = 0 then
lv_return_value := 'No API found';
elsif ln_loop_counter = 1 then
lv_return_value := lv_return_value ||';'||chr(10);
else
lv_return_value := lv_return_value ||');'||chr(10);
end if;
return lv_return_value;
end generate_api_calls;