I am trying to call an "Oracle function" from webfocus code. I appreciate any one have suggestion for this issue?

Vijaya, you might want to use the Search button and type in 'Oracle function'. There are a number of hits. Here is one of them:
http://forums.informationbuild...681077331#3681077331

This topic is similar to yours; please take a look at it: Oracle functions

- Neftali.

Ginny-I used search button"Oracle Function" before I posted this issue in the user forum.I could find any thing for my problem.

I will open a case with IBI.

Thanks.

Vijaya, what problem do you have? Why would you need to open a case with IBI?

Using an Oracle function from WebFOCUS requires that you create a PL/SQL procedure as a wrapper and "return" whatever you need in a REF CURSOR that can be consumed by WebFOCUS by reading SQLOUT.

Neftali,
I am using Oracle function via passthru.
I 've the below code in my Webfocus. If you looked at the below code i am passing (2588) to get the output. It does give output for what ever id i am passing. But my problem is instead passing one id at time to get the output. I want to list all the value from this record. I apreciate ,is it possible to list all the value from this record.

SQL SQLORA
SELECT scott.BPO_CURRENT_STATUS(2588) FROM DUAL;
END

Thank you
Vijaya

Sorry Vijaya, but I don't think I understand your requirement.

quote:

I want to list all the value from this record

What do you mean by that? Your function is supposed to return the Status of whatever ID you pass to it and as far as I can see it does that.

My guess is that you have a table with ID's and you need to make sure that each ID you pass (on a row-by-row basis) is returned along with the corresponding status?

If that is what you need, then you have to change the structure of your SQL; there is nothing wrong here with using an Oracle function from WebFOCUS.

I would potentially do something like this assuming you have a table called MYTABLE that contains the ID's you want to evaluate:

SQL SQLORA
SELECT SCOTT.MYTABLE.ID AS 'ID',
       SCOTT.BPO_CURRENT_STATUS(SCOTT.MYTABLE.ID) AS 'STATUS'
       FROM SCOTT.MYTABLE
;
END

Now, I would question the necessity of doing such a thing which usually ends up with a not-too-performant query, for you see, executing a PL/SQL function for each record in a SQL query is very inefficient due to SQL <-> PL/SQL context switching, but this is an Oracle architectural thing that is out-of-scope for this forum.

Give that code a try for yourself preferably using a SQL client (leave WebFOCUS out for now) until you get what you need. Once it works, put whatever query you end up with betwen SQL SQLORA ... END and it should work.

However if you could rewrite your query to do whatever your function is doing in "pure and straight" SQL, your business users will certainly appreciate the performance gains. Of course, if you're dealing with a table with a few hundred or maybe thousands of records this may not be such a big deal but in a DSS environment with millions and millions of records it does make a difference.

I think Oracle 11g offers some function "caching" stuff that may/may not help alleviate performance issues due to context switching but I have no idea as to whether or not that would be applicable to your case of if you have Oracle 11g at all.

This message has been edited. Last edited by: njsden,