Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How To Fetch Data From The Refcursor

Rajan SwJun 25 2014 — edited Jun 25 2014

Hi Gurus,

I have declared a ref cursor type in the package specification like below

create or replace PACKAGE atchley_mir_extract AS

  TYPE AtchleyExtractCurType IS REF CURSOR;

  PROCEDURE monetary_instrument_extract(cdate IN CHAR, monInstrumentExtract_cv OUT AtchleyExtractCurType);

END atchley_mir_extract;

and I am trying to use this procedure as part of the below anonymous block and getting error , Please share the thoughts to rectify it.

I can not declare the record datatype as it contains many columns

declare

v2 atchley_mir_extract.AtchleyExtractCurType;

v3 atchley_mir_extract.AtchleyExtractCurType;

v_usr tptux.users.user_name%Type;

v_cost tptux.Transaction_Journal.cost_center%Type;

--v_cust_nb tptux.Transaction_Journal.customer_nbr%Type;

begin

Atchley_Mir_Extract.Monetary_Instrument_Extract('06-18-14',v2);

open v2;

LOOP

FETCH v2 INTO v3 ;

 

exit when v2%notfound;

--DBMS_OUTPUT.PUT_LINE('User is :'||v_usr||' and The cost center is ');

END LOOP;

 

end; 

ror report -

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

ORA-06512: at line 14

06504. 00000 -  "PL/SQL: Return types of Result Set variables or query do not match"

*Cause:    Number and/or types of columns in a query does not match declared

           return type of a result set  variable, or declared types of two Result

           Set variables do not match.

*Action:   Change the program statement or declaration. Verify what query the variable

           actually refers to during execution.

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 23 2014
Added on Jun 25 2014
7 comments
5,168 views