Calling Procedures/Functions from an Object

building semantic layer
1

Hi,

Has anyone tried calling Oracle Procedures or Functions as a part of an object ‘Select’ statement?

In BO3.1 version an object could be set up as following:

SELECT statement:
business_hours(LOGS.CREATION_TS,LOGS.RESOLUTION_TS,CUSTOMER_SITES.SITE_ID,‘US’)

business_hours being the name of an oracle procedure.

Apparently, this does not work in BO4.1.3. Are there any alternatives to it?

neel
MCI Systemhouse
***************************************************************************** Neel Sekhon
MCI Systemhouse @ Cadence Design Systems Phone: 408-428-5382 E-mail: neels@cadence.com Pager:1-800-374-7685

Listserv Archives (BOB member since 2002-06-25)

2

Has anyone tried calling Oracle Procedures or Functions as a part of an object ‘Select’ statement?

In BO3.1 version an object could be set up as following:

SELECT statement:
business_hours(LOGS.CREATION_TS,LOGS.RESOLUTION_TS,CUSTOMER_SITES.SITE_ID,’
US’)

business_hours being the name of an oracle procedure.

Apparently, this does not work in BO4.1.3. Are there any alternatives to
it?

We also wanted to call an oracle procedure from within BO but have been told by them that it isn’t possible. We would also appreciate hearing of any workarounds anyone has used since we couldn’t work out a way round it.

Louise Priest
Fraser Williams Pharma Systems (UK)
lpriest@pharma.fraser-williams.com

Listserv Archives (BOB member since 2002-06-25)

3

I have objects created that call oracle stored procedures in BO 4.1.3. I have not had any problem with them. I’d be interested in hearing what kinds of problems you have had with these objects.

Amy Martel
amartel@allmerica.com

Lou Priest bolist@PHARMA.FRASER-WILLIAMS.COM 10/07/1998 11:28am

Has anyone tried calling Oracle Procedures or Functions as a part of
an
object ‘Select’ statement?

In BO3.1 version an object could be set up as following:

SELECT statement:
business_hours(LOGS.CREATION_TS,LOGS.RESOLUTION_TS,CUSTOMER_SITES.SITE_ID,’
US’)

business_hours being the name of an oracle procedure.

Apparently, this does not work in BO4.1.3. Are there any alternatives
to
it?

We also wanted to call an oracle procedure from within BO but have been told
by them that it isn’t possible. We would also appreciate hearing of any workarounds anyone has used since we couldn’t work out a way round it.

Listserv Archives (BOB member since 2002-06-25)

4

Lou, Neel,

Has anyone tried calling Oracle Procedures or Functions as a
part of an
object ‘Select’ statement?

I’ve used Oracle functions quite a lot with no real problems. I haven’t needed to use procedures so can’t answer that bit. The syntax for the select clause is as you have ie ‘fnc_myfunction(params)’. The only issue that I have found is using function based objects in conditions together with the normal objects in the result objects eg.
Object A based on a function accepting tableA.Column1 as the parameter. Object B based on tableA.column1.

If you put both Object A and object B in the query and try to use object A in the conditions you may find an error. I haven’t tried this in any detail outside our specific requirements.

Regards,

Jonathan

Project Leader
Group Medical, Regulatory and Product Strategy IS

Listserv Archives (BOB member since 2002-06-25)

5

I am calling different Oracle Functions as part of “selects” with Business Objects version 4.1.2 without problems. One function converts a given calendar date information to fiscal date information; the other uses complex logic to determine a customer type. What is the error / problems you are encountering?

Best Regards, Lori S. Furda
Sage Solutions, Inc.
Lori_SAGE@solution4u.com

Has anyone tried calling Oracle Procedures or Functions as a part of an object ‘Select’ statement?

We also wanted to call an oracle procedure from within BO but have been told by them that it isn’t possible. We would also appreciate hearing of any workarounds anyone has used since we couldn’t work out a way round it.

Listserv Archives (BOB member since 2002-06-25)

6

In a message dated 98-10-07 11:14:15 EDT, you write:

SELECT statement:
business_hours(LOGS.CREATION_TS,LOGS.RESOLUTION_TS,CUSTOMER_SITES.SITE_ID,’ US’)

