1 Reply Latest reply: May 9, 2013 5:35 PM by CraigB RSS

    execute_query slow

      I am new to oracle forms,

      Please let me know what do_key('execute_query') does, I have a button when-button-pressed trigger on it

      when I clicke the button, data populates,,,when someone says execute query is slow, how can I fix it ? there are multiple tables involved in the form, how do I know which table is slow
        • 1. Re: execute_query slow
          Oracle Forms is tightly integrated with the Oracle Database. Consequently, you can create what is called a Base Table Data Block in a Form that is based on a Table in your database. When Forms executes the "EXECUTE_QUERY" built-in, it will query all of the records in the table and display them - on by on - in the Base Table Data Block. This is makes displaying data in a Form much easier because you don't have: 1) to write the SQL to fetch the record, 2) write code to execute the SQL and retrieve the result set and 3) write code that displays each record in your User Interface (UI). The Data Block also has properties which allow you to filter and order your result set. These are the WHERE clause and ORDER by properties. You can set these properties at design time or you can modify them using Forms Built-ins [(SET_BLOCK_PROPERTY('BLOCK_NAME', DEFAULT_WHERE, 'WHERE clause...')].
          Please let me know what do_key('execute_query') does
          The DO_KEY() built-in executes the named function key. In this case - Execute Query. This is differnt from calling the EXECUTE_QUERY() built-in because the DO_KEY() builtin causes the Block or Module level Key-ExeQry trigger to fire - which enables you to override the chain of events that occur while EXECUTE_QUERY() simple instructs Forms to execute a query against the current navigation data block.

          When a query is running slowly against a base table block, the first thing I look for is the presence of a Post-Query trigger on the block. The Post-Query trigger is used to perform additional processing after each row of a query is fetched. If a developer performs additional database queries in the Post-Query trigger - this can cause the over all query to be slow. If you have too many additional fetches in the Post-Query trigger it is usually a good idea to change your Data Block from being based on a Table to based on a Procedure or a Table Join.

          Hope this helps.