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

 

[synapsevampire]

Ahhh, I think that it's a question of phrasing, the temp table is selected from WITHIN the SP as the data source for the report in my example and way of thinking.

I cna see how the notion of generating a temp table from within an SP and then externally calling that from the report directly wouldn't make sense, it wouldn't to me either, and I immediately ignored that as improperly phrased

We learn to do that a good deal around these parts, just look at my atrocious typing...

-k

 

[rleiman]

Hi elsenorjose,

The star is a "thank you" for posting valuable information.

If you collect enough stars you get your name listed as a valuable poster.

You got the star when I clicked on the "Thank elsenorjose for this valuable post" link.

Truly,
Emad

 

[synapsevampire]

esj: If someone provides valuable information it's a way of thanking them for their efforts.

Aside from becoming a MVP if you gain enough stars, it's the polite thing to do.

-k

 

[elsenorjose]

Shoot man, I owe you a million stars then! You and Ido have helped me out of some real binds.

 

[JScannell]

What I am trying to do seems to follow right in line with this thread. The initial question as to how to use a SP to become a datasource is by making the SP return a "ref cursor".

The SP is defined like:
PROCEDURE SP_ProcName ( varOne IN VARCHAR2, varTwo IN DATE, resultQuery IN OUT ref cursor )


The last statement inside the SP would be like:
open resultQuery for
select field1, field2, field3 from table

Since CR wants the ref cursor to be in out, that means that you have to define it in CR, call the SP, then retrieve the fields from the ref cursor. That's all find and dandy but ... how do you create a "ref cursor" variable in Crystal so you can pass it to the SP and retrieve the data after?? Once you figure that out, then you can place the fields right on your report as if it was from a table.

I'm stuck on the "ref cursor" definition in Crystal. I get an error message when I run the report.

I need an answer to this fairly quickly, please!!

 

[synapsevampire]

You don't define a ref cursor in Crystal Crystal does so itself.

Just point to the SP in lieu of a table and Crystal handles it.

Please remember to include basic technical information, such as your Crystal version and how you are connecting to Oracle.

-k

 

[JScannell]

Sorry about leaving out the version info:
Crystal 8.5
Oracle connection is via ODBC

There are two input parameters to the SP and one in-out one for the reference cursor. If I do a refresh, is it OK to force the ref cursor's in part to null?

The error I get when I run is: ORA-01023: Cursor Context not found (invalid cursor number)

 

[synapsevampire]

Did you write the SP in sccordance with the above stated requirements for SPs in CR 8.5?

And what does "force the ref cursor's in part to null?" mean?

Are you trying to say that you intend to pass a null parameter?

I fear that you're overthinking this, and not writing to the SP to the spec.

Create the SP to the spec, then point to the SP using the Crystal GUI.

Also you cannot use the Oracle supplied ODBC driver, use the Crystal suppied Oracle ODBC driver and turn on the procedure returns results option on it or SPs don't work correctly, or better yet, use the Oracle native connectiviy, it's faster and no messy ODBC to configure.

-k

 

[JScannell]

To answer your questions:

"Did you write the SP in sccordance with the above stated requirements for SPs in CR 8.5?" YES I DID. CRYSTAL FINDS THE SP AND SHOWS A LIST OF ALL THE COLUMNS. I DON'T THINK THAT WOULD BE THE CASE IF THE SP WASN'T CREATED CORRECTLY.


"And what does "force the ref cursor's in part to null?" mean?" WHEN YOU CLICK THE REFRESH BUTTON, THERE ARE PROMPTS FOR EACH PARAMETER. ONE OF THE OPTIONS IN THE DIALOG BOX PERTAINS TO KEEPING IT AS NULL, OR SOME SUCH TEXT, WHICH I CHECKED AT THE PROMPT FOR THE REF CURSOR VALUE BECAUSE I CAN'T REALLY PUT ANYTHING THERE.

"Are you trying to say that you intend to pass a null parameter?" NOT A NULL PARAMETER - JUST A NULL VALUE. THERE ISN'T ANY WAY TO PRODUCE A REF CURSOR VARIABLE.

