Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

How to get SQL of a LOV record group at runtime ?

Vincelogic
Vincelogic Member Posts: 31 Bronze Badge
edited Jun 4, 2015 4:53AM in Forms

I would like  to show the SQL of current item's LOV record group, but seems that there is no such built-in like "GET_RECORD_GROUP_PROPERTY", any other way to get the SQL text of it ?

Tagged:
Vincelogic

Answers

  • HamidHelal
    HamidHelal Member Posts: 3,524 Gold Trophy
    edited Apr 10, 2015 5:36AM

    Is this helps ?

    Syntax SYSTEM.LAST_QUERY

    Description SYSTEM.LAST_QUERY represents the query SELECT statement that Oracle Forms most recently used to populate a block during the current Runform session. The value is always a character string.

  • Vincelogic
    Vincelogic Member Posts: 31 Bronze Badge
    edited Apr 10, 2015 5:43AM

    Thanks HamidHelal, I wondered that previously, but unfortunately SYSTEM LAST_QUERY only stores the SQL query against blocks, not for LOV

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Apr 10, 2015 8:28AM

    If your SQL query (in Record Group Query property) is static, then you can remember it in a VARCHAR2 variable (and display it at run time).

    If it is dynamic, ie. made with POPULATE_GROUP_WITH_QUERY built-in, then you can remember that query.

    Regards,

    Zlatko

  • Vincelogic
    Vincelogic Member Posts: 31 Bronze Badge
    edited Apr 12, 2015 4:25AM

    Thanks Zlatko Sirotic, my case is static, and how to get it at runtime ?

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Apr 12, 2015 5:03AM

    Suppose your Record Group Query property is "SELECT deptno, dname FROM dept".

    Then you can create eg. packaged constant:

    PACKAGE pack IS

      query_c CONSTANT VARCHAR2(1000) := 'SELECT deptno, dname FROM dept';

    END;

    and then display it in a given time.

    I do not know if this is what you wanted!?

    Regards,

    Zlatko

    Vincelogic
  • Vincelogic
    Vincelogic Member Posts: 31 Bronze Badge
    edited May 27, 2015 12:20AM

    My purpose is to get the SQL in the LOV which is already defined at design time statically (means maybe a legacy or others people's program and I am not able to re-engineer them), I tried to search the parameters of "GET_LOV_PROPERTY" and seems it only gives the Record Group Name but there is nothing like "GET_RECORD_GROUP_PROPERTY" to get the SQL of that Record Group, so what I would like to know is : would there be any other way to fulfill this ?

  • Pk
    Pk Member Posts: 712 Silver Badge
    edited May 28, 2015 7:59AM

    Open the Oracle form and check the record group of the required LOV. You can get the SQL from there.

    If you are checking for certain condition, then check the triggers in the form if a different record group is getting assigned dynamically.

    By the way which form are you looking at.

    Vincelogic
  • InoL
    InoL Member Posts: 9,444 Gold Crown
    edited May 28, 2015 3:12PM

    If you have no access to the source code, you can use SQLDeveloper or TOAD to monitor the session in the database. As soon as a user clicks the LOV, look in the session for the current statement.

    I use this technique quite often to see the actual query of an LOV. However, you need DBA access to the database to do this (or your DBA can give you specific grants to be able to do this).

    Vincelogic
  • Vincelogic
    Vincelogic Member Posts: 31 Bronze Badge
    edited Jun 4, 2015 4:41AM

    Thanks for your replies, guys.

  • Vincelogic
    Vincelogic Member Posts: 31 Bronze Badge
    edited Jun 4, 2015 4:53AM

    Thanks, PK, but I need to do that in the runtime.

This discussion has been closed.