Use of SQL commands to extract data from Stored Procedure

[thiagarr]

Hi,

Does anybody have some insight into using SQL commands for importing (or using as is) Oracle Stored Procedures in CR. I am using Oracle 9.2 and CR XI.

We are trying to use existing SPs in CR without any changes (or minimal changes). I have been advised that SQL commands are the option to pursue. I have been successful in using using SQL comands in Add Command option in CR and use the outputted values in the report. But, when I use the SP in an SQL command, I do not see any outputted values available for use. I am not able to use an additional SELECT statements successfully in the SP.

Any insight, guidance will be greatly appreciated.

Thanks and regards,

Chandra

 

[synapsevampire]

Why would you use a Command Object for this, just point at the stored procedure as the data source.

I've heard people say that they found the command object faster, but my testing resulted in similar performance.

Also you should state the connectivity you are using.

-k

 

[thiagarr]

Hi K,

Thank you for the details.

I am using Oracle Drivers.

By "point at the stored procedure as the data source", do you mean select the SP like one would select a table or View in CR?

If yes, I have report/s that do exactly that (after modifying the SPs with REF cursors, etc). They are working good but the user want this way to minimize the dependancy on the database and also to reduce future maintenance efforts.

If not, is there any other means to do this?

I also have used the SQL commands to retrieve the data (only relevant data from the SPs) for use in the reports. But, with a SP as is in SQL command, I have had no success at all).


Where can I get more details on SQL command option to pursue what the user wants unless I can convince them otherwise.



Thanks and regards,

Chandra

 

[synapsevampire]

Oracle drivers aren't an option in Crystal, OLE DB, ODBC, DAO and native connectivity are.

How does using a Command Object accomplish: "They are working good but the user want this way to minimize the dependancy on the database and also to reduce future maintenance efforts."?

You want dependency on the database, the last thing you want is for Crystal to do processing, but I suspect that you mean something very different.

Also maintenance doesn't change much, if you change a stored procedure and it returns differnt values, you'll still need to do a verify database in either case.

What you are doing in either case is hardcoding what is to be used in the report, and if you need to change the datasource, you'll have to repoint the report to the new datasource.

Anyway, I'd seen another thread which stated they had problems using EXECUTE <spname>

But it works for me right now in SQL Server, and I believe that I tested it in Oracle as well some time back.

I think that you should not state HOW something should be done and request assistance with architecture as you are unfamiliar with Crystal, and someone here just might be able to add a lil sumpin to your design.

-k

 

[thiagarr]

Hi K,

Thank you for the details.

I select Oracle server in create New connection option and directly type in the service name, userid and password. I presume it is native connectivity?? My apologies for using the wrong terminology.

I have also tried ODBC with CR drivers, but tend to use the above option on a regular basis.

I will add more information soon.

Thank you.



Thanks and regards,

Chandra