Oracle Stored Procedures - How do I set up a report to call it? 2

[rleiman]

Hi Everyone.

I am new to Crystal Reports 11 and have created an Oracle 9i stored procedure that creates and populates a global temporary table.

Can you tell me if Crystal Reports can call this procedure and print the rows on the global temporary table? If so, can you tell me how to set up the report for this?

Thanks in advance.

Truly,
Emad

 

[elsenorjose]

Emad, what is the purpose of the temp table? Typically, people will create a stored procedure and use it as the data source in the Crystal Report. I worked in a shop where we were not allowed to access tables so procedures would be created which we would then use as the data source. Is that an option for you?

 

[synapsevampire]

Here's the old whitepaper on them:

http://technicalsupport.businessobj...respdfasp&sliceId=&dialogID=6276805&stateId=1

0 6274868

Haven't seen anything for recent versions, but this method still works, and probably any SP that does a single SELECT at the end will work.

So the last thing in the SP should be to do a select from the temp table.

-k

 

[rleiman]

Hi elsenorjose,

Thanks for the quick reply.

We plan to use the temp table as the data source.

We wanted to call the procedure from within crystal reports before the report is run. This would create the delete and populate the temp table rows.

Thanks.

Emad

 

[elsenorjose]

K, this still assumes using the SP, not selecting from a temp table created by the SP. If Emad is using an SP to create a temp table, you're suggesting he create another SP to retrieve data from that temp table? Still begs the question: Why use an SP to populate a temp table instead of just using the SP to retrieve the data and then using it as the dataset in Crystal?

 

[elsenorjose]

Emad,

That's an interesting approach. I've never seen a situation like that. What happens are report runtime that would require you to repopulate the temp table?

 

[synapsevampire]

I assumed rleiman meant that the temp table is being created solely by and for the SP, and no, I'm not suggesting that another SP call it.

-k

 

[rleiman]

Hi.

The temp table would be populated in a different way depending on a date date parameter.

We are also using c# so another question would be should we call the stored procedure from c# and then just use the temp table as the data source? Also if we use c# to populate the table, would it be running in the same instance?

Thanks.
Emad

 

[rleiman]

Hi.

synapsevampire is correct on why we are creating the temp table from the sp.

Emad

 

[elsenorjose]

I still don't see how to connect to the temp table. If Crystal calls the SP, you're not really using the temp table as the datasource, you're using the SP. Emad is saying that he can't do that and needs to be able to connect to the table. So it's almost as if Crystal has to call the SP PRIOR to selecting a datasource so that the temp table can populate the filed explorer as an available data source.

Just trying to understand this more clearly for my own use. Like I said, I've used Oracle procedures in Crystal quite a bit and have never encountered a requirement like this before.

 

[elsenorjose]

Emad,

Couldn't you just parameterize the procedure and incorporate that parameter in the report? This is how the shop I worked at would approach it. No need for a temp table to be created and dropped repeatedly.

 

[rleiman]

Hi elsenorjose,

We were planning on connecting to the global temp table as the datasource in crystal and using the call to the stored procedure to populate the table before it is run.

Thanks.

Emad

 

[synapsevampire]

The temp table is available within the SP, the last loine in the SP does a select from the temp table.

Temp tables are fairly common for database programmers, though less frequently by Oracle developers.

More often a query is used in place of a temp table - Oracle coders tend to dubs them inline queries, T-SQL coders call them derived tables.

Here's a blurb on them from an Oracle wizard that might prove interesting to both of you:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:15826034070548

Anyway, you can do so, I don;t tyr to get intooptimizing SQL here, there are much better resources on tek-tips in the appropriate forums, and my SQL skills are solid, but generic in nature, I swith databases all of the time and F1 is my bets buddy to remember the best way to do things in each database.

-k

 

[elsenorjose]

Well, perhaps K has a solution. I see what you're trying to accomplish, just not sure how to go about using Crystal to call external code like that. I'm staying tuned. I'd love to see the final solution to this one

 

[synapsevampire]

esj: Crystal has an option to use Stored Procedures as a datasource so I don't understand what you find perplexing about all of this???

But it may be that a temp table isn't required, read Tom's thread pasted above, he's a very sharp database programmer.

-k

 

[elsenorjose]

What I find perplexing is that the datasource is NOT the procedure. Emad wants the temp table to be the datasource. I know you can use SPs, like I said I use them all the time. That's not Emad's question.

"We were planning on connecting to the global temp table as the datasource in crystal and using the call to the stored procedure to populate the table before it is run."

He's using the table as the data source, NOT the procedure.

 

[rleiman]

Hi.

Thanks everyone. We have a bit of reading and research to do.

Truly,
Emad

 

[elsenorjose]

Emad,

Can you post your final solution once you get there? I'm still interested in seeing how you solve this.

J

 

[rleiman]

Hi elsenorjose,

Yes I can.

Thanks.

Emad

 

[elsenorjose]

What does the star next to my name mean?