Oracle SP returns no data in Crystal

[jrennatc]

I've written a stored procedure in Oracle 8i that takes no input parameters and returns (IN OUT) a ref cursor. When checking the cursor through SQL Developer, the cursor returns data.
Using Crystal 9 developer edition, I can see the stored procedure, I can add it to my new report, all the fields are present, but when I run it...no data appears.

 

[Turkbear]

Hi,
Why IN OUT if no INput ?( Just curious)

If you right-click on a field shown in CR, can you browse data?
What connection method( Native, ODBC, etc.)?
Are the username/password and tnsnames entry the same as where you run Sql Developer?

 

[jrennatc]

Using IN OUT because white paper says the ref cursor has to be defined that way.

How do I determine which connection I'm using. My CR was already set up with all the connections when I started my position, so I'm not sure.

Usernames and passwords are the same (we only have one).

 

[Turkbear]

Hi,
When the report is opened in the CR designer, click on the Database menu item and choose set datasource location ( or similar,it varies by version and I use 10)..Check the properties of the current connection and as Server Type it will show the method...( Oracle Server for native, ODBC for others, etc)

While there, check to be sure the same tnsnames.ora entry is used to connect that you used with Sql Developer. ( For Native Connection only, for ODBC you will need to check the configuration of the ODBC DSN being used).

 

[jrennatc]

I'm using native and the connection name is the same that I use with both SQL Developer and SQL*Plus. I tried running the procedure in both tools because the white paper says it has to return data in SQL*Plus. I'm getting data in both tools, but not Crystal.

 

[Turkbear]

Hi,
OK..That's all the usual suspects taken care of..

Be sure there is no Selection formula ( Record or Group) in your report that would reject all the records..Also check for any supressed group or section formulas..

( Grasping at straws, here )

Did you try browsing the data in the field explorer?

 

[jrennatc]

Since I wanted to check my result set, all I did was dump all fields on the detail section -- hadn't gotten to the point to set any filters or try and get fancy with suppressing groups.

Browsing the data in the field explorer shows no results. I'm pretty much grasping at straws as well.

 

[jrennatc]

From the white paper:
If you are using the native Oracle driver and using hard-coded date selection within the procedure, the date selection must use either a string representation of the format 'YYYY-DD-MM' or the TO_DATE function...

Gist of the matter was that I had a hardcoded date that used a date format of 'dd-mon-yyyy' which worked fine in SQL*Plus. So I changed to use the TO_DATE function.

 

[Turkbear]

Hi,
Glad you got it figured out..