How to call stored procedure in direct database request obiee 11g analytics?

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

I have created stored procedure in db....

I want to execute stored procedure in direct database request in analytics..

When i trying , i am getting errors..

my stored procedure is

CREATE OR REPLACE PROCEDURE TESTINGPROCEDURE(OutCur OUT sys_refcursor)

AS

BEGIN

OPEN OutCur FOR

select * from w_company_d

END;

execution:

var rc refcursor

exec testingprocedure(:rc)

print rc

Please help me..

in direct database request of obiee 11g analyics, how to call this procedure..

I have tried.. i have enter connection pool name..

Odbc driver returned an error (SQLExecDirectW).

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 17001] Oracle Error code: 900, message: ORA-00900: invalid SQL statement at OCI call OCIStmtExecute: var rc refcursor exec testingprocedure(:rc) print rc. [nQSError: 17011] SQL statement execution failed. (HY000)

SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "r2_conn_pool" var rc refcursor exec testingprocedure(:rc) print rc')}

rmoff

If I'm not mistaken, that's a PL/SQL code block you're passing. OBIEE is expected SQL (what do you expect it to do with "print rc"? )

Why are you wanting to use DDR anyway? You can call SP from within the physical layer of the RPD itself.

cesar.advincula.o

Hello Kayva.

Directly OBIEE cant execute SP from Oracle BI Presentation Service,

so if you want you could create a webservice to call a stored procedure, an put it as an "action link" ( you could pass parameters if you want),

but it's not real dynamic,

However, you could execute, SP, by these events:

- Execute on connect

- Execute before query

- Execute after query

- Execute on disconnect

Kind Regards,

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

In db , if i execute the stored procedure, i am getting data as showing below screenshot..

The same procedure, executing in obiee direct data base request.. i am getting error..

please help me.. how to execute the stored procedure in obiee 11g analytics

rmoff

If you want more help, you need to interact with the questions and suggestions that are being offered.

If you ask the same question, I'm going to give you the same answer:

If I'm not mistaken, that's a PL/SQL code block you're passing. OBIEE is expected SQL (what do you expect it to do with "print rc"? )

Why are you wanting to use DDR anyway? You can call SP from within the physical layer of the RPD itself.