BO 5.1.1 / Oracle 8.1.7 Stored Procs Modifying DB 1

[Oradam]

Hi All,
I'm running bo511 and oracle817 and have an object that calls a stored function in the SELECT section, passing other object values in. This stored function returns a numeric value based on the values passed into it and this value become one of the data columns for this object.

When I call this procedure, have it to its work and return the number, my reports look fine, the data is correct, the procedure runs once for every iteration [row] of the dataset.
Dilemma: I need this procedure to INSERT a row in a table when it is called before it returns the number to BO. BO detects that this insert is going to (or has) occur and somehow preempts or rolls it back! I have tried doing the PRAGMA RESTRICT_REFERENCES(func_name,WNDS) to tell BO it is a read-only function. Oracle didn't like this answer, it said I "violated my pragma".
I have also tried the PRAGMA AUTONOMOUS_TRANSACTION pragma which starts a new context transaction and does the insert. BO still finds out about it and rolls it back!!

Does anyone have any possible solutions for this? Thanks so much for your help.

 

[BobWard]

You surprise me that BO is so clever. Could it not be the user you are connecting as doesn't have insert permissions. I dont see how BO could control the storedprocedure execution. Interested to see the resolution,

 

[Oradam]

Trust me, we were just as surprised as BO's 'intuitiveness' on this one. I am primarily an Oracle developer that has been thrown on this task because of its database nature, therefore I am not as familiar with the inner-workings of BO.
Can you suggest a tactic I could use to view or change how BO is connecting? The weird thing is that I don't get any error messages at all, just data that = 0 whenever DML (insert, update, delete) is issued.
Thanks for your reply,
Adam

 

[deepakveni]

Hi,
Im new to BO. Can you please guide me about how you are retriving data in BO through stored procedure in Oracle. Because I tried the same but getting an error. BO is asking me to insert a value for OUT variables in SP.
Please help.

With cheers,
Deepak Veni (deepakveni@yahoo.com)

 

[Oradam]

I'm not entirely sure you can call procedures that use OUT variables from BO. There are two ways to call procs (that I know of) from BO. You can do it through an Object, or use the BO reporting tool to directly call them (similar to the freehand SQL method). We call them through objects.
Here is what the SELECT area of our object looks like:
PROC_NAME(TABLE.column, TABLE2.column, (@Prompt ('data:','N',,mono,free)))
You can also put stuff in the WHERE clause to narrow the data, like prompting the user for a list of limitations that will reduce the recordset.
ie: WHERE:
@Select(Patient ID\Patient Last Name First) IN (@Prompt ('Patients:','C','Patient ID\Patient Last Name',multi,free), 4)

 

[BobWard]

You can look at the connection details of BO in either Supervisor od designer. Look at Universe Parameters in designer when the universe you are using is open, its off the file menu. You can change very little username, password, asychronous or sychronous mode. This is the only place you can change the details of the connection.

A test worth doing, if you dont know the answer would be to cut and paste the sql produced by the BusinesObjects report and run it in sql*plus. Does it insert what you want ?

 

[Oradam]

Yeah, the SQL works fine in SQLPlus, that's where we designed it. No luck in the universe preferences - I appreciate your help, though!

 

[ajmcgarry]

I have a problem whereby when I select the new report wizard and select Stored Procedures, it tells me I can't use them because I have no connections.
I have a connection to an SQL Server 2000 Database set up in Designer. Where am I going wrong?

 

[BobWard]

ajmcgarry: I think you may need personnal connections not secured ones.

 

[ajmcgarry]

Thanks for the quick reply Bob. I have Shared, Secured and Personal type connections in Designer (All using the one user dsn), but none of them show up in the wizard.

 

[savery]

Just reading through this forum and am very interested in Orodam's comments on running stored procedures from objects. How do you do this? I have tried adding the suggested code to an object SELECT statement in Designer, but my PROC_NAME is not recognised (Oracle 8i stored procedure). On Parsing I get 'Invalid table name'. Have I got the wrong end of the stick?

What I am trying to achieve is a way of repeatedly running a stored procedure with different parameters (prompted) from within the BO reporter. All the stored procedure does is populate a table, BO actually then reports from this table (i.e. the stored proc does not itself return anything)

I would be most appreciative of any help

 

[savery]

In BO you can call a user defined function in the select part of the object. if the function is a column function and you pass it a table.column value.
Here we have sys.dual in the universe and use it to ensure function is called once (only one row in dual)
eg

SELECT
myschema.myfunction(sys.DUAL.DUMMY, myparam1, myparam2,..)

myparam1 can use prompts, selects, variables, etc

Inclusion of this object will call the function using a sql statement like -> select myschema.myfunction(...) from dual

In Oracle create function. The first parameter v_dummy is to accept the dual.column value that you need in BO to allow it to associate the select with a table

create or replace function myschema.myfunction(
v_dummy in varchar2, -- to accept dual.dummy column entry
mparam1 in number,
mparam2 in number)
begin
-- call myprocedure which does update/insert
myprocedure(mparam1, mparam2);

Result := 'Procedure updated';
return(Result);
end myschema.myfunction;

then define an autonomous procedure to do the insert

create or replace procedure myprocedure(
mparam1 in number,
mparam2 in number)

PRAGMA AUTONOMOUS_TRANSACTION;
begin

insert into mytable values(1,2,3);
commit;
end;

this procedure needs to be autonomous (ie separate transaction from calling function - supported in 8.1.7 and above) and you need to commit transaction before returning (else you get an error/rollback)

In our case we wanted the procedure to update a table based on parameters supplied and then report on the updated table. We had to create two data providers, the first just contained the procedure object, the second info for the report. If you do not do this then BO may fetch the data first and then run the procedure!