CODEWITHSUNDEEP
sqloracle-databasenested-table
Suprit Sawant
Suprit Sawant

Reputation: 1

Inserting values into a nested table

I am trying to develop a university database with the help of nested tables, I have successfully created all other nested tables required and inserted data as well, but while inserting data into marks table I am facing problem of inconsistent datatype.

codes:

CREATE OR REPLACE TYPE MODULE_MARKS;
CREATE OR REPLACE TYPE MM_NT_TYPE AS TABLE OF REF MODULE_MARKS;

CREATE OR REPLACE TYPE MODULE_MARKS AS OBJECT
(
  MODULE REF MODULE_T, MARKS_OBTAINED, TOTAL_MARKS, STATUS
)

CREATE TABLE MARK_TAB
(
  student ref student_t,
  modules_marks mm_nt_type
)

I am able to insert reference to student correctly but I want to insert data into module_marks.

Tried doing :

INSERT INTO MARK_TAB VALUES((SELECT REF(S) FROM STUDENT_TAB S WHERE 
S.S_ID=1), 
MM_NT_TYPE( MODULE_MARKS_T((SELECT REF (M) FROM MODULE_TAB M WHERE 
M.MODULE_ID =1),
90,100,'PASS')));

this shoes the error ORA-00932. EXPECTED REFERENCE OF MODULE_MARKS_T GOT MODULE_MARKS_T.

Upvotes: 0

Views: 1467

Answers (2)

MT0
MT0

Reputation: 168311

MM_NT_TYPE is a collection of REF MODULE_MARKS whereas you are passing MODULE_MARKS objects and not references. Instead, you need to have a table containing containing MODULE_MARKS objects that you can reference:

CREATE TABLE module_marks_tab OF module_marks;

Then you can reference those objects. For example:

INSERT INTO mark_tab VALUES (
  ( SELECT REF(s) FROM students s WHERE id = 2 ),
  MM_NT_TYPE(
    ( SELECT REF( m ) FROM module_marks_tab m WHERE m.module.id = 1 AND marks_obtained = 3 ),
    ( SELECT REF( m ) FROM module_marks_tab m WHERE m.module.id = 3 AND marks_obtained = 8 )
  )
);

db<>fiddle

Upvotes: 0

Popeye
Popeye

Reputation: 35920

It seems a familiar structure to me. Maybe I have created the same structure for one of my projects.

I think you are confused when inserting the record in the column having a type which is table of REF.

I have COURSES_TABLE_TYPE which is table of REF COURSES_T and courses table is table of COURSES_T;

I suggest you do the following:

INSERT INTO DEPARTMENT VALUES (
    1,
    COURSES_TABLE_TYPE((  -- REFs of single records delimited by comma
        SELECT
            REF(C)
        FROM
            COURSE C
        WHERE
            COURSE_ID = 1
    ),(
        SELECT
            REF(C)
        FROM
            COURSE C
        WHERE
            COURSE_ID = 2
    ))
);

Upvotes: 0

Related Questions