varray in procedure
448125Jul 25 2005 — edited Jul 26 2005Have procedure that passes to and from "c" external code, a varray. Want to now insert each element of the varray into an existing database table...How do you loop to process each varray element to insert into an existing oracle db table???
We defined the following:
CREATE TYPE TAX_DETAIL as OBJECT (
tax_amt number (11,5),
tax_type varchar2(2),
tax_authority varchar2(1))
/
CREATE TYPE TAX_DETAIL_VARRAY
AS VARYING ARRAY (20) OF "TAX_DETAIL"
/
CREATE TABLE TMP_TAX_DETAILS
( BILL_YEAR NUMBER (4, 0) NOT NULL,
BILL_PERIOD NUMBER (2, 0) NOT NULL,
INVOICE_NO NUMBER NOT NULL,
APPLICATION_CD VARCHAR2 (3) NOT NULL,
DEVICE_CD VARCHAR2 (16) NOT NULL,
GEO_CD VARCHAR2 (9) NOT NULL,
INSERT_DATE DATE DEFAULT sysdate NOT NULL,
LAST_UPDATE DATE DEFAULT sysdate NOT NULL,
USER_ID VARCHAR2 (10) NOT NULL,
TAX_YEAR NUMBER (4, 0) NOT NULL,
TAX_PERIOD NUMBER (2, 0) NOT NULL,
TAX_CODE VARCHAR2 (1),
TAX_DET_LIST TAX_DETAIL_VARRAY )
/
And passing to "c" external procedue:
p_contract_id,
v_geo_cd,
v_geo_cd,
v_geo_cd,
p_invoice_date,
p_invoice_no,
p_taxable_amt,
p_vertex_tax_category,
p_vertex_tax_service,
p_exemption_flg,
p_resale_cd,
p_tax_amt,
TAX_DET_LIST ,
v_table_occur,
v_return_cd
);