Crystal 8.5 and Oracle 9 Stored Procedure - Need Assistance.

[Maven4Champ]

Hi, I am using an Oracle9 database and Crystal 8.5 for reporting. I normally have tables or views/materialized views) that I report off of but I am presented with an interesting issue.

I have FINALLY figured out how to create a procedure per the whitepaper document found on the BO Crystal website regarding Oracle Stored Procedures and Crystal Reports Software. Find more info on the actual procedure here:

http://www.tek-tips.com/viewthread.cfm?qid=1379858&page=1

Now when I connect via ps2ora7.dll driver to my oracle database, I receive the initial prompts for three items (my REF CURSOR, StartDate and EndDate) and leave them all with the SET TO NULL checked per another document I found which tells Crystal to prompt each time for the appropriate parameters.

Now, when I do this I receive an ORA 00000 message stating successfull operation. But it DOESN'T add the stored procedure to my report so that I can develop/design accordingly.

So I try to add it again figuring Crystal is just tring to verify it can connect and then try to add it again and then it says INVALID CURSOR. I have found that this is due to trying to open it twice so there is problem #1. Problem #2 is some people are stating that if done corretly, it should only prompt for a StartDate and EndDate, not the REF CURSOR.

Problem #3 is connectivity. I found some posts on here stating what drivers to use and not use but per the whitepaper from BO, I am following correctly with CR8.5 and ps2ora7.dll.

Any advice?

 

[Turkbear]

Hi,
Switch to the native method of connecting ( Oracle Server);
much better to use whan accessing Oracle. especially when using a Stored Procedure.That ODBC one is older than your Database and may be causing some issues.

You also do not need to issue the CLOSE CURSOR command, the END Proc takes care of that..

Not sure why you get a prompt for the REF CURSOR, we don't on our SPs..Try not setting it to NULL

We build ours this way ( small example)

 CREATE OR REPLACE  PROCEDURE "WAREHOUSE"."GETOFFICE_PROCEDURE" (
Office_Cursor IN OUT GetOffice_Package.Office_List,
Office_Parameter IN WAREHOUSE.MNDOT_EMPLOYEE.DEPT_NBR%TYPE)
AS
BEGIN
OPEN Office_Cursor FOR
SELECT DEPT_NBR,DEPT_NM,OFFICE_NM
FROM WAREHOUSE.MNDOT_EMPLOYEE
WHERE WAREHOUSE.MNDOT_EMPLOYEE.DEPT_NBR like Office_Parameter||'%';
END GetOffice_Procedure;

The Package referred to has:

CREATE OR REPLACE PACKAGE GetOffice_Package
AS TYPE Office_List IS REF CURSOR;
END GetOffice_Package;

Does not prompt for anything but the Office_Parameter






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Ok I used this exact setup and I am still getting the same issue of CR 8.5 prompting for the cursor value. Obviously you can't send a value to this parameter nor can you send a null value so I still am having the smae problem. I have no ODBC setup at this point. I am going through Data Explorer > More Data Sourcecs > Oracle Server and then connecting to me stored proc there.

It will prompt for the values, says SUCCESSFULL but won't add the stored proc. And I can't try and add the Stored Proc a second time but then it says error because I have already opened the stored proc.

Any ideas? Is this just a CR 8.5 bug?

 

[Turkbear]

Hi,
Which Oracle client version is installed on your workstation?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

ORA92 is installed. I have TOAD and SQL Plus as well.

 

[Turkbear]

Hi,
Not really sure what you mean by:

It will prompt for the values, says SUCCESSFULL but won't add the stored proc.

What will prompt..What says Successful?
What do you mean by add the SP?


You created the SP directly in the database instance and you just use it as the datasource, like it were a Table or View, Correct?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Ok let me try to explain differently.

I connect to the database, find my stored procedure (it shows up in Crystal) and choose Add from the Data Explorer in Crystal (the part of the wizard to add tables to a report). When I choose Add it prompts me for CR_CURSOR, StartDate and EndDate.

The prompts pop-up as soon as I hit Add as to ask for requirements from the stored procedure (my input parameters). Set to NULL value is checked by default so I leave it that way per instructions I found on another tip page stating if I leave them as NULL values initially, then it will always prompt for the value when running the stored procedure from Crystal.

So I do that and hit OK and it says the following:

ORA-00000: normal, succesfull completion

If I hit OK to that, it does nothing but return me back to the Data Explorer where I can try to add the stored procedure again to Crystal report and it doesn't. It's like it recognizes the SP, gives me the appropriate prompts but doesn't allow me to add to a report. Furthermore, should it really be prompting me for CR_CURSOR?

 

[Maven4Champ]

Yes you are correct. I am using it as my datasource (like a table). Crystal is the application prompting me for the values and offering up messages stating succesfull, etc.

 

[Turkbear]

Hi,
Sorry, I cannot reproduce that behavior ( I am using CR XI R2) --

I select Blank Report, click on my database connection ( Oracle Server based), log in and navigate to the SP I posted previously, click to add it, get prompted for the Parameter, use the 'set to Null' for a start and then I can design the report layout I want..
When I preview it, it returns all the records ( since I have the parameter set to NULL) - If I refresh and opt to supply a new parameter, it works as expected then as well - It never asks about the REF CURSOR...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Maven4Champ]

Turkbear,

I basically have the same setup except after adding, I cannot design or develop my report - I am stuck at that point.

 

[aks12]

Here at my work site, when we were on CR8.5, our reports pointing to Oracle stored procedures always prompted for the cursor name along with the parameters and we saw this as normal CR behavior. After we upgraded to CR10 though, the cursor prompts disappeared.

I remember getting that same message, "Normal successful completion" a few times also, but unfortunately I don't know how it was ultimately resolved.

 

[Maven4Champ]

Everyone, thanks for the help so far...

I'm trying to stay positive in a situation where I have no clue what to do - have I fallen into the Crystal Reports black hole?

 

[Maven4Champ]

HOUSTON - WE HAVE LIFT OFF.

I changed from Native Oracle driver to ODBC and it let me connect and build the report. I then changed back to Native Oracle driver (psdora7.dll I believe?) and it works. It does still prompt the user for CR_CURSOR and STARTDATE and ENDDATE but if they choose nothing for CR_CUROSR and just proceed with the dates, it works.

So my two questions are - and maybe I should post this in a new topic -
1.) How do I supress the CR_CURSOR from being shown to the user?
2.) How do I default the date parameter to just DATE format in Crystal instead of DATETIME? It's currently set as DATE in Oracle Stored Procedure (the parameter) but Crystal still makes it DATETIME.

Any further ideas?