Comments

  • Fernando Ponte

    Hi Donald.

    No worries. It was an interesting learning. Never thought about using Function on WITH clause in BIP. 

    An old workmate once told me while running SQL on different tools.

    "We have two pieces of software talking with the database. They make not talk the same way all the time"

    Cheers

    Fernando.

  • Fernando Ponte

    Hi Donald.

    Forgot about some options in the Data Model.

    The following works.

    Type of SQL: Non-Standard SQL.

    I used this code in my Data Model just to test.

    WITH  FUNCTION GetValue1 (p_value VARCHAR2) Return VARCHAR2 IS
                      CURSOR c1 IS
                           SELECT 'aa' aa FROM dual WHERE p_value = 'ABC';
          
                  cc VARCHAR2(100);
                BEGIN
                     FOR x IN c1
                     LOOP
                          cc := x.aa;
                     END LOOP;
                     RETURN cc;
                EXCEPTION
                      WHEN OTHERS
                      THEN
                          RETURN 'Error GetValue1: '|| p_value;
                END GetValue1;
    ------------
    --Main Query
    ------------
    SELECT GetValue1('ABC') GetValue
    FROM   DUAL


    Cheers

    Fernando

     

     

     

  • Fernando Ponte

    Hi Donald.

    I would transfer the function to the database instead of being part of the WITH block. Especially if you plan to reuse it.

    I like the challenge so I will do some tests too. So far I don't know an answer for the workaround in the Data Model. Hope someone else has the same issue and come with some ideas.

    Cheers

    Fernando

  • Fernando Ponte

    Hi Donald

    I think the first error was because of the following part in your original code.

    FOR x IN c1
                     LOOP
                          cc := x.aa;
    END LOOP;
    RETURN cc;

    [2019-11-18T07:54:45.258+00:00][dp id:463169166][sch info:]Bind Variables ...
    [2019-11-18T07:54:45.258+00:00][dp id:463169166][sch info:]    1: :null
    [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]XMLGEN:Exception while processing datamodel...:__ora_fusion_user__temp_81910071_8fb6_4741_9ac3_042425fc62e8_xdm
    [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]Process Cancelled Stage:false
    [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]XMLPGEN:Invalid column index
    [2019-11-18T07:54:45.259+00:00][dp id:463169166][sch info:]java.sql.SQLException: Invalid column index

    If I'm not wrong when you use ":" in BI Publisher it will expect a Parameter right? That is why if failed exactly on that validation.

    Once you removed in the second example the Error became a Warning in the Engine Log 2.

    Cheers

    Fernando

  • Fernando Ponte

    Hi Donald.

    The only weird thing I found in the engine log was.

    [dv id:1293682950]Validation code:DM08_COLUMN_ALIAS_LENGTH
    [dv id:1293682950]Error Type:WARNING
    [dv id:1293682950]Description:Selected column length name exceeds the limit of 15. Length of the column name must not be more than 15 chars. Use short alias for column names. 
    [dv id:1293682950]Details:Column name / alias:   REMARKS
    ----------------------------------------------------------------------
    polines.po_header_id      length exceeds limit of 15 chars.
     
    [dv id:1293682950]==================================
     
    Here you find more information about the process of Data Model validation.

    Just to remove that warning can you add an alias to your "polines.po_header_id"

    Just another note. Can you modify the with block to the construction below.

         PO AS
         (SELECT Segment1 FROM po_headers_all)
    ,    POLines AS
         (SELECT PO_Header_Id FROM po_lines_all)

    Cheers

    Fernando

  • Fernando Ponte

    Hi Donald.

    This is just me and my standards, so ignore this first  lines if you want. There is one thing that always bugs me when I see some Data Models. It is the "SELECT * FROM table". It may not be the your case but, if something change in the table structure your Data Model will be affected and you are selecting more than you need.

    Does this work fine on SQL Developer? I believe it will so I think it is something related to the way BI Publisher interacts with the database.

    Can you download the Engine Log from the data model for more details and share with us.

    Are you able to add just the columns you need on those two WITH block PO and POLINES and test again?

    Cheers

    Fernando

  • Fernando Ponte

    Hi.

    Does this started happening after and update in the report or started happening with no changes applied? It can be the Report Template got "corrupted" in some way or a formatting was changed?

    Are you able to create a new Report template on the same report (not duplicate the current one) and check the export again?

    Cheers

    Fernando

  • Fernando Ponte

    Hi Srekan.

    Yes, there is.

    Navigate to your Report Template, click on the table that contains your data and on the Table tab options change the value on "Rows to Display" (see image).

    If you are using a PivotTable, I don't think that is possible.

    Cheers

    Fernando

  • Fernando Ponte

    Hi Srekan.

    Looks like you didn't get any reply on this.

    It is a RTF template you are working with?

    Have a look at the following options.

    https://docs.oracle.com/cd/E80149_01/bip/BIPRD/GUID-F8B5ACA1-6054-4028-8AC4-2BC43E12332F.htm#BIPRD2583

    Cheers

    Fernando

  • Fernando Ponte

    Hi Subrahmanyan.

    Unless somebody comes with a workaround, this is not possible.

    I assume the sFTP is external to your network right?

    Cheers

    Fernando

  • Fernando Ponte

    Hi Ibrahim.

    My two cents.

    You may try to create your own for the Desktop version.

    https://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/dvdesktop/viz_plugin/dvd_sdk.html

    Cheer

    Fernando

  • Fernando Ponte

    Hi Duncan.

    Looks like the objective of this report is to export data using DV? Please correct me if I'm wrong.

    If that is the case please look at this.

    https://docs.oracle.com/en/cloud/paas/bi-cloud/bilug/write-visualization-data-csv-or-txt-file.html

    If it is not I'm not sure what the user is trying to see with this amount of records in a table.

    Please let us know what is the business use in this case. Other people may have better ideas or approach.

    Cheers

    Fernando

  • Fernando Ponte

    Hi/

    You can also just log into you cloud database and run a select on v$version view.

    Cheers

    Fernando

  • Fernando Ponte

    Hi Chethana.

    This may help.

    https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/configure-notifications.html#GUID-885DF71A-6873-4B86-8EC5-771BC667EDDE

    Cheers

    Fernando

  • Fernando Ponte

    Hi Zunaira

    Please have a look on how to get more details from Oracle DV Desktop on this post.

    https://blogs.oracle.com/analyticscloud/4-steps-to-oracle-data-visualization-diagnostics-support

    Best Regards

    Fernando Ponte