Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 27 September 2017

How to Use Oracle SQL Functions in BI/XML Publisher Reports



XML Publisher has extended a set of SQL and XSL functions for use in RTF templates. The syntax for these extended functions is

for extended SQL functions <?xdofx:expression?>

for extended XSL functions<?xdoxslt:expression?>

Example of Extended SQL Functions

<?xdofx:decode(’xxx’,’bbb’,’ccc’,’xxx’,’ddd’)?>
<?xdofx:Instr(’abcabcabc’,’a’,2)?>
<?xdofx:substr(’abcdefg’,2,3)?>
<?xdofx:to_number(’12345’)?>
<?xdofx:replace(name,’John’,’Jon’)?>


Share this article :

3 comments:

  1. Hello There,


    Great piece on How to Use Oracle SQL Functions in BI/XML Publisher Reports, I’m a fan of the ‘flowery’ style Looking forward to more long form articles ??
    Please help with the below query.

    Requirement: To track DML changes on multiple tables and to populate its consolidated records in one master table by having triggers on the MLOG tables.

    Action : I have created triggers on MLOG tables, few of them works and few are failed.

    Query: Need suggestion, Is it advisable to have triggers on MLOG$_TABLES (Materialized view log tables ), If yes , Is there any restriction to have so.

    Please keep providing such valuable information.


    Many Thanks,
    Sindu

    ReplyDelete
  2. Hello,


    #Topic being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.

    I am trying to use this in a materialized view and got below error:


    SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query

    12015. 00000 - "cannot create a fast refresh materialized view from a complex query"

    *Cause: Neither ROWIDs and nor primary key constraints are supported for

    complex queries.

    *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE

    option or create a simple materialized view.


    select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J

    LEFT JOIN MV_INST_LOB_R IR ON

    (IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)

    WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL

    UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;

    Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
    where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2


    Can you help me in converting to simple query, so that I can use simple materialized view.

    Follow my new blog if you interested in just tag along me in any social media platforms!

    morgan

    ReplyDelete
  3. Hi There,

    11/10!! Your blog is such a complete read. I like your approach with How to Use Oracle SQL Functions in BI/XML Publisher Reports . Clearly, you wrote it to make learning a cake walk for me.

    I have two tables A and B. Where A is a parent table and B is a child table.
    I need all the records from table A that is not present in table B.

    I would like to know the best way in terms of performance to achieve this. (I have millions of records in the tables)

    But nice Article Mate! Great Information! Keep up the good work!

    Thank you,
    Preethi.

    ReplyDelete