8 Replies Latest reply: May 7, 2013 12:49 AM by Kedar Gupte RSS

    Using XML based output of rq*Eval() for generating graphical views

    Kedar Gupte
      I've been trying the standard example used in training manuals of generating RandomRedDots.
      (Scripts that donot involve images are working fine)
      While using rqTableEval() the output format
      1. using select clause (select cast(value as longvarchar(32000)) from dual)
      2. 'png'
      is throwing ORA-29400: data cartridge error
      ORA-24333: zero iteration count
      ORA-06512: at "RQSYS.RQTABLEEVALIMPL", line 7
      ORA-06512: at line 4

      3. 'xml' type is generating output as desired with two columns name, value.
      When this xml query is used in physical table with columns - name as int, value as longvarchar(32000) as output is clob ,
      the execution(view data) results as 'no data'.

      Can i get help in how to utilize this xml output so that graphs can be made available in obiee dashboard ultimately.
      Thanks
        • 1. Re: Using XML based output of rq*Eval() for generating graphical views
          Denis Mukhin-Oracle
          Please paste your queries and R scripts.

          Thanks,
          Denis
          • 2. Re: Using XML based output of rq*Eval() for generating graphical views
            Kedar Gupte
            Hi,

            R script to be executed is as follows:

            begin
            sys.rqScriptDrop('Example6');
            sys.rqScriptCreate('Example6',
            'function(n){
                 res<-1:10
            plot( 1:n, rnorm(n), pch = 21,
            bg = "red", cex = 2 )
                 res
            }');
            end;
            /
            set long 20000
            set pages 1000

            select id,image
            from table(rqTableEval(
            cursor(select 100 n from dual),
            NULL,
            'PNG',
            'Example6'));

            ERROR message:
            select id,image
            *
            ERROR at line 1:
            ORA-29400: data cartridge error
            ORA-24333: zero iteration count
            ORA-06512: at "RQSYS.RQTABLEEVALIMPL", line 7
            ORA-06512: at line 4

            when tried with xml as output format since 'png' was not working

            select name,value
            from table(rqTableEval(
            cursor(select 100 n from dual),
            NULL,
            'XML',
            'Example6'))

            there is xml output in sqlplus

            NAME
            --------------------------------------------------------------------------------
            VALUE
            --------------------------------------------------------------------------------
            <root><R-data><vector_obj> <ROW-vector_obj><value>1</value></ROW-vector_obj><ROW
            -vector_obj><value>2</value></ROW-vector_obj><ROW-vector_obj><value>3</value></R.......so on

            How to utilize this output in physical layer of rpd, in table with select clause
            I tried select dbms_lob.substr(value,100000,1) to convert the clob of xml to varbinary but then there is no output in sqlplus.
            If the above is used as select clause for physical table it throws ORA:00904 "value" invalid identifier.

            Can I please get help in getting this output in physical layer tables and eventually to presentation layer.
            Have i missed something or some configuration needs to be done for acquiring the images
            (ps: this is occurring for images and scripts involving number type are working fine.
            I've included png and other packages in RProfile.site file)
            • 3. Re: Using XML based output of rq*Eval() for generating graphical views
              Denis Mukhin-Oracle
              Do you see base64 encoded image in the XML output? Have you installed ORE supporting packages on the DB server (especially the png package)?
              • 4. Re: Using XML based output of rq*Eval() for generating graphical views
                Mark Hornick-Oracle
                PNG output is supported in OBIEE 11.1.1.7.

                What version of ORE and OBIEE are you using?

                XML output must be consumed via Oracle BI Publisher, where you create an RTF template that is loaded into an OBIEE dashboard - not via RPD. The PNG result is a more direct alternative.

                For an example using PNG output, see the following slide deck: http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/ore-trng5-operatnlzgrscripts-1501640.pdf

                Are you able to execute this script from R using ore.tableApply?

                Mark
                • 5. Re: Using XML based output of rq*Eval() for generating graphical views
                  Kedar Gupte
                  Hi Denis,

                  yes there is base64 encoding in xml output
                  <image><img src="data:image/pngbase64"><![CDATA[iVBORw0KGgoAAAANSUhEUgAA....
                  However xml here is of little help as xml output is consumed via BI publisher and obiee analysis is desired as the output.

                  Yes png package has been installed and i've included it in RProfile.site
                  library(ORE)
                  library("png", lib.loc="C:/Program Files/R/R-2.13.2/library")

                  I'm using obiee 11.1.1.7.0, ore 1.1 and R 2.13.2
                  Supporting ,client and server packages are also of v1.1

                  Can i please get help in getting the png output working.

                  Thanks,
                  Kedar
                  • 6. Re: Using XML based output of rq*Eval() for generating graphical views
                    Kedar Gupte
                    Hi,

                    Thanks for pointing out that xml can consumed by BIP as obiee analysis is the desired output so getting png working is the motto.

                    I did try the same example as in training pdf, it is resulting in same error
                    select id,image
                    *
                    ERROR at line 1:
                    ORA-29400: data cartridge error
                    ORA-24333: zero iteration count
                    ORA-06512: at "RQSYS.RQTABLEEVALIMPL", line 7
                    ORA-06512: at line 4

                    Tried using the plot function using ore.Eval function and it works in the R console. In table called job_roles there are id, shortfall columns which i intended to plot and the data is retrieved.
                    mod <- ore.doEval(
                    function(){
                    ore.sync()
                    ore.attach()
                    dat <- ore.pull(JOB_ROLE)
                    plot(dat$SHORTFALL,dat$ID)
                    dat
                    });
                    mod_local <- ore.pull(mod)
                    mod_local
                    plot(mod_local$ID, mod_local$SHORTFALL)
                    mod_local
                    ID JOB_ROLE COUNT_OF_ATTRITION SHORTFALL
                    1 1 Welder 470 719
                    2 2 Lathe Operator 122 272
                    ......

                    No idea why the png output format is not working. I've included the following packages in RProfile.ste
                    library(ORE)
                    library("png", lib.loc="C:/Program Files/R/R-2.13.2/library")

                    I'm using obiee 11.1.1.7.0, ore 1.1 , R 2.13.2, oracle 11g 11.2.0.3.0 patchset and hence didnot have any externally applied patches to database
                    Supporting ,client and server packages are also of v1.1

                    Can i please get help in getting the png output format operational.

                    Thanks,
                    Kedar
                    • 7. Re: Using XML based output of rq*Eval() for generating graphical views
                      Denis Mukhin-Oracle
                      Kedar,

                      Support for PNG output was added in ORE 1.3. In ORE 1.1 you can only get XML output.

                      Denis
                      • 8. Re: Using XML based output of rq*Eval() for generating graphical views
                        Kedar Gupte
                        Thanks Denis, never thought of giving a look to version numbers. ore 1.3 is yielding png output.