Updating Object Type Columns with SQL
437871Feb 17 2005 — edited Feb 18 2005Please note that is a re-post from a different forum. I thought I would try this forum as reading through it, it made more sense to post here. Problem:
I am trying to update a column inside a Nested Table Type in my Table.
This is what I got from some documentation I found from Oracle.
UPDATE TABLE(SELECT PROGRAMS FROM TB_ITEM_MASTER WHERE ITEM_NO_ID =1748) IM
SET IM.PROGRAM_ID = 1;
WHERE IM.ITEM_NO_ID = 1748;
Where PROGRAM_ID is a field from the nested table (ITEMS_IN_PROGRAMS_NT) and ITEM_NO_ID is the Primary Key in my TB_ITEM_MASTER table. When I run this it gives me an error of:
ORA-22908: REFERENCE TO NULL TABLE VALUE
I assume this is because PROGRAM_ID is currently NULL, and when it pulls the record back, I am pulling a NULL value. Currently all the values will be NULL in the table, but I would like to create a UPDATE Statement that I will call that will update that value, from NULL to whatever ID.
Here are my def'n's of all my objects for this problem:
--Object Type def'n
CREATE OR REPLACE TYPE ITEMS_IN_PROGRAMS_TY AS OBJECT(
PROGRAM_ID NUMBER(38));
--Object Type def'n for Nested Table
CREATE OR REPLACE TYPE ITEMS_IN_PROGRAMS_NT
AS TABLE OF ITEMS_IN_PROGRAMS_TY;
--Table def'n
CREATE TABLE TB_ITEM_MASTER
(ITEM_NO_ID NUMBER(38) NOT NULL,
ITEM_NO VARCHAR2(75) NOT NULL,
NOMENCLATURE VARCHAR2(100) NULL,
PROGRAMS DMISDBA.ITEMS_IN_PROGRAMS_NT NULL);
Can someone please help me....I am pulling my hair out on this problem!!! All I want to do is update the column inside a Nested Table. This can not be that hard!!!