oracle object array - supported? 1

[pelajhia]

I have created crystal reports from oracle databases, and used stored procedures on other databases (sybase) without difficulty.
I assumed that an oracle stored procedure would be simple to access.(I am using cr9, going to cr10).

I am being told by the db team that the stored procedures they want to set up are in packages and therefore would not be easily accessible.
I have tried to access a test package/procedure they provided and indeed the cr odbc driver for oracle sees the sp but does not offer columns/data.
I have been told this is because the driver can not handle the oracle object array. I am unsure of what this really means (still digging around for info on that...)
but I found a document on the b.o. site: cr_oracle_stored_procedures.pdf which appears to indicate that there are some requirements which need to be followed to set this up, but it is do-able. This does not explicitly mention 'object array'.
Anyone work with this before?
Does crystal NOT support the oracle object array?

Thanks.

 

[synapsevampire]

Forward the Crystal SP requirements to your database programmer and I think that you'll find that they can do a minor rewrite that will make it work.

The SP's need to be in packages, so I think that there's a misunderstanding.

Also make sure that you select the Procedure Returns Results in the CR Oracle ODBC driver connection.

-k

 

[pelajhia]

I have selected the proc. returns results option in the driver settings;
I have forwarded two docs from business objects outlining the sp requirements to the the dbas. After reading these, they are in agreement, and I quote: 'I don’t think we are going to have any luck calling a procedure from Crystal reports that returns multiple rows of data via an array. '
This is in reference to the array of objects, I guess.
They said the proc is set up accrding to the requirements.

What the heck is that anyway??


The example sp they have asked me to try is:

PACKAGE procname1 IS

PROCEDURE get_xxx(p_role_list OUT sc_role_arr,
return_code OUT NUMBER,
err_msg OUT VARCHAR2);

//there are some other procedures in here, too...

end procname1;


The syntax I am trying to use in the crystal report (command) is as follows:
{call tblowner.pkgname.procname1()}

(Got this out of annette harper's cr 9 on oracle book.)


I notice there is no 'IN' parameter; is this a problem?

Thank you for the rapid response!!

 

[pelajhia]

Clarification: When I said what the heck is that? I meant what the heck is an array of objects?
Thanks!

 

[pelajhia]

And I forgot to include the error msg I'm getting:
---------------------------
Crystal Reports
---------------------------
Failed to open a rowset.
Details: HY000:[DataDirect][ODBC Oracle driver][Oracle]ORA-06550: line 1, column 8:
PLS-00306: wrong number or types of arguments in call to 'GET_xxx'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

---------------------------
OK
---------------------------

 

[synapsevampire]

I don't have an Oracle database to test against right now, but the article clearly describes the steps required.

You didn't include the actual SP code, just the package, and yes, the lack on an IN OUT is a problem (I'm wondering about your dba's cometency now).

From the CR Whitepaper on SPs:

The REF CURSOR parameter must be defined as IN OUT (read/write
mode). After the procedure has opened and assigned a query to the REF
CURSOR, CR will perform a FETCH call for every row from the query's
result. This is why the parameter must be defined as IN OUT.

-k

 

[pelajhia]

Thank you for confirming this in/out stuff; it sounds like this part 'CR will perform a FETCH call for every row from the query's result' indicates that there is no problem returning multiple rows (eg: object array?)

I think the dba's are going to build views instead so I'm not going to go nuts trying to figure this out.
I suspect there would not be a problem if they wanted to try it, but their hearts are not in it.

Is there any difference in processing speed (generally) if views are used instead?

Thanks again! I do appreciate all the help for future reference.

 

[synapsevampire]

It depends on the number of rows being returned.

SP's are faster, but if the rowset is large, or the majority of the time is in the query itself, then the difference should be minimal.

The reason for the difference is that an SP has a precompiled execution plan.

I usually go with Views, SP's are generally much faster for OLTP type of actions as they return 1 or few rows.

With a View you can design it to be more generic, and you can join Views to other Views in CR.

-k

 

[pelajhia]

Oh, I can not join multiple sp's from separate data sources (eg: by user id)? That would definitely make views much more attractive. I am using a few views in another db right now.

 

[Turkbear]

Hi,
You can create SPs that use more than one Oracle datasource
and, in the Pl/Sql code, do any joins, etc to unify the data for output as the IN OUT REF CURSOR.

( In most cases, I prefer views, I think they are much easier to re-code if needed).

 

[synapsevampire]

As Turk mentioned, that is typically done within an SP if you need data sources combined.

It's also much easier at most sites to get permission to use, create and modify Views.

Joining views across data cources will be limiting in terms of the joins available though as you'll have different connections and Crystal must do the database processing for the joins, which is generally slow and a bad idea.

Try to join data sources on a database server, Crystal shouldn't be used for heavy lifting

-k

-k

 

[pelajhia]

Many thanks to you both for the excellent info!