Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Updating Object Type Columns with SQL

437871Feb 17 2005 — edited Feb 18 2005
Please 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!!!

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 18 2005
Added on Feb 17 2005
5 comments
534 views