BI Publisher - Parameter for Functions / Trigger Before

Oracle Business Intelligence, Oracle Warehouse Builder OWB, Oracle Discoverer, BI Publisher, XML Publisher, OLAP, Data Mining, ODI Oracle Data Integrator, etc
Post Reply
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Personal, good morning.

I'm starting in technology, having to develop a report on the BI Publisher tool for the company and, I came across a problem And I would like the opinion of the experts:

Scenario:
I need to use a temp_table to generate my report.

Error:
I can not pass the parameters of my date set to the procedure call at Event_Trigger.

Solution found:
I saw in a forum that, so that the parameter you create on your date set operate, in the Package specs, I am required to have the same parameters, even if without them use.

Doubt:
Do you use this way too, or do some other call? I would like to work on the best practices here.

Thank you,

Trevisolli
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Yes. That's how anyway. It's kind of annoying to make triggers work in BI Publisher. I'vê done countless times and I always have to look at an example to see how it is.

Trevis, I'm not on my pc now. Ask me for Monday to post here an example, beleza?
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Speak dr_gori , blzinha?
quanto time ... I'm sorry for the absence, it's that, I changed my job and, I'm in a rush to learn the Bi Publisher ...

I made some things tb And, I'll be posting some examples ..

But, as you understand much more of this, if you can post here for people ...

Thank you Brother , that hug.

Trevis
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Trévis ... It's like this: To work out you have to follow the letter What I'm going to put below:

In the XML Dead Definition, you need to tell the DefaultPackage and DataSourceRef , see how my header is: (in my case, xg_pkg_ar_002)

Select all

 <?xml version="1.0" encoding="UTF-8"?> 
<dataTemplate name          ="XG_XML_AR_002"  
              description   ="XG - AR - Relatório de Comissões"  
              dataSourceRef ="APPS"  
              defaultPackage="XG_PKG_AR_002"  
              version       ="1.0"> 
<parameters>  
  <parameter name="p_gl_period"       dataType="varchar2" /> 
  <parameter name="p_selecao"         dataType="varchar2" /> 
  <parameter name="p_tipo_comissao"   dataType="varchar2" /> 
  <parameter name="p_repres_numero"   dataType="varchar2" /> 
  <parameter name="p_tipo_ordem"      dataType="varchar2" /> 
</parameters> 
 
<dataQuery> 
 
<sqlStatement name="Q_DATAS"> 
<![CDATA[ 
      select * blablablabla 

Repair that I have multiple parameters. I'll only use 1 on my package, but I need to "refer" all of them, just as it is up there, see how my package spec: (I created variables on the package-specs exactly with the same name as the parameters !! !)

Select all

CREATE OR REPLACE PACKAGE xg_pkg_ar_002 IS 
--      ***************************************************** 
--         !!!!!!!!!!!!!!  IMPORTANTE !!!!!!!!!!!!!!!!!!!   
--      ***************************************************** 
--       Se adicionar mais parametros no XML, deve também   
--             adicionar os mesmos parametros abaixo  
--      ***************************************************** 
 
  p_gl_period         VARCHAR2(60);   --MESMO nome do parâmetro vindo do XML Publisher. 
  p_selecao           VARCHAR2(100);  --não USADO 
  p_tipo_comissao     VARCHAR2(100);  --não USADO 
  p_repres_numero     VARCHAR2(100);  --não USADO  
  p_tipo_ordem        VARCHAR2(100);  --não USADO 
    
 
 
  FUNCTION fnc_xml_before_trigger 
  ( p_gl_period  VARCHAR2 
  ) RETURN BOOLEAN; 
END; 
/
After that, simply put it on the Data-Definition to call the Trigger Before or After.
(if it is after, you need to put after <datastructure>, if you are before, it is before!)

Select all

. 
. 
. 
 
</sqlStatement> 
</dataQuery> 
 
<dataTrigger name="beforeReport" source="XG_PKG_AR_002.fnc_xml_before_trigger( :p_gl_period )" /> 
 
<dataStructure> 
  <group name="G_EMPRESA" source="Q_EMPRESA"> 
    <element name="NOME_EMPRESA"                    value="NOME_EMPRESA" /> 
  </group> 
  <group name="G_DATAS" source="Q_DATAS"> 
    <element name="DTINI"                           value="DTINI" /> 
. 
. 
.
Ready! If you do according to these steps, it has to work!

: - D
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Speak my king, blzinha? I hope everything in order ...

Then Brother, thank you even for the tips, once again ...

For what I realized, This tip that passed, is Pro publisher 10 ???

is that I realized the process here at 11 and, I will only leave documented below as I did, for those who need (and see if I did right rs ...):

99] In the Data Model, in the field shortly after the Source Data, which is Default Package , I informed the name of my PCK (which contains the parameters in the spec, as the same name declared in Publisher:

Select all

 
Oracle DB Default Package: PCK_UTIL 
After this step, automatically, when it is going to create a Event Trigger , the package appears on the "Available Functions" / b] For you to use.

That way, you can use it by passing the required parameters (on the right side, "Event Trigger"), following the premise that the Parameters must be at the Package spec.

Important notes:
  • The parameters must have the same name, and even not being used, should be at the Package spec, [/ list]
    • The list of objects that will appear, will contain only FUNCTIONS and, these must, Atatorily, returning a Boolean; [/ list]

      is there the tip of how I proceeded here and, I will be putting more information in the forum, for your evaluation.

      Thank you again.

      Great embrace,

      Trevis

Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 6 guests