Home » SQL & PL/SQL » SQL & PL/SQL » Creating multiple level nested tables (merged 2 threads)
Creating multiple level nested tables (merged 2 threads) [message #282712] Fri, 23 November 2007 02:45 Go to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
hi

I face a problem while creating multiple level nested table.

anyone help me in solving it...

CREATE OR REPLACE TYPE DISCOUNT_DETAIL AS OBJECT(
DISC_CODE NUMBER(15),
DISC_END_DATE DATE,
DISC_START_DATE NUMBER(10,2),
DISC_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE DISCOUNT_TABLE AS TABLE OF DISCOUNT_DETAIL;

CREATE OR REPLACE TYPE SUPPLEMENT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE SUPPLEMENT_TABLE AS TABLE OF SUPPLEMENT_DETAIL;

CREATE OR REPLACE TYPE AUDIT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE AUDIT_TABLE AS TABLE OF AUDIT_DETAIL;

CREATE OR REPLACE TYPE NVP_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NVP_TABLE AS TABLE OF NVP_DETAIL;

CREATE OR REPLACE TYPE CUSTOMER_DETAIL AS OBJECT(
CUSTOMER_ID NUMBER(9),
CENTRICA_VALUE_CODE VARCHAR2(Cool,
NUM_LIVE_CONTRACTS NUMBER(5));


CREATE OR REPLACE TYPE PREMISES_DETAIL AS OBJECT(
PREMISE_ID VARCHAR2(7),
ZIPC_CODE VARCHAR2(10),
POST_IN_CODE VARCHAR2(10),
NUMBER_OF_BEDROOMS NUMBER(3),
HOME_SEC_IND VARCHAR2(1));


CREATE OR REPLACE TYPE APPLIANCE_DETAIL_TYP AS OBJECT(
STYP_CODE VARCHAR2(4),
CYLINDER_TYPE VARCHAR2(4),
ACTIVATED_DATE DATE,
INSTALLED_DATE DATE,
FLUE_TYPE VARCHAR2(1),
GCN VARCHAR2(10),
GURAN_END_DATE DATE,
INVENTORY_CODE VARCHAR2(20),
LAST_YEAR_JOB_COUNT NUMBER(3),
NUM_RADS NUMBER(4),
NUM_SPL_RADS NUMBER(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
THIS_YEAR_JOB_COUNT NUMBER(3),
WSUP_CODE VARCHAR2(4),
YEARS_WITHOUT_CLAIM NUMBER(3),
CHIRP_FLAG varchar2(1),
Appl_Audit_Details AUDIT_TABLE,
Appl_NVP NVP_TABLE);

CREATE OR REPLACE TYPE NT_APPL_TYP AS TABLE OF APPLIANCE_DETAIL_TYP;


CREATE OR REPLACE TYPE PRODUCT_DETAIL_TYP AS OBJECT(
PRODUCT_NO NUMBER(15),
REAS_CODE VARCHAR2(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
NEW_PROD_IND VARCHAR2(1),
CHARGE_TYPE VARCHAR2(1),
CHOICES_IND VARCHAR2(2),
CHOICES_LINK VARCHAR2(3),
DATE_ADDED DATE,
PRICING_DATE DATE,
REPRICE_IND VARCHAR2(1),
Prod_Disc_Details DISCOUNT_TABLE,
Prod_Suppl_Details SUPPLEMENT_TABLE,
PRIOR_AMT NUMBER(10,2),
BASE_PRICE NUMBER(10,2),
HEADLINE_PRICE NUMBER(10,2),
FULL_YEAR_PRICE NUMBER(10,2),
PRODUCT_CHARGE NUMBER(10,2),
RISK_ADJ NUMBER(10,2),
TOT_PROD_DISC NUMBER(10,2),
Prod_Audit_Details AUDIT_TABLE,
Prod_NVP NVP_TABLE,
Appliances NT_APPL_TYP);


CREATE OR REPLACE TYPE NT_PROD_TYP AS TABLE OF PRODUCT_DETAIL_TYP

CREATE OR REPLACE TYPE CONTRACT_DETAIL AS OBJECT(
QUOTE_NUMBER NUMBER(15),
SCON_NUMBER NUMBER(15),
ANNIVERSARY_DATE VARCHAR2(4),
BULK_IND VARCHAR2(4),
PRICING_DATE VARCHAR2(1),
END_DATE VARCHAR2(1),
START_DATE VARCHAR2(2),
LOYALTY_POINTS VARCHAR2(3),
NUM_PAYMENTS DATE,
PAYMENT_CODE DATE,
Cont_Disc_Details DISCOUNT_TABLE,
Cont_Supp_Details SUPPLEMENT_TABLE,
REAS_CODE_CHANGE VARCHAR2(4),
TOT_CONTRACT_DISC NUMBER(10,2),
TOT_PROD_CHARGE NUMBER(10,2),
ANNUAL_CHARGE NUMBER(10,2),
Cont_Audit_Details AUDIT_TABLE,
Cont_NVP NVP_TABLE,
Products NT_PROD_TYP)


CREATE OR REPLACE TYPE NT_CONT_TYP AS TABLE OF CONTRACT_DETAIL_TYP

CREATE OR REPLACE TABLE PRICE_XML_TABLE (MESSAGE_ID VARCHAR2(20),
USER_ID VARCHAR2(20),
REQUEST_ACTION VARCHAR2(50),
REQUEST_TYPE NUMBER(3),
RULE_SET_VERSION NUMBER(3),
AUDIT_DETAILS AUDIT_TABLE,
CUSTOMER CUSTOMER_DETAIL,
PREMISE PREMISES_DETAIL,
CONTRACTS NT_CONT_TYP)
NESTED TABLE CONTRACTS STORE AS nt_contracts_tab
(NESTED TABLE Cont_NVP STORE AS nt_contNVP_tab
NESTED TABLE Cont_Audit_Details STORE AS nt_contAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_contSupp_tab
NESTED TABLE Cont_Disc_Details STORE AS nt_contDisc_tab
NESTED TABLE Products STORE AS nt_product_tab
(NESTED TABLE Prod_NVP STORE AS nt_prodNVP_tab
NESTED TABLE Prod_Audit_Details STORE AS nt_prodAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_prodSupp_tab
NESTED TABLE Prod_Disc_Details STORE AS nt_prodDisc_tab
NESTED TABLE Appliances STORE AS nt_contracts_tab
(NESTED TABLE Appl_NVP STORE AS nt_applNVP_tab
NESTED TABLE Appl_Audit_Details STORE AS nt_applAudit_tab)))


i get the an invalid datatype error on NT_CONT_TYP which is highlited in red.

thanks,
sathiya

Re: Multi Level Nested Tables [message #282714 is a reply to message #282712] Fri, 23 November 2007 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't hijack a topic, create your own.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Creating multiple level nested tables. [message #282715 is a reply to message #282712] Fri, 23 November 2007 02:49 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
hi

I face a problem while creating multiple level nested table.

i'am using oracle version 9.

anyone help me in solving it...

CREATE OR REPLACE TYPE DISCOUNT_DETAIL AS OBJECT(
DISC_CODE NUMBER(15),
DISC_END_DATE DATE,
DISC_START_DATE NUMBER(10,2),
DISC_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE DISCOUNT_TABLE AS TABLE OF DISCOUNT_DETAIL;

CREATE OR REPLACE TYPE SUPPLEMENT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE SUPPLEMENT_TABLE AS TABLE OF SUPPLEMENT_DETAIL;

CREATE OR REPLACE TYPE AUDIT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE AUDIT_TABLE AS TABLE OF AUDIT_DETAIL;

CREATE OR REPLACE TYPE NVP_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NVP_TABLE AS TABLE OF NVP_DETAIL;

CREATE OR REPLACE TYPE CUSTOMER_DETAIL AS OBJECT(
CUSTOMER_ID NUMBER(9),
CENTRICA_VALUE_CODE VARCHAR2(,
NUM_LIVE_CONTRACTS NUMBER(5));


CREATE OR REPLACE TYPE PREMISES_DETAIL AS OBJECT(
PREMISE_ID VARCHAR2(7),
ZIPC_CODE VARCHAR2(10),
POST_IN_CODE VARCHAR2(10),
NUMBER_OF_BEDROOMS NUMBER(3),
HOME_SEC_IND VARCHAR2(1));


CREATE OR REPLACE TYPE APPLIANCE_DETAIL_TYP AS OBJECT(
STYP_CODE VARCHAR2(4),
CYLINDER_TYPE VARCHAR2(4),
ACTIVATED_DATE DATE,
INSTALLED_DATE DATE,
FLUE_TYPE VARCHAR2(1),
GCN VARCHAR2(10),
GURAN_END_DATE DATE,
INVENTORY_CODE VARCHAR2(20),
LAST_YEAR_JOB_COUNT NUMBER(3),
NUM_RADS NUMBER(4),
NUM_SPL_RADS NUMBER(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
THIS_YEAR_JOB_COUNT NUMBER(3),
WSUP_CODE VARCHAR2(4),
YEARS_WITHOUT_CLAIM NUMBER(3),
CHIRP_FLAG varchar2(1),
Appl_Audit_Details AUDIT_TABLE,
Appl_NVP NVP_TABLE);

CREATE OR REPLACE TYPE NT_APPL_TYP AS TABLE OF APPLIANCE_DETAIL_TYP;


CREATE OR REPLACE TYPE PRODUCT_DETAIL_TYP AS OBJECT(
PRODUCT_NO NUMBER(15),
REAS_CODE VARCHAR2(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
NEW_PROD_IND VARCHAR2(1),
CHARGE_TYPE VARCHAR2(1),
CHOICES_IND VARCHAR2(2),
CHOICES_LINK VARCHAR2(3),
DATE_ADDED DATE,
PRICING_DATE DATE,
REPRICE_IND VARCHAR2(1),
Prod_Disc_Details DISCOUNT_TABLE,
Prod_Suppl_Details SUPPLEMENT_TABLE,
PRIOR_AMT NUMBER(10,2),
BASE_PRICE NUMBER(10,2),
HEADLINE_PRICE NUMBER(10,2),
FULL_YEAR_PRICE NUMBER(10,2),
PRODUCT_CHARGE NUMBER(10,2),
RISK_ADJ NUMBER(10,2),
TOT_PROD_DISC NUMBER(10,2),
Prod_Audit_Details AUDIT_TABLE,
Prod_NVP NVP_TABLE,
Appliances NT_APPL_TYP);


CREATE OR REPLACE TYPE NT_PROD_TYP AS TABLE OF PRODUCT_DETAIL_TYP

CREATE OR REPLACE TYPE CONTRACT_DETAIL AS OBJECT(
QUOTE_NUMBER NUMBER(15),
SCON_NUMBER NUMBER(15),
ANNIVERSARY_DATE VARCHAR2(4),
BULK_IND VARCHAR2(4),
PRICING_DATE VARCHAR2(1),
END_DATE VARCHAR2(1),
START_DATE VARCHAR2(2),
LOYALTY_POINTS VARCHAR2(3),
NUM_PAYMENTS DATE,
PAYMENT_CODE DATE,
Cont_Disc_Details DISCOUNT_TABLE,
Cont_Supp_Details SUPPLEMENT_TABLE,
REAS_CODE_CHANGE VARCHAR2(4),
TOT_CONTRACT_DISC NUMBER(10,2),
TOT_PROD_CHARGE NUMBER(10,2),
ANNUAL_CHARGE NUMBER(10,2),
Cont_Audit_Details AUDIT_TABLE,
Cont_NVP NVP_TABLE,
Products NT_PROD_TYP)


CREATE OR REPLACE TYPE NT_CONT_TYP AS TABLE OF CONTRACT_DETAIL_TYP

CREATE OR REPLACE TABLE PRICE_XML_TABLE (MESSAGE_ID VARCHAR2(20),
USER_ID VARCHAR2(20),
REQUEST_ACTION VARCHAR2(50),
REQUEST_TYPE NUMBER(3),
RULE_SET_VERSION NUMBER(3),
AUDIT_DETAILS AUDIT_TABLE,
CUSTOMER CUSTOMER_DETAIL,
PREMISE PREMISES_DETAIL,
CONTRACTS NT_CONT_TYP)
NESTED TABLE CONTRACTS STORE AS nt_contracts_tab
(NESTED TABLE Cont_NVP STORE AS nt_contNVP_tab
NESTED TABLE Cont_Audit_Details STORE AS nt_contAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_contSupp_tab
NESTED TABLE Cont_Disc_Details STORE AS nt_contDisc_tab
NESTED TABLE Products STORE AS nt_product_tab
(NESTED TABLE Prod_NVP STORE AS nt_prodNVP_tab
NESTED TABLE Prod_Audit_Details STORE AS nt_prodAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_prodSupp_tab
NESTED TABLE Prod_Disc_Details STORE AS nt_prodDisc_tab
NESTED TABLE Appliances STORE AS nt_contracts_tab
(NESTED TABLE Appl_NVP STORE AS nt_applNVP_tab
NESTED TABLE Appl_Audit_Details STORE AS nt_applAudit_tab)))


i get the an invalid datatype error on NT_CONT_TYP which is highlited in red.

thanks,
sathiya
Re: Creating multiple level nested tables. [message #282717 is a reply to message #282715] Fri, 23 November 2007 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Creating multiple level nested tables. [message #282721 is a reply to message #282717] Fri, 23 November 2007 03:02 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
ya here is my formatted message..sorry for that..

hi

I face a problem while creating multiple level nested table.

i'am using oracle version 9.

anyone help me in solving it...
CREATE OR REPLACE TYPE DISCOUNT_DETAIL AS OBJECT(
DISC_CODE NUMBER(15),
DISC_END_DATE DATE,
DISC_START_DATE NUMBER(10,2),
DISC_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE DISCOUNT_TABLE AS TABLE OF DISCOUNT_DETAIL;

CREATE OR REPLACE TYPE SUPPLEMENT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE SUPPLEMENT_TABLE AS TABLE OF SUPPLEMENT_DETAIL;

CREATE OR REPLACE TYPE AUDIT_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE AUDIT_TABLE AS TABLE OF AUDIT_DETAIL;

CREATE OR REPLACE TYPE NVP_DETAIL AS OBJECT(
SUPP_CODE NUMBER(15),
SUPP_END_DATE DATE,
SUPP_START_DATE NUMBER(10,2),
SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NVP_TABLE AS TABLE OF NVP_DETAIL;

CREATE OR REPLACE TYPE CUSTOMER_DETAIL AS OBJECT(
CUSTOMER_ID NUMBER(9),
CENTRICA_VALUE_CODE VARCHAR2(,
NUM_LIVE_CONTRACTS NUMBER(5));


CREATE OR REPLACE TYPE PREMISES_DETAIL AS OBJECT(
PREMISE_ID VARCHAR2(7),
ZIPC_CODE VARCHAR2(10),
POST_IN_CODE VARCHAR2(10),
NUMBER_OF_BEDROOMS NUMBER(3),
HOME_SEC_IND VARCHAR2(1));


CREATE OR REPLACE TYPE APPLIANCE_DETAIL_TYP AS OBJECT(
STYP_CODE VARCHAR2(4),
CYLINDER_TYPE VARCHAR2(4),
ACTIVATED_DATE DATE,
INSTALLED_DATE DATE,
FLUE_TYPE VARCHAR2(1),
GCN VARCHAR2(10),
GURAN_END_DATE DATE,
INVENTORY_CODE VARCHAR2(20),
LAST_YEAR_JOB_COUNT NUMBER(3),
NUM_RADS NUMBER(4),
NUM_SPL_RADS NUMBER(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
THIS_YEAR_JOB_COUNT NUMBER(3),
WSUP_CODE VARCHAR2(4),
YEARS_WITHOUT_CLAIM NUMBER(3),
CHIRP_FLAG varchar2(1),
Appl_Audit_Details AUDIT_TABLE,
Appl_NVP NVP_TABLE);

CREATE OR REPLACE TYPE NT_APPL_TYP AS TABLE OF APPLIANCE_DETAIL_TYP; 


CREATE OR REPLACE TYPE PRODUCT_DETAIL_TYP AS OBJECT(
PRODUCT_NO NUMBER(15),
REAS_CODE VARCHAR2(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
NEW_PROD_IND VARCHAR2(1),
CHARGE_TYPE VARCHAR2(1),
CHOICES_IND VARCHAR2(2),
CHOICES_LINK VARCHAR2(3),
DATE_ADDED DATE,
PRICING_DATE DATE,
REPRICE_IND VARCHAR2(1),
Prod_Disc_Details DISCOUNT_TABLE,
Prod_Suppl_Details SUPPLEMENT_TABLE,
PRIOR_AMT NUMBER(10,2),
BASE_PRICE NUMBER(10,2),
HEADLINE_PRICE NUMBER(10,2),
FULL_YEAR_PRICE NUMBER(10,2),
PRODUCT_CHARGE NUMBER(10,2),
RISK_ADJ NUMBER(10,2),
TOT_PROD_DISC NUMBER(10,2),
Prod_Audit_Details AUDIT_TABLE,
Prod_NVP NVP_TABLE,
Appliances NT_APPL_TYP);


CREATE OR REPLACE TYPE NT_PROD_TYP AS TABLE OF PRODUCT_DETAIL_TYP

CREATE OR REPLACE TYPE CONTRACT_DETAIL AS OBJECT(
QUOTE_NUMBER NUMBER(15),
SCON_NUMBER NUMBER(15),
ANNIVERSARY_DATE VARCHAR2(4),
BULK_IND VARCHAR2(4),
PRICING_DATE VARCHAR2(1),
END_DATE VARCHAR2(1),
START_DATE VARCHAR2(2),
LOYALTY_POINTS VARCHAR2(3),
NUM_PAYMENTS DATE,
PAYMENT_CODE DATE,
Cont_Disc_Details DISCOUNT_TABLE,
Cont_Supp_Details SUPPLEMENT_TABLE,
REAS_CODE_CHANGE VARCHAR2(4), 
TOT_CONTRACT_DISC NUMBER(10,2),
TOT_PROD_CHARGE NUMBER(10,2),
ANNUAL_CHARGE NUMBER(10,2),
Cont_Audit_Details AUDIT_TABLE,
Cont_NVP NVP_TABLE,
Products NT_PROD_TYP)


CREATE OR REPLACE TYPE NT_CONT_TYP AS TABLE OF CONTRACT_DETAIL_TYP

CREATE OR REPLACE TABLE PRICE_XML_TABLE (MESSAGE_ID VARCHAR2(20),
USER_ID VARCHAR2(20),
REQUEST_ACTION VARCHAR2(50),
REQUEST_TYPE NUMBER(3),
RULE_SET_VERSION NUMBER(3),
AUDIT_DETAILS AUDIT_TABLE,
CUSTOMER CUSTOMER_DETAIL,
PREMISE PREMISES_DETAIL,
CONTRACTS NT_CONT_TYP
NESTED TABLE CONTRACTS STORE AS nt_contracts_tab
(NESTED TABLE Cont_NVP STORE AS nt_contNVP_tab
NESTED TABLE Cont_Audit_Details STORE AS nt_contAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_contSupp_tab
NESTED TABLE Cont_Disc_Details STORE AS nt_contDisc_tab
NESTED TABLE Products STORE AS nt_product_tab
(NESTED TABLE Prod_NVP STORE AS nt_prodNVP_tab
NESTED TABLE Prod_Audit_Details STORE AS nt_prodAudit_tab
NESTED TABLE Cont_Supp_Details STORE AS nt_prodSupp_tab
NESTED TABLE Prod_Disc_Details STORE AS nt_prodDisc_tab
NESTED TABLE Appliances STORE AS nt_contracts_tab
(NESTED TABLE Appl_NVP STORE AS nt_applNVP_tab
NESTED TABLE Appl_Audit_Details STORE AS nt_applAudit_tab)))

i get the an invalid datatype error on NT_CONT_TYP type which is used in creating the PRICE_XML_TABLE table.

thanks,
sathiya
Re: Creating multiple level nested tables. [message #282723 is a reply to message #282721] Fri, 23 November 2007 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format does not just mean adding code tags but also indent in a pretty way.

"9" is only one decimal and not four.

Regards
Michel

[Updated on: Fri, 23 November 2007 03:08]

Report message to a moderator

Re: Creating multiple level nested tables. [message #282724 is a reply to message #282721] Fri, 23 November 2007 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE OR REPLACE TYPE CUSTOMER_DETAIL AS OBJECT(
CUSTOMER_ID NUMBER(9),
CENTRICA_VALUE_CODE VARCHAR2(,
NUM_LIVE_CONTRACTS NUMBER(5));

Invalid statement "VARCHAR2("

CREATE OR REPLACE TYPE APPLIANCE_DETAIL_TYP AS OBJECT(
STYP_CODE VARCHAR2(4),
CYLINDER_TYPE VARCHAR2(4),
ACTIVATED_DATE DATE,
INSTALLED_DATE DATE,
FLUE_TYPE VARCHAR2(1),
GCN VARCHAR2(10),
GURAN_END_DATE DATE,
INVENTORY_CODE VARCHAR2(20),
LAST_YEAR_JOB_COUNT NUMBER(3),
NUM_RADS NUMBER(4),
NUM_SPL_RADS NUMBER(4),
SRVC_CODE VARCHAR2(4),
STYP_CODE VARCHAR2(4),
THIS_YEAR_JOB_COUNT NUMBER(3),
WSUP_CODE VARCHAR2(4),
YEARS_WITHOUT_CLAIM NUMBER(3),
CHIRP_FLAG varchar2(1),
Appl_Audit_Details AUDIT_TABLE,
Appl_NVP NVP_TABLE);
/

Contains STYP_CODE twice.

Regards
Michel
Re: Creating multiple level nested tables. [message #283057 is a reply to message #282724] Mon, 26 November 2007 00:52 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
hi Michel,

I have made the corrections in the create scripts, but now i get a new error.

Can you please help me out?


CREATE OR REPLACE TYPE DISCOUNT_DETAIL_TYP AS OBJECT(
	DISC_CODE NUMBER(15),
	DISC_END_DATE DATE,
	DISC_START_DATE NUMBER(10,2),
	DISC_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NT_DISCOUNT_TYP AS TABLE OF DISCOUNT_DETAIL_TYP;

CREATE OR REPLACE TYPE SUPPLEMENT_DETAIL_TYP AS OBJECT(
	SUPP_CODE NUMBER(15),
	SUPP_END_DATE DATE,
	SUPP_START_DATE NUMBER(10,2),
	SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NT_SUPPLEMENT_TYP AS TABLE OF SUPPLEMENT_DETAIL_TYP;

CREATE OR REPLACE TYPE AUDIT_DETAIL_TYP AS OBJECT(
	SUPP_CODE NUMBER(15),
	SUPP_END_DATE DATE,
	SUPP_START_DATE NUMBER(10,2),
	SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NT_AUDIT_TYP AS TABLE OF AUDIT_DETAIL_TYP;

CREATE OR REPLACE TYPE NVP_DETAIL_TYP AS OBJECT(
	SUPP_CODE NUMBER(15),
	SUPP_END_DATE DATE,
	SUPP_START_DATE NUMBER(10,2),
	SUPP_AMOUNT NUMBER(10,2));

CREATE OR REPLACE TYPE NT_NVP_TYP AS TABLE OF NVP_DETAIL_TYP;

CREATE OR REPLACE TYPE CUSTOMER_DETAIL AS OBJECT(
	CUSTOMER_ID NUMBER(9),
	CENTRICA_VALUE_CODE VARCHAR2(8),
	NUM_LIVE_CONTRACTS NUMBER(5));


CREATE OR REPLACE TYPE PREMISES_DETAIL AS OBJECT(
	PREMISE_ID VARCHAR2(7),
	ZIPC_CODE VARCHAR2(10),
	POST_IN_CODE VARCHAR2(10),
	NUMBER_OF_BEDROOMS NUMBER(3),
	HOME_SEC_IND VARCHAR2(1));


CREATE OR REPLACE TYPE APPLIANCE_DETAIL_TYP AS OBJECT(
	STYP_CODE VARCHAR2(4),
	CYLINDER_TYPE VARCHAR2(4),
	ACTIVATED_DATE DATE,
	INSTALLED_DATE DATE,
	FLUE_TYPE VARCHAR2(1),
	GCN VARCHAR2(10),
	GURAN_END_DATE DATE,
	INVENTORY_CODE VARCHAR2(20),
	LAST_YEAR_JOB_COUNT NUMBER(3),
	NUM_RADS NUMBER(4),
	NUM_SPL_RADS NUMBER(4),
	SRVC_CODE VARCHAR2(4),
	THIS_YEAR_JOB_COUNT NUMBER(3),
	WSUP_CODE VARCHAR2(4),
	YEARS_WITHOUT_CLAIM NUMBER(3),
	CHIRP_FLAG varchar2(1),
	Appl_Audit_Details NT_AUDIT_TYP,
	Appl_NVP NT_NVP_TYP);

CREATE OR REPLACE TYPE NT_APPL_TYP AS TABLE OF APPLIANCE_DETAIL_TYP; 		
  

CREATE OR REPLACE TYPE PRODUCT_DETAIL_TYP AS OBJECT(
	PRODUCT_NO NUMBER(15),
	REAS_CODE VARCHAR2(4),
	SRVC_CODE VARCHAR2(4),
	STYP_CODE VARCHAR2(4),
	NEW_PROD_IND VARCHAR2(1),
	CHARGE_TYPE VARCHAR2(1),
	CHOICES_IND VARCHAR2(2),
	CHOICES_LINK VARCHAR2(3),
	DATE_ADDED DATE,
	PRICING_DATE DATE,
	REPRICE_IND VARCHAR2(1),
	Prod_Disc_Details NT_DISCOUNT_TYP,
	Prod_Suppl_Details NT_SUPPLEMENT_TYP,
	PRIOR_AMT NUMBER(10,2),
	BASE_PRICE NUMBER(10,2),
	HEADLINE_PRICE NUMBER(10,2),
	FULL_YEAR_PRICE NUMBER(10,2),
	PRODUCT_CHARGE NUMBER(10,2),
	RISK_ADJ NUMBER(10,2),
	TOT_PROD_DISC NUMBER(10,2),
	Prod_Audit_Details NT_AUDIT_TYP,
	Prod_NVP NT_NVP_TYP,
	Appliances NT_APPL_TYP);


CREATE OR REPLACE TYPE NT_PROD_TYP AS TABLE OF PRODUCT_DETAIL_TYP

CREATE OR REPLACE TYPE CONTRACT_DETAIL_TYP AS OBJECT(
	QUOTE_NUMBER NUMBER(15),
	SCON_NUMBER NUMBER(15),
	ANNIVERSARY_DATE VARCHAR2(4),
	BULK_IND VARCHAR2(4),
	PRICING_DATE VARCHAR2(1),
	END_DATE VARCHAR2(1),
	START_DATE VARCHAR2(2),
	LOYALTY_POINTS VARCHAR2(3),
	NUM_PAYMENTS DATE,
	PAYMENT_CODE DATE,
	Cont_Disc_Details NT_DISCOUNT_TYP,
	Cont_Supp_Details NT_SUPPLEMENT_TYP,
	REAS_CODE_CHANGE VARCHAR2(4), 
	TOT_CONTRACT_DISC NUMBER(10,2),
	TOT_PROD_CHARGE NUMBER(10,2),
	ANNUAL_CHARGE NUMBER(10,2),
	Cont_Audit_Details NT_AUDIT_TYP,
	Cont_NVP NT_NVP_TYP,
	Products NT_PROD_TYP)


CREATE OR REPLACE TYPE NT_CONT_TYP AS TABLE OF CONTRACT_DETAIL_TYP

CREATE TABLE PRICE_XML_TABLE 
			(MESSAGE_ID VARCHAR2(20),
			USER_ID VARCHAR2(20),
			REQUEST_ACTION VARCHAR2(50),
			REQUEST_TYPE NUMBER(3),
			RULE_SET_VERSION NUMBER(3),
			AUDIT_DETAILS NT_AUDIT_TYP,
			CUSTOMER CUSTOMER_DETAIL,
			PREMISE PREMISES_DETAIL,
			CONTRACTS NT_CONT_TYP
			)
			NESTED TABLE AUDIT_DETAILS STORE AS nt_audit_tab,
			NESTED TABLE CONTRACTS STORE AS nt_contracts_tab
			(NESTED TABLE Cont_NVP STORE AS nt_contNVP_tab
			NESTED TABLE Cont_Audit_Details STORE AS nt_contAudit_tab
			NESTED TABLE Cont_Supp_Details STORE AS nt_contSupp_tab
			NESTED TABLE Cont_Disc_Details STORE AS nt_contDisc_tab
			NESTED TABLE Products STORE AS nt_product_tab
				(NESTED TABLE Prod_NVP STORE AS nt_prodNVP_tab
				NESTED TABLE Prod_Audit_Details STORE AS nt_prodAudit_tab
				NESTED TABLE Cont_Supp_Details STORE AS nt_prodSupp_tab
				NESTED TABLE Prod_Disc_Details STORE AS nt_prodDisc_tab
				NESTED TABLE Appliances STORE AS nt_contracts_tab
					(NESTED TABLE Appl_NVP STORE AS nt_applNVP_tab
			  		NESTED TABLE Appl_Audit_Details STORE AS nt_applAudit_tab)))



the error when i try to create the PRICE_XML_TABLE is given below ,

ORA-02320: failure in creating storage table for nested table column CONTRACTS
ORA-02320: failure in creating storage table for nested table column PRODUCTS
ORA-00904: : invalid identifier

thanks,
sathiya
Re: Creating multiple level nested tables. [message #283071 is a reply to message #283057] Mon, 26 November 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02320: failure in creating storage table for nested table column %s
 *Cause:  An error occurred while creating the storage table for the
         specified nested table column.
 *Action: See the messages that follow for more details. If the situation
          they describe can be corrected, do so; otherwise contact Oracle
          Support.

Quote:

ORA-00904: : invalid identifier

Search and fix that.

By the way, I still don't see your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Mon, 26 November 2007 01:16]

Report message to a moderator

Re: Creating multiple level nested tables. [message #283105 is a reply to message #283071] Mon, 26 November 2007 02:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel is absolutely right: "invalid identifier" should give you a clue. And you know that this invalid identifier is somewhere in the PRODUCTS nested table. That narrows it down.

Some tips:
- Have a look at the "store as" table names: there are duplicates.
- Have a look at the nested type names: you've done some copy/paste work that Oracle doesn't agree with, i.e. a nested table name that's incorrect. Hence the error.

Are we just proofreading your script?

MHE
Re: Creating multiple level nested tables. [message #283108 is a reply to message #283071] Mon, 26 November 2007 02:33 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
hi Michel,

ORACLE version what we use is 9.2.0.1

I'am not able to find why we get the error while creating the storage table for the nested table column.
can you please help me.

thanks,
Sathiya
Re: Creating multiple level nested tables. [message #283118 is a reply to message #283105] Mon, 26 November 2007 02:57 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
Maaher,

Have a look at the nested type names: you've done some copy/paste work that Oracle doesn't agree with, i.e. a nested table name that's incorrect. Hence the error.
-- do you maen to say that since i use the same types(say NT_DISCOUNT_TYP,NT_SUPPLEMENT_TYP,NT_AUDIT_TYP,NT_NVP_TYP)in creating both PRODUCT_DETAIL_TYP and CONTRACT_DETAIL_TYP is giving me the error.
Re: Creating multiple level nested tables. [message #283136 is a reply to message #283118] Mon, 26 November 2007 03:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
No. I mean: look at your table definition. You use the same table name twice in your storage clause. You also use 'Cont_Supp_Details' twice. The second time you're wrong.

MHE
Re: Creating multiple level nested tables. [message #283146 is a reply to message #283136] Mon, 26 November 2007 03:38 Go to previous messageGo to next message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
Ya i corrected those mistakes, even then i get the same error.

CREATE TABLE PRICE_XML_TABLE 
			(MESSAGE_ID VARCHAR2(20),
			USER_ID VARCHAR2(20),
			REQUEST_ACTION VARCHAR2(50),
			REQUEST_TYPE NUMBER(3),
			RULE_SET_VERSION NUMBER(3),
			AUDIT_DETAILS NT_AUDIT_TYP,
			CUSTOMER CUSTOMER_DETAIL,
			PREMISE PREMISES_DETAIL,
			CONTRACTS NT_CONT_TYP
			)
			NESTED TABLE AUDIT_DETAILS STORE AS nt_Audit_tab,
			NESTED TABLE CONTRACTS STORE AS nt_contracts_tab
			(NESTED TABLE Cont_NVP STORE AS nt_contNVP_tab
			NESTED TABLE Cont_Audit_Details STORE AS nt_contAudit_tab
			NESTED TABLE Cont_Supp_Details STORE AS nt_contSupp_tab
			NESTED TABLE Cont_Disc_Details STORE AS nt_contDisc_tab
			NESTED TABLE Products STORE AS nt_products_tab
				(NESTED TABLE Prod_NVP STORE AS nt_prodNVP_tab
				NESTED TABLE Prod_Audit_Details STORE AS nt_prodAudit_tab
				NESTED TABLE Prod_Supp_Details STORE AS nt_prodSupp_tab
				NESTED TABLE Prod_Disc_Details STORE AS nt_prodDisc_tab
				NESTED TABLE Appliances STORE AS nt_appliances_tab
					(NESTED TABLE Appl_NVP STORE AS nt_applNVP_tab
			  		NESTED TABLE Appl_Audit_Details STORE AS nt_applAudit_tab)))


please help.

thanks,
sathiya
Re: Creating multiple level nested tables. [message #283148 is a reply to message #283146] Mon, 26 November 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Go on.
"Invalid Identifier" is just a syntax problem.

Regards
Michel
Re: Creating multiple level nested tables. [message #283152 is a reply to message #283148] Mon, 26 November 2007 03:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
For crying out loud! Come on! I handed you everything you needed!

I will spell it out for you: PROD_SUPP_DETAILS does not exist.

MHE
Re: Creating multiple level nested tables. [message #283162 is a reply to message #283108] Mon, 26 November 2007 04:07 Go to previous message
rkumudha
Messages: 10
Registered: November 2007
Location: India
Junior Member
THANSK A LOT!!

The issue got solved.
there was a mistake in the table name given, as we corrected it the issue was solved.

thanks,
Sathiya
Previous Topic: how to commit grant on tables
Next Topic: Complete tree
Goto Forum:
  


Current Time: Sat Dec 10 16:48:12 CST 2016

Total time taken to generate the page: 0.25618 seconds