Failed to retrieve data from the database ORA-24374 define not done before fetch or execute and fetch

[Former Member]

Hi All,

I have a report request for Crystal reports 2004 and get the error Failed to retrieve data from the database ORA-24374 define not done before fetch or execute and fetch

I am trying to have three parameters like the below. when I select the manual entry it should allow me to input any dates and when I select option 2 in the date-selector it should select only yesterdays date and in Option 3 it should select the last week range

I have tried the below sql query but it works when I hard code the values in the oracle sql developer so the syntax is right,but I get the below error message

Failed to retrieve data from the database ORA-24374 define not done before fetch or execute and fetch

and (1= {?DATE_SELECTOR}

   and trunc(da.create_date_time) between {?Create Date Start} and {?Create Date End})

   OR

  ( 2={?DATE_SELECTOR}

  and trunc(da.create_date_time) = trunc(sysdate-1,'DD'))

  OR

  (3={?DATE_SELECTOR}

   and trunc(da.create_date_time) between trunc(sysdate-7,'DD') and trunc(sysdate-1,'DD'))

Can anyone help,Thanks in advance

[raghavendra_hullur · Accepted solution]

Hi,

I think the trunc function used is not available in Crystal.

Instead can you try using currentDate function for sysdate and cDate(<date field>) for other dates requirements as required in your formula.

Hope it works with this change.

Thanks,

Raghavendra

[DellSC · Accepted solution]

I have successfully used Trunc() in Crystal with no problems.

What type of connection are you using in the report - Native Oracle or ODBC?  You should be on a Native Oracle connection if possible.

What version of Crystal are you running (including SP number - you can go to Help>>About to get the full version number)?

-Dell

[Former Member · Accepted solution]

Hello,

this one helps to you  ORA-24374 | Oracle Community.

I hope this is totally background issue. Let us know for more information.

--Thanks

Dhana

[Former Member · Accepted solution]

Hi Dell,

I am on Crystal reports 11.0.0.895 and using ODBC (RDO) to connect

[DellSC · Accepted solution]

The ODBC type of connection may be your problem.  Can you try changing it to a Native Oracle connection?  In your report, go to the database menu and choose "Set Location".  In there create a new connection direct to your database instead of using ODBC.

You're also on a very early version of XI.  If changing the connection type doesn't work, I would try downloading and upgrading to XI r2 SP6.  I don't have the download link handy, but I know that has posted it in several thread recently.

-Dell

[Former Member · Accepted solution]

Hi Dell,

I think I am following the wrong approach.

The Date_Selector parameter mentioned was created using the Parameter Fields in the Field Explorer.

This parameter is used for a formula calculation and it works fine.

But I want to embed the same parameter to the sql query.Can I embed the parameter to the sql query in the Edit Command window of the Database expert.

P.S. I am not able to embed any other new parameter as well using the Edit command window

[DellSC · Accepted solution]

I know exactly what your problem is!!

Parameters that are used inside a Command MUST be created in the Command Editor - commands cannot see parameters that are created in the Field Explorer in the main report.  There are some internal properties of parameters created in the Command Editor that are not available if you create it through the Field Explorer.  If you need the parameter for multiple things, create it in the Command Editor and then edit it in the Field Explorer afterward.  If you have multiple commands (not a recommended practice!) that share parameters, create parameters with exactly the same name in the Command Editor for each command.  Crystal will automatically combine them so that the parameter should appear only once when the report is run.

-Dell

[Former Member · Accepted solution]

I tried deleting the parameter in both the Command editor as well as the field explorer and then tried creating the parameter in the Command editor first but still I get the same error

[DellSC · Accepted solution]

From my own experiences with the issue, try this:

1.  Delete the parameters from both the Command Editor and the Field Explorer.

2.  Save the report.

3.  Close Crystal.  This will make sure that nothing is "stuck" in memory.

4.  Open the report and add the parameters to the Commands only.

-Dell

[former_member183750 · Accepted solution]

Before doing anymore work, I'd highly recommend what Dell mentioned initially; update to CR XI R2 SP6. See this blog on how to. Then go over the subsequent communications and recommendations from Dell.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

Hi Dell,

I followed your instructions and was able to avoid the errors now.Many thanks for it.

But the below code when added does not return any results.DATE_SELECTOR is my parameter name

('1'='{?DATE_SELECTOR}'

   and trunc(da.create_date_time) between {?Create Date Start} and {?Create Date End})

   OR

  ('2'='{?DATE_SELECTOR}'

  and trunc(da.create_date_time) = trunc(sysdate-1,'DD'))

  OR

  ('3'='{?DATE_SELECTOR}'

   and trunc(da.create_date_time) between trunc(sysdate-7,'DD') and trunc(sysdate-1,'DD'))

But when I hard-code the value '1'='1' the report works fine.

The DATE_SELECTOR I think is not able to substitute the value which is input when the report is executed.Should I link the parameter in the command window to the parameter in the Field explorer?

[DellSC · Accepted solution]

Put the {?Date_Selector} parameter in the report header and see what it's actual value is.

-Dell