Solved

Can I retrieve the where clause Oracle generates from the Enter/Execute query buttons

Posted on 2009-05-05
8
2,211 Views
Last Modified: 2013-12-18
When a user goes into 'Enter Query' mode, enters some form criteria, and then presses 'Execute Query', is there a way to retrieve the where clause Oracle generates against the data block?
0
Comment
Question by:iamlooper
  • 4
  • 3
8 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24306431
Oracle doesn't use a where clause against the data block, the where clause is against the objects (the sql execution engine / optimizer) but as far as retrieving the SQL the user submitted, you can find it multiple ways (besides if Oracle forms gives you an option, as I haven't done forms in years).

1) You can use the V$SQLAREA manually to check queries
2) You can use general auditing
3) You can use FGA (Fine Grained Auditing) against the specific tables or schemas or even for specific users

V$SQLAREA is a view that will contain all SQL being run in the instance, and requires no setup. (select sql_text from v$sqlarea) or if the sql is larger than 1k, use sql_fulltext field which is a clob.

FGA requires a bit of setup, but its eaasy and is my preferred route. If you want to go that route, I can provide info on how to set it up with 10g and up.

As to a Form's specific option, I will let a Forms guru add that as I am not sure.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24306447
See the bottom of this thread where we provide examples of setting up FGA to audit an object

http://www.experts-exchange.com/Database/Oracle/Q_24376940.html
0
 

Author Comment

by:iamlooper
ID: 24306893
Thanks mrjoltcola. Let me clarify the reason I want to capture the users query condition. Very simply, if the user queries some records out using Enter_Query and Execute_query, I want to be able to re-execute the form at a later time and retrieve the same records.  

I have a text box on the form where a custom where clause can be typed in, and then executed to 'Filter' the form records. I also want to give the user the option to use Oracles built in Query-By-form tools (Enter_Query / Execute_Query) to filter the records. Ideally I would like to populate the custom where clause text box on the form with the results of the Enter_Query. I can then re-execute the same query condition whenever needed.

The ultimate reason I want to be able to store the where condition is because when the user is looking at a record on the form, they may choose to run some updates using server side procedures, which change the current record being displayed. The only way I know of to get the form to reflect the external changes made to the record, is to save the current block record number, re-execute the query, then go back to the saved record number. If the user had used ENTER_QUERY, then re-executing the form retrieves all records with no filter condition, and the record number is no longer valid. I want to re-execute with the same ENTER_QUERY clause used initially.

If there is a way to refresh the current form record being displayed without re-executing the data block, then that would be a useful solution. Although I would still love to capture the Enter_Query condition to populate the custom where clause text box. I investigated using KEY_ENTQRY and KEY_EXEQRY a while back to see if I could intercept what the user had entered so that I could build a where clause up myself, but could'nt seem to get it working. At the time I thought that I was very close, but maybe just missing a key point somewhere.

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24307507
So the Oracle form interface and the service side procedure are running in different sessions?

Why can't you integrate those procedures into your form? Are they PL/SQL?

Otherwise, there really is no trick to have the form reload changes from a particular record, you have to reissue a query, however, not knowing forms too well, I cant offer more advice, I'm sorry. I will leave this for a Form guru.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:iamlooper
ID: 24307976
They are PL/SQL procedures in a server side package. Even if I moved the procedures to the form I would still have the same issue I think. The table behind the form would still get updated, and the form would still need to be requeried.

Once the user presses the execute query button, there must be a way to intercept what the user entered into the form during the enter_query session. I seem to remember when I looked into this a while back (like a year ago), that you could iterate through the form objects looking for text the user entered? This wuold be the best solution for me if it is indeed possible.

Thanks mrjoltcola. Hopefully a form guru will pick this up and there's a 'OMG that's so simple' solution.
0
 
LVL 12

Accepted Solution

by:
jwahl earned 500 total points
ID: 24311677
did you try the forms built-in GET_BLOCK_PROPERTY('BLOCK_NAME', LAST_QUERY) ?
0
 

Author Comment

by:iamlooper
ID: 24315869
OMG!!!!!!!

That works beautifully!! So simple too. On the KEY_EXEQRY trigger I simply parse out the WHERE clause from the last_query text string.

That you SO MUCH jwahl !!!


DECLARE
 

  v_last_query      VARCHAR2(2048);

  v_where_start_pos NUMBER(4,0);

  v_order_start_pos NUMBER(4,0);

  v_where_clause    VARCHAR2(1024);
 

BEGIN
 

  go_block('booking');
 

  count_query;
 

  execute_query;
 

  v_last_query      := get_block_property('booking',last_query);

  v_where_start_pos := instr(v_last_query,'WHERE');
 

  IF v_where_start_pos = 0

  THEN

    v_where_clause := null;

  ELSE

    v_where_start_pos := v_where_start_pos + 6;

    v_order_start_pos := instr(v_last_query,'order by');

    IF v_order_start_pos = 0

    THEN

      v_where_clause := substr(v_last_query,v_where_start_pos);

    ELSE

      v_where_clause := substr(v_last_query,v_where_start_pos,v_order_start_pos - v_where_start_pos);

    END IF;

  END IF;
 

  where_clause.other_where_clause := v_where_clause;
 

END;

Open in new window

0
 

Author Closing Comment

by:iamlooper
ID: 31578088
I can't believe I've been stewing over this for a year! That will teach me to go to the exchange sooner. I thank you again !!!!
btw the last line of the code snippet is missing the block colon. Should be..
:where_clause.other_where_clause := v_where_clause;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now