Amazon Contextual Product Ads

Sunday, 15 May 2011

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

No comments:

Post a Comment