BO calling Oracle stored procedure?

building semantic layer
1

Anyone with experience using BO to call an Oracle stored procedure?

I have an Oracle 7.3.3 stored procedure with no parameters (simpliest case): CREATE or REPLACE PROCEDURE rontest5 AS
BEGIN
update rontest set item = ‘3’;
commit;
END;
It executes successfully from the SQL command prompt:

SQL> execute rontest5

PL/SQL procedure successfully completed.

But…when running the wizard to create a new report with a new data provider from a stored procedure, BO 5.0 & 5.1 give the following message: “ORA-06550: line 1, column 23: :PLS-00103: Encountered the symbol “END” when expecting one of the following: : : := . ( @ % ; :The symbol “;” was substituted for “END” to continue. :-6550”

This is a oracle parser message which I translate to mean Oracle could not parse the BO stored procedure execute command.

Any ideas?

Thanks in advance
Ron

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

2

In a message dated 00-03-21 13:47:01 EST, you write:

Anyone with experience using BO to call an Oracle stored procedure?

I have an Oracle 7.3.3 stored procedure with no parameters (simpliest
case):
CREATE or REPLACE PROCEDURE rontest5 AS BEGIN
update rontest set item = ‘3’;
commit;
END;
It executes successfully from the SQL command prompt:

[ snip ]

Any ideas?

Keep in mind the function of a data provider is to provide data. In your simple example, there is no output! If there is no output, then there is nothing for BusObj to process.

A few days ago someone posted a sample stored procedure for Oracle that you might want to look at. Unfortunately I didn’t keep a copy, but you should be able to search the archives. I believe that instructions for reviewing the archives are at the bottom of each email distributed by the list.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

3

Ron Plank escribiÛ:

Anyone with experience using BO to call an Oracle stored procedure?

I have an Oracle 7.3.3 stored procedure with no parameters (simpliest case): CREATE or REPLACE PROCEDURE rontest5 AS
BEGIN
update rontest set item = ‘3’;
commit;
END;
It executes successfully from the SQL command prompt:

SQL> execute rontest5

PL/SQL procedure successfully completed.

But…when running the wizard to create a new report with a new data provider from a stored procedure, BO 5.0 & 5.1 give the following message: “ORA-06550: line 1, column 23: :PLS-00103: Encountered the symbol “END” when expecting one of the following: : : := . ( @ % ; :The symbol “;” was substituted for “END” to continue. :-6550”

This is a oracle parser message which I translate to mean Oracle could not parse the BO stored procedure execute command.

Any ideas?

Thanks in advance
Ron

Ron,

I had the same problem with one customer. If you review the following example (from BO TechSup) It can help you.

You would be to use the string “:cbo” as parameter 0 calling the Store procedure from SQL FreeHand.

In our case, can be that you define your store procedure and that you can execute it from SQL, But It can have problems passing parameters. Please, see the Store Procedure construction.

Best regards,

–
Libardo Rodriguez V.
UNIBASE

Problem
How do I call an Oracle stored procedure using FreeHand SQL?

Resolution
CONFIGURATION*
This entry applies only to Oracle databases. ****RESOLUTION
The connection to the Oracle instance must use an Oracle login that has been granted the ability to create and execute Stored Procedures.

  1. Start BusinessObjects.
  2. Call the stored procedure from FreeHand SQL with the following syntax:

begin
ProcedureName(:cbo);
end;

Note: ‘:cbo’ is the implicit REF cursor that is used in FreeHand SQL for an
Oracle connection.

For example, you create the following stored procedure called ‘get_item’ :

CREATE PACKAGE pack_items IS
TYPE catcurtyp IS REF CURSOR RETURN items%ROWTYPE; END pack_items;
/

CREATE PROCEDURE get_item ( cat_cv IN OUT pack_items.catcurtyp) IS BEGIN
OPEN cat_cv FOR SELECT * FROM items;
END;
/

The above example could be implemented using Freehand SQL as:

begin
get_item(:cbo);
end;

MORE*****
For information on constructing Oracle stored procedures, see the Oracle Database Guide, chapter 3.

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