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.
Click on answer and you should see a link in the subject area. (If not see the security paragraph below)
In the global header, select New, then Analysis, then Create Direct Database Request.
- the connection pool
- the sql statement
You can find the name of the connection pool in the Oracle BI Repository in the physical layer.
Call a database Function
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)
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
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
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.
is not good whereas