About
The Direct Database Request permit you to perform SQL statement directly to a datasource. The Oracle BI Server sends unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set can be rendered in Oracle BI Presentation Services, and then charted, rendered in a dashboard, and treated as an Oracle BI request.
Becareful, you can perform SELECT but also CREATE or even DELETE and DROP statement!
You bypass completely the OBI Server mechanisms such as :
- the Security
You can use it to perform complex query such as using a table function.
Articles Related
How to create a Direct Database Access ?
Access to Direct Database Access
10G
Click on answer and you should see a link in the subject area. (If not see the security paragraph below)
11G
In the global header, select New, then Analysis, then Create Direct Database Request.
Creation
Enter :
- the connection pool
- the sql statement
You can find the name of the connection pool in the Oracle BI Repository in the physical layer.
You can use in the SQL statement a variable. Below, a example with a presentation service variable with a default value of 'Photo'.
How to
Call a database Function
TT:
Database Request in Dashboard
Add a prompt in the dashboard to update the presentation service variable and the Direct Database Request can be filtered.
And the download link works perfectly and gives you the filtered data (Tested on version 10.3.4.1)
Security, Privileges
OBI Presentation Service
You must grant your account with the privilege “Write Back to database” :
- Login in OBI Presentation Service Website
- Follow this links : Setting / Administration / Manage Privileges
- Grant the privilege Answers/“Execute Direct Database Requests” to the permitted group
Administration Tool
You must grant the direct access database to the user through the Administration Tool in the security part.
- Login in OBI Administration
- Go to the menu : Manage / Security
- Grant the privilege “Execute Direct Database Access” to the permitted group of user
Support
ORA-00911: invalid character at OCI call OCIStmtExecute
Be careful that you have to suppress the semicolon at the end of the statement.
[nQSError: 27022] Unresolved Connection Pool object: ….. (HY000)
Be sure to have the name of the database and the name of the connection pool framed by two doubles quotes.
Example:
database."Connection Pool"
is not good whereas
"database"."Connection Pool"
is good.