How to create nested tables in sql

Question

I am trying to create a nested table for a car website in sql.So I can fetch data from database and include the drop down menu in my website (I am using php, html and css)

So I wanna create a drop down menu like this. When I select the first option of dropdown, then the second option appear based on the previous selection. That's the link of website from where I took the screenshot that is attached above (the image) https://carbuyersonline.com.au/

     CREATE OR REPLACE TYPE my_nested_table IS TABLE OF VARCHAR2 (10);
       /
    create table car (
    year int(11) not null, 
    model varchar(128) not null, 
    shape varchar(128) not null, 
    badge varchar(128) not null,
    engineofcar varchar(128) not null,


  make my_nested_table  //here it says unrecorgnised data type near my_nested_table

  )NESTED TABLE make STORE AS nested_make;
  /

 DESC alfa_romeo;
 INSERT INTO alfa_romeo(model, shape, badge, engineofcar)
 VALUES ("Giulia", "4 Door Sedan", "Auto MY19, Quadrifoglio Auto MY19, Super 
 Auto MY19, Veloce", "4 Cylinder, 2.0 Litre - Petrol");

   //I added same code again just to show example but it will be different car next time

 DESC alfa_romeo;
INSERT INTO alfa_romeo(model, shape, badge, engineofcar)
VALUES ("Giulia", "4 Door Sedan", "Auto MY19, Quadrifoglio Auto MY19, Super 
Auto MY19, Veloce", "4 Cylinder, 2.0 Litre - Petrol");

I have mentioned the error above, next to the code

     make my_nested_table  //here it says unrecorgnised data type 
    my_nest_table)

Any help is appreciated. Also, is there a better way to do that?

Have you tried including the schema name when you create the type? — KMoe, Apr 26 '20 at 01:13
@Hemlata CREATE OR REPLACE TYPE schema_name. my_nested_table. The default is usually dbo, so try CREATE OR REPLACE TYPE dbo. my_nested_table — KMoe, Apr 26 '20 at 13:24
@APC What do you mean by "you need to post that actually reproduces it"? I didn't understand. But, I will be happy to post if I know what I have to post. — Hemlata, Apr 27 '20 at 00:22
@APC I am trying to make it work in phpmyadmin as I am using Xampp. Someone edited my post and added "oracle" tag but I am not using it for database. — Hemlata, Apr 27 '20 at 00:24

score 0 · Answer 1 · answered Apr 27 '20 at 05:04

I am not familiar with Xampp so I am uncertain what the exact code would be. It appears you may have created a table data type but did not first define it. Perhaps you could try something like this:

CREATE or REPLACE TYPE my_nested_table AS OBJECT 
(table_column1 VARCHAR2(10), table_column2 VARCHAR2(10));

CREATE or REPLACE TYPE my_nested_table_type AS TABLE OF 
my_nested_table;

If this is not he correct/exact syntax, hopefully I've nudged you in the right direction!

How to create nested tables in sql

1 Answers1