Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle dependencies dashboard. (Read 3528 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Oracle dependencies dashboard.
23.11.06 at 13:09:34
Oracle dependencies dashboard.
Oracle spreadsheets can be created with SQL*XL. This topic is about how to create a Excel dashboards to show database data can be created using SQL*XL without writing any VBA code at all. In this example I will show how the ora_dependencies Excel spreadsheet was created. You can find the spreadsheet attached to this forum topic. Just connect with SQL*XL to your Oracle database and then open this spreadsheet.
In B9 the following formula was entered:
=sqlqueryvalue("select owner,name, type from all_dependencies where referenced_owner = upper('" & C2 & "') and referenced_name = upper('" & C3 & "') and referenced_type = '" & C4 & "' order by 1,2",D19)
SQLQueryValue is the SQL*XL worksheet function to lookup database data.
With & C4 & the value of cell C4 where the object name is entered - is embedded into the query. & is the operand used for string concatenation.
D19 is a parameter into the SQLQueryValue function that tells it from which row to show the results. I wanted to show 10 rows at maximum. When there are more rows I want to start at e.g. row 11 or 21 to show different pages of results.
To make the formula an array formula I selected B9 and expanded the selection to B9:D18. I put the cursor at the end of the formula in the Excel formula toolbar and pressed Cltr-Shift-Enter. Excel puts curly brackets {} around the formula to indicate it is an array formula.
The scrollbar was added from the forms toolbar. To show the forms toolbar go to View/Toolbars. I painted a scrollbar onto the worksheet and right clicked on it. I set the minimum value to 1 and bound cell D19 to the scrollbar value. Whenever I click the scrollbar the value in D19 will change and vise versa. In turn D19 is used in the array formula to give the starting row for the shown results.
Now you can enter the owner, object_name and type. Whenver the values in these 3 cells change the formula will be recalculated and SQL*XL will retrieve the new results.
Back to top
« Last Edit: 24.01.07 at 15:53:06 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1