8 Replies Latest reply: Feb 9, 2009 7:42 AM by robmcangus RSS

    PL/SQL Stored Procedure w/ XML Template?

    robmcangus
      Is it possible to take a store PL/SQL procedure and create an XML report from it?

      Thanks,
      Rob
        • 1. Re: PL/SQL Stored Procedure w/ XML Template?
          nagornyi
          Almost certainly, the short answer is yes. For further help, please provide more details on what you need to do.
          • 2. Re: PL/SQL Stored Procedure w/ XML Template?
            524753
            Hey Rob,

            Re: Stored procedures and dynamic columns

            l want to use the pl/sql package in datatemplate,
            Yes, we can use them in different ways, by using the oracle table function or Pipelined Table Functions.

            Pl/sql package will return the collection variable , which you can cast it as table in the query..
            sample

            create type numset_t as table of number;
            /

            create function f1(x number) return numset_t pipelined is
            begin
            for i in 1..x loop
            pipe row(i);
            end loop;
            return;
            end;
            /
            select * from table(f1(3));

            COLUMN_VALUE
            1
            2
            3

            In the datatemplate , you can just call the

            select * from table(f1(3));

            Or even the parameter can be passed to the function, in which your entire logic can be written, which will return the collection variable.

            Datatemplate wil be more powerful , when used like this,
            • 3. Re: PL/SQL Stored Procedure w/ XML Template?
              robmcangus
              well I have a normal select statement but to use one of the packages I have to set a variable: exec Pay_Balance_Pkg.set_context ('TAX_UNIT_ID', 81)

              I tried to put that in front of the select statement and I also tried to just put a separate sqlStatement but I get the same error - Invalid SQL Statement

              <sqlStatement name='R1'>
              <![CDATA[execute Pay_Balance_Pkg.set_context ('TAX_UNIT_ID', 81)]]>
              </sqlStatement>


              thanks ,
              Rob
              • 4. Re: PL/SQL Stored Procedure w/ XML Template?
                BarnesDJ
                Need to know more about what you are using for data source. (e.g. rdf, datatemplate, pl/sql etc.) but assuming datatemplate then execute your package in the beforereport trigger.

                Cheers,
                Dave

                P.S. Same for rdf.
                • 5. Re: PL/SQL Stored Procedure w/ XML Template?
                  robmcangus
                  This is the data template:

                  <?xml version="1.0" encoding="UTF-8"?>
                  <dataTemplate name='gisdtrsecre' defaultPackage='gisd_test_pkg'>
                  <properties>
                  <property name="scalable_mode" value="on"/>
                  </properties>
                  <parameters>
                  <parameter name='p_ssn' datatype='character'/>
                  <parameter name='p_date_lo2' datatype='date'/>
                  <parameter name='p_date_hi2' datatype='date'/>
                  </parameters>
                  <dataQuery>
                  <sqlStatement name='Q1'>
                  <![CDATA[SELECT DISTINCT papf.full_name,
                         papf.employee_number,
                         papf.national_identifier,
                         paaf.assignment_id,paa.assignment_action_id,
                         ppa.effective_date,
                         Pay_Balance_Pkg.get_value(Hr_Us_Reports.get_defined_balance_id('TRS DEPOSIT Eligible Comp', '_ASG_GRE_RUN',81),paa.assignment_action_id) trs_eligible_comp,
                         Pay_Balance_Pkg.get_value(Hr_Us_Reports.get_defined_balance_id('Gross Earnings', '_ASG_GRE_RUN',81),paa.assignment_action_id) gross_earnings
                    FROM pay_payroll_actions ppa,
                         pay_assignment_actions paa,
                         per_all_assignments_f paaf,
                         per_all_people_f papf
                  &p_where7
                    ORDER BY paaf.assignment_id,ppa.effective_date]]>
                  </sqlStatement>
                  </dataQuery>
                  <dataTrigger name='before_report7' source='gisd_test_pkg.before_report7'/>
                  </dataTemplate>
                  • 6. Re: PL/SQL Stored Procedure w/ XML Template?
                    robmcangus
                    Dave -

                    as for the RDF - what should I paste in here?

                    Thanks!
                    Rob
                    • 7. Re: PL/SQL Stored Procedure w/ XML Template?
                      BarnesDJ
                      As you have a datatemplate the rdf is irrelevant.

                      Just call your pl/sql package in your before report trigger.

                      Cheers,
                      Dave
                      • 8. Re: PL/SQL Stored Procedure w/ XML Template?
                        robmcangus
                        Thanks Dave!
                        That did it -

                        Rob