This discussion is archived
8 Replies Latest reply: Feb 9, 2009 5:42 AM by robmcangus RSS

PL/SQL Stored Procedure w/ XML Template?

robmcangus Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    634563 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Dave -

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

    Thanks!
    Rob
  • 7. Re: PL/SQL Stored Procedure w/ XML Template?
    634563 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Dave!
    That did it -

    Rob

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points