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

Vincelogic

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 ?

HamidHelal

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

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

Zlatko Sirotic

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

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

Zlatko Sirotic

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

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

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.

InoL

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

Thanks for your replies, guys.

Vincelogic

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