Home » SQL & PL/SQL » SQL & PL/SQL » inconsistent datatype ora:00932 (10g)
inconsistent datatype ora:00932 [message #388788] Thu, 26 February 2009 04:08 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
Hi,

I have created an object of table type.
I have a refcursor whose records i am going to insert in that table type object.

Cursor structure and table type object structure is totally same.

Still i am getting inconsistent datatype error while fetching records from cursor to table type object...
Re: inconsistent datatype ora:00932 [message #388791 is a reply to message #388788] Thu, 26 February 2009 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either you show us your code, either you fix it by yourself.

Regards
Michel
Re: inconsistent datatype ora:00932 [message #388802 is a reply to message #388791] Thu, 26 February 2009 04:36 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
declare
v_bv_test bv_test1 := bv_test1();
l_bv_query VARCHAR2 (20000);
TYPE bv_cur IS REF CURSOR;
c12 bv_cur;
CURSOR c1
IS
SELECT *
FROM xxcss_pbm_tmp_sttr_qtc_bv_tb
WHERE request_id =841;
BEGIN
FOR i IN c1
LOOP
v_bv_test.EXTEND;
l_bv_query :=
'SELECT DISTINCT tmp.order_number order_number,
tmp.header_id order_header_id, tmp.org_id om_org_id,
tmp.om_ou om_ou, tmp.NAME order_source,
tmp.orig_sys_document_ref ec_order_number,
tmp.cust_po_number customer_po_number,
tmp.contract_quote_number quote_number,
tmp.attribute4 revenue_type, tmp.line_id line_id,
tmp.lineid lineid, tmp.line_number line_number,
oet.NAME line_type_name,
tmp.attribute3 contract_number, msi.segment1 item,
tmp.cancelled_quantity cancelled_quantity,
tmp.ordered_quantity ordered_quantity,
bid_hl.country country,
tmp.account_number customer_number,
tmp.party_name invoice_to_customer_name,
tmp.invoice_to_org_id invoice_to_site_use_id,
tmp.flow_status_code line_status,
tmp.ordered_date ordered_date,
tmp.creation_date order_header_create_date,
tmp.last_update_date order_header_update_date,
tmp.booked_date booked_date,
tmp.booked_flag booked_flag,
tmp.cisco_book_date cisco_booked_date,
tmp.cisco_book_result_code cisco_booked_result_code,
ohd.NAME hold_name,
ohr.creation_date hold_release_date,
tmp.receivable_intf_date interfaced_to_ar_date,
rah.trx_number invoice_number,
rah.creation_date invoice_creation_date,
rat.NAME sales_territory,
rat.description sales_territory_description,
osct.NAME sales_credit_type, pctm.theater theater,
tmp.attribute8 deal_id
FROM xxcss_pbm_tmp_sttr_qtc_bv_tb tmp,
oe_sales_credit_types osct,
ra_territories rat,
csm_hierarchy ch,
oe_order_holds_all ooha,
oe_hold_releases ohr,
oe_hold_sources_all ohsa,
oe_hold_definitions ohd,
oe_transaction_types_tl oet,
hz_party_sites bid_hps,
hz_locations bid_hl,
xxcss_pbm_country_theater_map pctm,
ra_customer_trx_all rah,
mtl_system_items_b msi
WHERE 1 = 1
AND pctm.theater IN
(''EM-EAST'', ''APAC'', ''Canada'', ''Japan'', ''USA'',
''EM-LATAM'', ''European Market'')
AND tmp.request_id = :req_id
AND osct.sales_credit_type_id=:sales_credit_type_id
AND oet.transaction_type_id= :line_type_id
AND rat.territory_id=:territory_id
AND rat.territory_id = ch.territory_id
AND ooha.header_id=:header_id
AND (ooha.line_id=:line_id or ooha.line_id IS NULL)
AND ooha.hold_release_id = ohr.hold_release_id(+)
AND ooha.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id
AND bid_hps.party_id(+)=:party_id
AND NVL (bid_hps.party_site_id, 1)=nvl(:party_site_id, 1)
AND bid_hps.location_id = bid_hl.location_id(+)
AND bid_hl.country = pctm.country_code(+)
AND rah.interface_header_attribute1(+)=to_char(:order_number)
AND msi.organization_id=:ship_from_org_id
AND msi.inventory_item_id=:inventory_item_id
AND tmp.header_seq_id =
(SELECT MAX (header_seq_id)
FROM xxcss_pbm_tmp_sttr_qtc_bv_tb tmp1
WHERE tmp1.request_id = :req_id
AND tmp1.source_header_id =
:source_header_id)';
OPEN c12 FOR l_bv_query
USING i.request_id,
i.sales_credit_type_id,
i.line_type_id,
i.territory_id,
i.header_id,
i.line_id,
i.party_id,
i.party_site_id,
i.order_number,
i.ship_from_org_id,
i.inventory_item_id,
i.request_id,
i.source_header_id;

begin
fetch c12 bulk collect into v_bv_test;
dbms_output.put_line('inside loop');
close c12;
end;
END LOOP;
END;




v_bv_test structure is :


CREATE OR REPLACE TYPE APPS.bv_test AS OBJECT (
ORDER_NUMBER NUMBER,
ORDER_HEADER_ID NUMBER,
OM_ORG_ID NUMBER,
OM_OU VARCHAR2(30 BYTE),
ORDER_SOURCE VARCHAR2(240 BYTE),
EC_ORDER_NUMBER VARCHAR2(50 BYTE),
CUSTOMER_PO_NUMBER VARCHAR2(50 BYTE),
QUOTE_NUMBER VARCHAR2(30 BYTE),
REVENUE_TYPE VARCHAR2(40 BYTE),
LINE_ID VARCHAR2(30 BYTE),
LINEID VARCHAR2(30 BYTE),
LINE_NUMBER NUMBER,
LINE_TYPE_NAME VARCHAR2(90 BYTE) ,
CONTRACT_NUMBER VARCHAR2(24 BYTE),
ITEM VARCHAR2(120 BYTE),
CANCELLED_QUANTITY NUMBER,
ORDERED_QUANTITY NUMBER,
COUNTRY VARCHAR2(180 BYTE),
CUSTOMER_NUMBER VARCHAR2(30 BYTE),
INVOICE_TO_CUSTOMER_NAME VARCHAR2(360 BYTE),
INVOICE_TO_SITE_USE_ID NUMBER,
LINE_STATUS VARCHAR2(30 BYTE),
ORDERED_DATE DATE,
ORDER_HEADER_CREATE_DATE DATE,
ORDER_HEADER_UPDATE_DATE DATE,
BOOKED_DATE DATE,
BOOKED_FLAG VARCHAR2(1 BYTE),
CISCO_BOOKED_DATE DATE,
CISCO_BOOKED_RESULT_CODE VARCHAR2(30 BYTE),
HOLD_NAME VARCHAR2(720 BYTE),
HOLD_RELEASE_DATE DATE,
INTERFACED_TO_AR_DATE DATE,
INVOICE_NUMBER VARCHAR2(60 BYTE),
INVOICE_CREATION_DATE DATE,
SALES_TERRITORY VARCHAR2(120 BYTE),
SALES_TERRITORY_DESCRIPTION VARCHAR2(720 BYTE),
SALES_CREDIT_TYPE VARCHAR2(720 BYTE),
THEATER VARCHAR2(240 BYTE),
DEAL_ID VARCHAR2(150 BYTE)
)
/



CREATE OR REPLACE TYPE APPS.bv_test1 as TABLE oF bv_test

[Updated on: Thu, 26 February 2009 04:40]

Report message to a moderator

Re: inconsistent datatype ora:00932 [message #388822 is a reply to message #388788] Thu, 26 February 2009 05:35 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I find it helpful when putting large chunks of code in a thread to use code tags - see the orafaq forum guide for details.

I also find it helpful when using ref cursors to actually tell oracle what select statement to use.
Previous Topic: Using Spool & double quote to CSV file
Next Topic: current_date and sysdate
Goto Forum:
  


Current Time: Sat Dec 10 15:07:45 CST 2016

Total time taken to generate the page: 0.07435 seconds