business_hours being the name of an oracle procedure.

Apparently, this does not work in BO4.1.3. Are there any alternatives to
it?

There are two types of “stored” processing allowed by Oracle: functions and procedures. Stored functions are invoked in a Select or Where clause. As in:

select business_hours(x, y, x)
from some_table

A stored procedure is invoked without a select, as in:

business_hours (x, y, z)

The main difference is that a stored function returns a value, while a stored procedure simply processes data. You should be able to use stored functions anywhere in Business Objects, in fact there is a technical document at my company’s web site that details how to do this.

www.islink.com/bobjoraf.htm

Stored procedures are another matter. Business Objects is designed to process output, and Oracle stored procedures do not (typically) generate output. They just “do something”. To the best of my knowledge, these will not work in BusinessObjects.

However, there was a note posted on the list a while back that I saved that included a reference to the BusObj manual… I have not looked it up yet, but have repeated it here for your reference. Again, though, your stored procedure has to generate output for BusinessObjects to be able to use it.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

In a message dated 98-08-15 10:29:29 EDT, you write:

Later versions of Oracle (maybe 7.1 and up?) can return a result set from a
stored procedure. If you have an Oracle stored procedure that returns a result set, you can use the SP as a data provider. See page 40 of the BusinessObjects Oracle Database Guide for detailed instructions. note- the Oracle account running the stored procedure must have execute access to to it.

Best Regards, Lori
Lori_SAGE@solution4u.com
SAGE Solutions, Inc.
908-813-8207

Listserv Archives (BOB member since 2002-06-25)

7

Hi,

FYI… We were able to call Oracle Stored Procedure from a select statement of the object. Set it up as you would a normal object. e.g.

procedure_name(arg1,argt2,…)

arg1,2 etc. can be class_name\object_name or hard coded values or prompts.

One thing you have to make sure is that there is always a table associated with it, otherwise it will give you an invalid definition error.

neel
MCI Systemhouse(US)

We also wanted to call an oracle procedure from within BO but have been told by them that it isn’t possible. We would also appreciate hearing of any workarounds anyone has used since we couldn’t work out a way round it.

Louise Priest
Fraser Williams Pharma Systems (UK)
lpriest@pharma.fraser-williams.com

Listserv Archives (BOB member since 2002-06-25)

8

From: DRathbun@AOL.COM[SMTP:DRathbun@AOL.COM]

However, there was a note posted on the list a while back that I saved that
included a reference to the BusObj manual… I have not looked it up yet, but
have repeated it here for your reference. Again, though, your stored procedure
has to generate output for BusinessObjects to be able to use it.

Later versions of Oracle (maybe 7.1 and up?) can return a result set
from a
stored procedure. If you have an Oracle stored procedure that returns
a
result set, you can use the SP as a data provider. See page 40 of the BusinessObjects Oracle Database Guide for detailed instructions. note- the Oracle account running the stored procedure must have execute access to to it.

Lori_SAGE@solution4u.com

Well, I have nothing about Stored Procrdures in Oracle Database Guide, neither on page 40 not in table of contents or index. There is also nothing in any of my User’s Guides, except a note in one of them, that there is a possibility
to use Stored procedured, but Database Administrator must provide me them. I would really love to see some explanation on this subject…

Ryszard Mikke

Listserv Archives (BOB member since 2002-06-25)

9

On this subject Mikke wrote:

Well, I have nothing about Stored Procrdures in Oracle Database Guide, neither on page 40 not in table of contents or index. There is also
nothing
in any of my User’s Guides, except a note in one of them, that there
is a
possibility to use Stored procedured, but Database Administrator must provide me them. I would really love to see some explanation on this subject…

It must be your lucky day, check out the website tech.busobj.com, newsletter september 1998. There is a discussion on how to use oracle stored procedures in it. It refers to the famous page 40, but includes the parts of it that you need. I didn’t try it out though, but I’m certainly going to!

Good luck to all who will be trying as well, Marianne Wagt-van Loenen
IDETA
The Netherlands

Listserv Archives (BOB member since 2002-06-25)