Creating "Value Set" for the framework Documents - Oracle Application

Ok. So here is the task. I need to create a "Value Set" of Framework documents. So far so good, bacause I have a nice package called jdr_utils with a nice function "listDocuments".

But the problem is that procedure outputs the results into dbms_output.put_line and "Value Set" can be based on  table or view only.

So what do I need to do. Read the output of dmbs_output, and insert it into some table or view

Step 1: Reading from dbms_output.

Well, reading from  dbms_output in not so tricky, bacause we have a function called dbms_output.get_lines. Here is the code:

declare
 l_outtab dbms_output.chararr;
 l_fetchln INTEGER := 90000;
begin
 jdr_utils.listDocuments(p_document_prefix, true);
 dbms_output.get_lines(l_outtab, l_fetchln);
end;

using this code l_outtab array holds all output lines and l_fetchln holds the number of fetched lines

 

Step 2: Creating view

Now we need to build a view, which holds lines from  l_fetchln array. For this, we will use pipelined function.

First we need to create the types for our pipelines function

create or replace type XXPCK_JDR_DOCUMENTS_ITEM as object
(
document varchar2(400)
)
/
create or replace type JDR_OEF_DOCUMENTS_TAB
as table of JDR_OEF_DOCUMENTS_ITEM 

now lets modify the the script above to loop over result array and pipe each row

create or replace function Get_Oef_Documents(p_document_prefix varchar2) return JDR_OEF_DOCUMENTS_TAB pipelined is
  l_outtab dbms_output.chararr;
  l_fetchln INTEGER := 90000;
begin
  jdr_utils.listDocuments(p_document_prefix,true);
  dbms_output.get_lines(l_outtab, l_fetchln);
--read from standart output
  FOR i IN 2 .. l_fetchln LOOP
    pipe row (JDR_OEF_DOCUMENTS_ITEM(document => l_outtab(i)));
  END LOOP;
    return;
end Get_Oef_Documents; 

However, according to the company policy, it if forbidden to compile functions and procedurs in the apps schema. No problem!!!. We will compile it under JDR schema. The following Metalink note holds

the list of all grants and synonyms that we need to create in order to use jdr_utils in non apps schema Note:399780.1.

The only thing that should be done after this, is giving grants on our function to apps and create a synonym

• 
  
  grant all on get_oef_documents to apps
  
  
• 
  
  create synonym app.get_oef_documents for jdr.get_oef_documents

Finally we can create a view in the apps schema

create or replace view jdr_oef_documents as
select document
from table(Get_Oef_Documents('/')). 

That is all. Now we can use this view as a source for our "Value Set" inside Oracle Application