"I fear that you're overthinking this, and not writing to the SP to the spec." THE SP APPEARS TO BE IN SPEC. IT RUNS INSIDE SQLPLUS. I CAN FIND THE COLUMNS TO IT IN CRYSTAL WHICH INDICATES THAT CRYSTAL RECOGNIZES IT. HOWEVER, THERE IS A RUNTIME ERROR THAT I GET AND I CAN'T FIGURE OUT WHY

"Also you cannot use the Oracle supplied ODBC driver, use the Crystal suppied Oracle ODBC driver and turn on the procedure returns results option on it or SPs don't work correctly, or better yet, use the Oracle native connectiviy, it's faster and no messy ODBC to configure." THE ODBC THAT I AM USING IS 'CROR8V36' WHICH IS A CRYSTAL ONE. THE 'PROCDEDURE RETURNS RESULTS' IS CHECKED.

 

[synapsevampire]

You shouldn't be setting the ref cursor to null, however I would guess that it is not written properly if that's a choice being presented to the user. In CR 9 there was a bug that presented this, however a hotfix addressed this.

-k

 

[JScannell]

If the SP wasn't created correctly, would it still show up in the list of StoredProcedures in the "add database to report..."?

The SP has three parameters. 2 INS and an IN OUT consisting of: A String (USER_ID), a date (AS_OF_TMS), and a Ref Cursor (RESULTQUERY).

After choosing the SP in the "Add database to the report...", crystal pops up with an "enter parameter values" window where it shows a listbox with all 3 parameters named exactly as above. How could that be the case if the SP wasn't created right?

There is a "Discrete value" entry field that changes as you select each field, I get an appropriate entry field type for each of the SP parameters exactly as defined there(e.g. string, date), There even is a data entry prompt for the 'RESULTQUERY' ref cursor variable. What am I supposed to enter there?? I can't enter anything. So I checked the ' box. I tried it again leaving the box unchedked and still get the OR01023 Cursor Context not found (invalid cursor number) error when I click the OK.

You mentioned that there was a hotfix for CR 9. But I'm using 8.5. Could that be the problem all along? All the documentation I have found talks about doing this in CR 8.5 like it was possible. maybe it isn't!

 

[synapsevampire]

No, they work in CR 8.5, I've used them.

Are you using a package?

There isn't supposed to be a prompt for the ref cursor as I recall in CR 8.5...

-k

 

[JScannell]

I tried doing it in a package initially because the example I found online said to, but package functions and procedures don't show up in the available list inside CR so I cahnged it to a procedure and now it shows up with all the parameters shown and the fields. So it must be able to see the ref cursor otherwise how would it know the field names??

You don't get a prompt for the ref cursor when you run the report (e.g. refresh). But you do get one when you initially add the SP to the report in the "Add database to the report..."

 

[IdoMillet]

Cursor is simply not a valid parameter data type in Crystal.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Why did you say that you followed the example, and now say:

I tried doing it in a package initially because the example I found online said to, but package functions and procedures don't show up in the available list inside CR

Anyway, use the example given.

I'm a tad rusty on them, but in 8.5 I followed it closely.

-k

 

[JScannell]

The online example shows several ways to do this. One of the ways is via a package and others using a procedure. I tried both.

You just asked me if I was using a package. I answered that I tried it. The package doesn't show up in the list. the procedure does but gives an error at runtime.

 

[JScannell]

In the pdf document 'scr_oracle_stored_procedures.pdf' there are several examples.

You asked me if I was using a package. Were you asking me that that as far as creating the ref cursor goes? If so, then I am using a package for that:

CREATE OR REPLACE PACKAGE cursorTypes AS
TYPE ref_cursor IS REF CURSOR;
END cursorTypes;
/

As far as the procedure goes, though, it is defined as a procedure not as a package. It references the above package like this:

CREATE OR REPLACE PROCEDURE ProcName ( USER_ID IN VARCHAR2, AS_OF_TMS IN DATE, resultQuery IN OUT cursorTypes.ref_cursor )


If you wondered if I was using a package for everything as in Example 3 of the pdf then the answer is no. When I tried to place the procedure inside a package it didn't show up inside CR.

As a synopsis, I have a procedure that references a ref cursor that is defined as a package. CR finds everything like I've stated before. It just won't run.