2

I am trying to create an OBIEE report using stored procedures. I have created a function in SQL Developer which takes a parameter and returns refCursor as output.

I, then, set the following query as default initialization string in physical layer of rpd: Select * from table(pipelined_emp(HR_DATA.GETCURSORS(parameter)))

GETCURSORS(parameter) is my function.

For now, in place of parameter, I am passing a constant value. While, I wish to pass a value from the OBIEE dashboard, similar to a prompt, to this function in the physical layer of rpd.

Thanks!

Aman Agarwal
  • 31
  • 6

1 Answers1

0

Yes. Session variables in the RPD being written into by front-end request variables: https://gerardnico.com/wiki/dat/obiee/obis/request_variable

Chris
  • 2,706
  • 2
  • 13
  • 15
  • Mr. Berg, I have already created a Physical Table of _Table Type: Stored Proc_ using `Select * from table(pipelined_emp(HR_DATA.GETCURSORS(60)))` . From the link you shared, I'm still unable to figure out how to pass a parameter in place of 60 from dashboard prompt. – Aman Agarwal Jul 05 '17 at 13:28
  • Select * from table(pipelined_emp(HR_DATA.GETCURSORS(VALUEOF(NQ_SESSION.YOURVARIABLENAMEGOESHERE))) – Chris Jul 05 '17 at 13:31
  • And where do I create this variable? – Aman Agarwal Jul 05 '17 at 13:33
  • The session variable in the RPD. The request variable is set from the front-end via a prompt or the SET VARIABLE command. – Chris Jul 05 '17 at 15:46
  • Hello Mr. Agarwal, could you solve your problem? Now, I have a problem exactly like you had and couldn't find any solution. Please let me know about your consequence. Thanks – Atefeh May 30 '20 at 04:28
  • I found that where and why I was wrong. The problem was around session variables which I had defined. The reason was that I had used reserved name (to and from) for my variables. I hope this experience be useful for others like me. – Atefeh May 30 '20 at 05:48