Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Fetch
Bulk Fetch [message #193542] Mon, 18 September 2006 02:58 Go to next message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
HI,

Iam trying to bulk fetch using a cursor Expression.
Iam getting the error
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got CURSER

But If I execute the same cursor query outside at the sqlplus Iam getting the result.

CURSOR QUERY:

SELECT key_Ingredient.description,key_Ingredient.key_ingredient_id,
CURSOR(
SELECT a.drug_formulation_code,b.description
FROM ki_formulation a,drug_formulation b
WHERE a.key_ingredient_id =
key_Ingredient.key_Ingredient_Id
AND a.drug_formulation_code = b.drug_formulation_code
)

Can u give me the solution for this.


SQL> desc keyIngredient_nt
KEYINGREDIENT_NT TABLE OF KEYINGREDIENT_TY

Name Null? Type
----------------------------------------- -------- -------------------------
DESCRIPTION VARCHAR2(70)
KEYINGREDIENTID NUMBER(15)
ROUTEOFADMINISTRATION KEYVALUE_NT
DRUGFORMULATION KEYVALUE_NT
ENTRYDATETIME DATE
MODIFIEDDATETIME DATE
STATUS NUMBER(2)
USERID VARCHAR2(20)
REASON VARCHAR2(2000)
DATAENTRYSITE VARCHAR2(20)
USERDESCOUNTRY KEYVALUE_TY


SQL> desc keyvalue_nt
KEYVALUE_NT TABLE OF KEYVALUE_TY
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE VARCHAR2(20)
DESCRIPTION VARCHAR2(100)


OPEN cur_query FOR SELECT keyIngredient_ty(key_Ingredient.description,key_Ingredient.key_ingredient_id,
CURSOR( SELECT keyvalue_ty
(a.drug_formulation_code,b.description)
FROM ki_formulation a,drug_formulation b
WHERE a.key_ingredient_id =
key_Ingredient.key_Ingredient_Id
AND a.drug_formulation_code =
b.drug_formulation_code)
,null,null,null,null,null,null,null,null)
FROM key_Ingredient;

FETCH cur_query BULK COLLECT INTO l_keyIngredient;

CLOSE cur_query;

I can do this by looping for each key ingredient and bulk fetching the drugformulation object (KEYVALUE_NT) inside the key ingredient object,but as it is a performance problem i want to avoid since there are more than 1 lakh ingredients present for which i have to loop.


thanks,
Raghavendra





Re: Bulk Fetch [message #193561 is a reply to message #193542] Mon, 18 September 2006 05:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Not sure what you are trying to do ...

Anyway Try like ...

OPEN cur_query FOR SELECT keyIngredient_ty(key_Ingredient.description,key_Ingredient.key_ingredient_id,
 (SELECT keyvalue_ty 
(a.drug_formulation_code,b.description)
FROM ki_formulation a,drug_formulation b
WHERE a.key_ingredient_id = 
key_Ingredient.key_Ingredient_Id
AND a.drug_formulation_code = 
b.drug_formulation_code )
,null,null,null,null,null,null,null,null)
FROM key_Ingredient


Or Use CAST function ...

Thumbs Up
Rajuvan.

[Updated on: Mon, 18 September 2006 05:05]

Report message to a moderator

Re: Bulk Fetch [message #193993 is a reply to message #193561] Wed, 20 September 2006 05:54 Go to previous messageGo to next message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
Hi tried using the above method its comiling but throwing the below error
PL/SQL: ORA-00932: inconsistent datatypes: expected CDSS_V12.KEYVALUE_TY got CDSS_V12.KEYVALUE_NT

I also tried using the CAST function. Even here its compiling but throwing the exception as below:
OPEN cur_query FOR SELECT
keyingredient_Ty(key_Ingredient.description,key_Ingredient.key_ingredient_id,
CAST(MULTISET(SELECT a.drug_formulation_code,b.description
FROM ki_formulation a,drug_formulation b
WHERE a.key_ingredient_id = key_Ingredient.key_Ingredient_Id
AND a.drug_formulation_code = b.drug_formulation_code ) AS keyvalue_nt)
,null,null,null,null,null,null,null,null)
FROM key_Ingredient;

FETCH cur_query BULK COLLECT INTO l_ki;

Exception:
ORA-00932:inconsistent data types expected - got ORA-06512;

Can u tell me hats the problem.

Thanks
Raghavendra
Re: Bulk Fetch [message #194034 is a reply to message #193542] Wed, 20 September 2006 08:54 Go to previous message
yraghavendra
Messages: 27
Registered: August 2005
Location: India
Junior Member
Hi Guys ,
I got the solution.
Thanks

Previous Topic: ORA-01722
Next Topic: Quick question about statistics
Goto Forum:
  


Current Time: Sun Dec 04 12:25:47 CST 2016

Total time taken to generate the page: 0.08606 seconds