Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vannessa.

Asked: March 16, 2017 - 12:11 pm UTC

Last updated: August 15, 2023 - 6:02 pm UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hello ,
i want to have multiple value per cell and i used nested tables.
After creating my table projects with a the nested column categories, i insert some rows.
but when i do select * from tables , i have the error unsupported data type by the column categories.
And i thought that with nested tables, i can have the multiple value in the column categories.
Is there another way to retrieve such data from nested tables?
What will the table projects look like in schema? Have I the multiple value in a cell ?

There is another way to add multiple value in cell ( Except using JSON) and to retrieve such data

with LiveSQL Test Case:

and Chris said...

It's down to the client to interpret nested tables and display them. Unfortunately LiveSQL doesn't support this yet.

But plenty of other clients do! For example, in SQL*Plus you can see the contents:

SQL> CREATE TYPE Categories AS OBJECT(
  2  priority VARCHAR2(10),
  3  subcategory VARCHAR2(60)
  4  );
  5  /

Type created.

SQL>
SQL> CREATE TYPE CategoriesList AS TABLE OF Categories;
  2  /

Type created.

SQL>
SQL> CREATE TABLE projekt (
  2  projektname VARCHAR2(10),
  3  categories CategoriesList)
  4  NESTED TABLE categories STORE AS categories_tab;

Table created.

SQL>
SQL> INSERT INTO projekt VALUES('a',CategoriesList(Categories('1','subcat1'),
  2                                                Categories('2','subcat4'),
  3                                                Categories('3','subcat5')));

1 row created.

SQL>
SQL> INSERT INTO projekt VALUES('b',CategoriesList(Categories('1','subcat1'),
  2                                                Categories('2','subcat3'),
  3                                                Categories('3','subcat5')));

1 row created.

SQL>
SQL> INSERT INTO projekt VALUES('c',CategoriesList(Categories('1','subcat1'),
  2                                                Categories('2','subcat2')));

1 row created.

SQL>
SQL> select * from projekt;

PROJEKTNAM
----------
CATEGORIES(PRIORITY, SUBCATEGORY)
--------------------------------------------------------------------------------------------------

a
CATEGORIESLIST(CATEGORIES('1', 'subcat1'), CATEGORIES('2', 'subcat4'), CATEGORIES('3', 'subcat5'))

b
CATEGORIESLIST(CATEGORIES('1', 'subcat1'), CATEGORIES('2', 'subcat3'), CATEGORIES('3', 'subcat5'))

c
CATEGORIESLIST(CATEGORIES('1', 'subcat1'), CATEGORIES('2', 'subcat2'))


You can also access the nested table elements using the table operator:

SQL> select t.*
  2  from   projekt p,
  3         table (p.categories) t;

PRIORITY   SUBCATEGORY
---------- ----------------------------------
1          subcat1
2          subcat4
3          subcat5
1          subcat1
2          subcat3
3          subcat5
1          subcat1
2          subcat2


This works in LiveSQL:
https://livesql.oracle.com/apex/livesql/file/content_EPTTKY4KPUCW0A444AZKQKX1F.html

Finally, while you can store multiple values using nested tables, in the vast majority of cases you're better off using regular tables instead. This makes it easier to query and work with your data!

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nested Tables

Vannessa Kabiwo, March 16, 2017 - 3:43 pm UTC

Thank you very much

Move table to a blob field

Raja, July 15, 2021 - 1:03 pm UTC

reate or replace TYPE INP_OBJ AS OBJECT

( NAME VARCHAR2(9 CHAR)

,ADDRESS1 VARCHAR2(50 CHAR)

,ADDRESS2 VARCHAR2(50 CHAR)

,CITY VARCHAR2(5 CHAR) );



create or replace TYPE INP-TAB AS TABLE OF INP_OBJ ;

I have 3 rows in the inp-tab and I want to move 2nd row of inp-tab to a blob field (say L-blob-field ).

I dont want to concatenate field by field and move it to l-blob-field. is there a way move the whole record (all column) in to a blob field ?
Chris Saxon
July 15, 2021 - 1:39 pm UTC

I'm unclear what you're trying to do here - extract the values from the objects and store them in a BLOB? If so, why a BLOB?

Please clarify!

For error logging

Raja, July 15, 2021 - 2:31 pm UTC

Sorry for the confusion and posting it different thread.

I receive input-table and try to insert /update/delete it and for some reason if the DML operation fails, I'm calling a procedure for error logging (inserting it to a error-log table) .

one of the field in the error log table is input-text which is a BLOB data-type (the whole input that failed in DML operation)


In actual input-table i have 30 column and i need to move the it to blob field and insert it to a error table incase of failure.

so I dont want to access each field from input-table and concatenate them in to string and move it to the BLOB-field.

I was curious if there is a way to access N-th row from input table (all columns ) and move it like "blob-field := input-table(n) "
Chris Saxon
July 16, 2021 - 8:40 am UTC

There's no implicit conversion from object types => blob. So you'll have to write a routine that extracts the values, concatenates them, and converts this to blob.

You could make this a function of the object type itself.

How to get all the data for priority 1 in single row in this example

Kartick T, November 22, 2021 - 5:19 am UTC

Hi,

How to get the values for each of the priorities in single multiple columns for this example? I want to unnest the nested column but the data for priority 1 should come in single row with each value in a different column.

Required Output
PRIORITY SUBCATEGORY SUBCATEGORY SUBCATEGORY
---------- -------------------- -------------- --------------------
1 subcat1 subcat1 subcat1
2 subcat4 subcat3 subcat2
3 subcat5 subcat5

Rathinavel, August 15, 2023 - 2:38 pm UTC

Nested table using
Create type greens is table of varchar(20);
Select * from user_objects from where object_type='type';
Chris Saxon
August 15, 2023 - 6:02 pm UTC

What exactly is your question here?