View Last Query Executed – Oracle

Just a quick post on how to view recent queries run into an Oracle database:

 
SELECT sql_text, sql_fulltext
  FROM v$sql
ORDER BY last_load_time DESC

Drill Down Queries

PS Query in PT 8.51 now supports a new expression type called as Drilling URL. This expression type allows a user to drill down on values displayed in the result. Drill down can be done to a child query, external URL, component or attachment.
Following steps illustrate the drilling url feature using parent - child queries.

1. Create two queries, a parent query (MENU) and a child query (PANEL), so as to speak. My parent query, queries the PSMENUDEFN and PSMENUITEM tables to list the menuname, component etc and then child query lists the pages in a component (from PSPNLGROUP).
2. I will be using the drilling url expression and join Panelgroup between the two queries.
3. My child query (PANEL) takes a prompt value for the field PNLGRPNAME
4. In my parent query (MENU) I add a new expression of the type Drilling URL and choose the "Query URL" link, which takes me to the Query URL Widget
5. Specify the child query name which is PANEL, and then assign the prompt key, which is
   PNLGRPNAME.
6. Under "Map URL to Query Columns" grid I selected the PNLGRPNAME column as I want this column to be hyperlinked.
7. The expression text will look like below.

'/q/?ICAction=ICQryNameURL=PUBLIC.PANEL&BIND1=%B.PNLGRPNAME%:B.PNLGRPNAME'

Now save and run the parent query and the results will be displayed like so.

 Clicking on the component hyperlink will open the child query results like so.

Drilling URLs are supported only in RTF XMLP templates.

To use a drilling URL in a XML Publisher report:

1. Create the query with the drilling URL defined as a field. When you run the query the URL to the child query should be displayed in the results like so. 

    2. Create an RTF template. In the RTF template map the field that contain drilling URL.

• Highlight the field where you want to place the drilling URL.
• Select Insert (from the Word menu), Hyperlink or use Ctrl+K.
• In the Insert Hyperlink dialog box, enter the URL link in Address field.
  Each URL link should be defined as {URL_FIELD}, where URL_FIELD is the unique field name for the expression that contains a specific drilling URL.

• Use the Target Frame push button to select how this URL link will be opened, either in the same window or in the new window
  
  Note. The URL value does not need to be added to the report, as long as the unique field name (data file that contains the URL) is mapped to a field in the report.