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;