Home » SQL & PL/SQL » SQL & PL/SQL » ORA-24374: define not done before fetch or execute and fetch (Oracle 10g)
ORA-24374: define not done before fetch or execute and fetch [message #410556] Sun, 28 June 2009 15:37 Go to next message
oracle123
Messages: 56
Registered: March 2009
Member
Hi,

This is the first time I am working with ref cursors. My code looks like as follows:

create or replace procedure test(p_deptno IN number,
                                 salary   OUT number,
                                 count    OUT number)is

  p_cursor sys_refcursor;

begin

  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;

  -- procedure to calculate total salary
  total_dept_salary(p_cursor, salary);
  
  -- procedure to calculate total head count in the department
  total_dept_count(p_cursor, cnt);

end test;

The above procedure compiles successfully but I am getting the following error message when I test it:
ORA-24374: define not done before fetch or execute and fetch

The above error points to the line "total_dept_count(p_cursor, cnt); "


So, I tried to open the cursor once again before that statement as shown below and everything works fine.


create or replace procedure test(p_deptno IN number,
                                 salary   OUT number,
                                 count    OUT number)is

  p_cursor sys_refcursor;
 
begin

  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;

  -- procedure to calculate total salary
  total_dept_salary(p_cursor, salary);

  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
  
  -- procedure to calculate total head count in the department
  total_dept_count(p_cursor, cnt);

end test;



So my question is: Do I need to open the cursor every time I pass it into a new program unit or else am I missing something?

Thanks for the help in advance.

- Scott.
Re: ORA-24374: define not done before fetch or execute and fetch [message #410560 is a reply to message #410556] Sun, 28 June 2009 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
24374, 00000, "define not done before fetch or execute and fetch"
// *Cause:  The application did not define output variables for data being
//          fetched before issuing a fetch call or invoking a fetch by
//          specifying a non-zero row count in an execute call.
// *Action: Issue OCI define calls for the columns to be fetched.
bcm@bcm-laptop:~$ 
Re: ORA-24374: define not done before fetch or execute and fetch [message #410561 is a reply to message #410556] Sun, 28 June 2009 21:43 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
@ Blackswan,

Quote:
*Action: Issue OCI define calls for the columns to be fetched.


I did found this on google but did not understand enough to implement that in my case.

Please elaborate what action should be done in my scenario.

Thanks a lot for your time.

- Scott.
Re: ORA-24374: define not done before fetch or execute and fetch [message #410562 is a reply to message #410556] Sun, 28 June 2009 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Is the problem in the code you have posted or code not posted?

How can we reproduce your problem?
Re: ORA-24374: define not done before fetch or execute and fetch [message #410564 is a reply to message #410556] Sun, 28 June 2009 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So my question is: Do I need to open the cursor every time I pass it into a new program unit or else am I missing something?

Yes you have as the previous call fetched the rows from the previous cursor.
Of course the result of the second query/open might not be the same of the previous one.

Regards
Michel
Re: ORA-24374: define not done before fetch or execute and fetch [message #410569 is a reply to message #410556] Mon, 29 June 2009 00:46 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
@ Michel,

Quote:
Yes you have as the previous call fetched the rows from the previous cursor.


I thought when we use the following statement, it just identifies the active set from the database and does not fetch any data.

  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;


So is there any other way to open the ref cursor just once at the starting of the code, and pass it as a parameter to different procedures?

Thanks for all your time.

- Scott.


Re: ORA-24374: define not done before fetch or execute and fetch [message #410571 is a reply to message #410569] Mon, 29 June 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I thought when we use the following statement, it just identifies the active set from the database and does not fetch any data.

Yes it does but if the procedure you call use it then it fetches the data from the cursor.

Quote:
So is there any other way to open the ref cursor just once at the starting of the code, and pass it as a parameter to different procedures?

No.
You can put the result in an array and pass it to the procedures.
BUt given the names of your function, in your case I bet you can do it in a single statement. Also here's a hint: "rethink if you can't do it in another way".

Regards
Michel
Re: ORA-24374: define not done before fetch or execute and fetch [message #410650 is a reply to message #410556] Mon, 29 June 2009 09:12 Go to previous message
oracle123
Messages: 56
Registered: March 2009
Member
@ Michel/Blackswan,

Thanks a lot for resolving my problem. I really appreciate all the time you take to resolve issues in this forum.

- Scott.
Previous Topic: Upper Function
Next Topic: How to find a column by sampling data
Goto Forum:
  


Current Time: Sun Dec 04 02:20:41 CST 2016

Total time taken to generate the page: 0.10399 seconds